557 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Copy, rename, delete and move files and folders Part two of a three-part series of blogs |
---|
By referencing the unintuitively named Microsoft Scripting Runtime object library you can write VBA code to access files and folders on your hard disk. This blog explains how, and gives a couple of worked examples.
This blog is part of a complete Excel VBA tutorial. We can also provide training in person, either on one of our Excel courses or on one of our VBA courses. |
Suppose that you know one of your workbooks in a folder contains the name of your dog (let's say he's called Fu-Fu), but you can't remember which. Here's an algorithm to find him:
Now that we've written our algorithm, it's time to code it!
The first thing we'll do is to create a function which will take any Excel file on the hard disk, open it, look for the hidden text in all of its worksheets and return True or False depending on whether it finds it or not:
Function IfSearchStringFound(ThisFile As File) As Boolean
'open this workbook (using full file path so can find it)
Dim wb As Workbook
Dim FoundCell As Range
Set wb = Workbooks.Open(ThisFile.Path)
'look for the hidden text in each worksheet
Dim ws As Worksheet
For Each ws In Worksheets
'(this command obtained by recording)
ws.Select
Set FoundCell = Cells.Find(What:="Fu-fu", LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If FoundCell Is Nothing Then
'if no cell pointed to, text not found
IfSearchStringFound = False
Else
'if we've found a cell, display it
MsgBox "Found text at cell " & FoundCell.Address & _
" in worksheet " & UCase(ws.Name) & _
" in workbook " & UCase(ThisFile.Path)
IfSearchStringFound = True
Exit Function
End If
Next ws
'whether we found the text or not, close the file
wb.Close savechanges:=False
End Function
Note that the function tidies up after itself (it closes down the workbook that it's opened).
Our main routine includes error-checking to see if the workbook referenced actually exists:
Sub ListFiles()
'the standard reference - see rest of blog
Dim fso As New FileSystemObject
'a reference to the folder containing the files
Dim WiseOwlFolder As Folder
'a reference to each file in the folder
Dim PossibleWorkbook As File
'the name of each file in the folder
Dim FileName As String
'we're looking in one particular folder,
'so try to refer to it
On Error GoTo NoSuchFolder
Set WiseOwlFolder = fso.GetFolder("C:\wise owl\")
'reset default error trapping
On Error GoTo 0
'if we get here, we've got a folder - loop over
'all of its files
For Each PossibleWorkbook In WiseOwlFolder.Files
'for this file, see if it's a workbook
FileName = PossibleWorkbook.Path
Select Case UCase(Right(FileName, 5))
Case ".XLSX", ".XLSM"
'it's a workbook, so open it
If IfSearchStringFound(PossibleWorkbook) Then
'if we've found what we were looking for,
'we can stop
Exit Sub
End If
Case Else
'for all other possible files, just ignore them
End Select
Next PossibleWorkbook
'no errors - can finish!
MsgBox "No text found in any of the files!"
Exit Sub
NoSuchFolder:
'report error and stop
MsgBox "Can not find a folder with this name!"
End Sub
This routine will loop over all of the files in the specified folder, but only try to open the Excel ones:
For this example, the macro will open the files shown selected only, since they are the only ones whose file names end in .XLSX or .XLSM.
If Fu-fu is indeed hidden somewhere in a cell in one of the workbooks in the folder given, the macro will display this:
An example message displayed by our macro - here Fu-fu was well hidden!
If, on the other hand, there is no Fu-fu, you will see this:
The message yoi'll see if none of the workbooks contains the hidden text.
That's the end of this worked example - the next one shows how to find all of the files on your hard disk whose names contain a certain string of text.
Parts of this blog |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.