Susan Harkins writes a column for TechRepublic with Office tips, including VBA code samples. In 10 ways to reference Excel workbooks and sheets using VBA, she posts a few methods for manipulating worksheets and workbooks through Excel VBA. I'd like to expand on a few of her samples.
1: Reference the active workbook
Her code:
Sub CloseActiveWBNoSave() 'Close the active workbook without saving. ActiveWorkbook.Close False End Sub Sub CloseActiveWBWithSave() 'Close the active workbook and save. ActiveWorkbook.Close True End Sub Sub CloseActiveWB() 'Close the active workbook. 'Let user choose whether to save. ActiveWorkbook.Close End Sub
She mentions that these could be combined into a single function. It would go something like
Function CloseActiveWB(Optional bSave As Variant)
' close active workbook
' bSave should be True to save changes,
' False to discard changes,
' blank to prompt user
Dim wb As Excel.Workbook
Set wb = Excel.ActiveWorkbook
If IsMissing(bSave) Then
wb.Close
Else
wb.Close SaveChanges:=bSave
End If
End Function
She mentions Select Case, but I'm not sure how you would use it in this case.
The next two samples return the path and filename of the active workbook and the workbook that contains the code. She uses:
Function GetActiveWB() As String GetActiveWB = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name End Function
Function GetThisWB() As String GetThisWB = ThisWorkbook.Path & "\" & ThisWorkbook.Name End Function
But the Workbook.FullName Property returns this information in one go. So we can rewrite these as
Function GetActiveWB() As String GetActiveWB = ActiveWorkbook.FullName End Function
Function GetThisWB() As String GetThisWB = ThisWorkbook.FullName End Function
Note that if the workbook isn't saved, FullName returns an empty String ("").
3: Reference workbooks in the Workbooks collection
Susan's code for opening all the workbooks in a folder is:
Sub OpenAllWB()
'Open all workbooks in specified folder.
Dim i As Integer
With Application.FileSearch
.LookIn = "C:\Examples"
.FileType = msoFileTypeExcelWorkbooks
'There are wb's
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
Workbooks.Open (.FoundFiles(i))
Next i
'There are no wb's
Else
MsgBox "There are no workbooks to open", vbOKOnly
End If
End With
End Sub
Problem is, FileSearch has been deprecated in Excel 2007. I'd use the Dir function instead, with a technique I describe in List matching files in a directory and Attach files in a folder to one email.
4: Explicitly reference a workbook
To activate a workbook, Susan provides the following code:
Function ActivateWB(wbname As String) 'Open wbname. Workbooks(wbname).Activate End Function
I'd prefer to return a reference to the workbook, like this:
Function ActivateWB(wbname As String) As Excel.Workbook 'Open wbname. Set ActivateWB = Workbooks(wbname) ActivateWB.Activate End Function
Sample usage:
Sub tst() Dim wb As Excel.Workbook Set wb = ActivateWB("book1") ' test out returned reference Debug.Print wb.Name End Sub
Update 9/4/2009 2:00 PM: Just realized I forgot to link to Susan Harkins' TechRepublic blog. Here's the feed: Microsoft Office
About JP
6 Comments:
Interesting post JP
Referring to the CloseActiveWB() function there is a comment line I don't think should be there "blank to prompt user", that's actually wrong.. Blank (ie. not passing anything) would result in close without changes.
Also, I'll suggest the following improvements of the function: convert to sub (it doesn't return anything, use scope if you want to hide it from the run macro dialog), avoid using variants as well as implement default value for optional parameters the proper way:
Private Sub CloseActiveWB(Optional bSave As Boolean = False) ' Close active workbook ' bSave should be True to save changes, ' False to discard changes (default) Dim wb As Excel.Workbook Set wb = Application.ActiveWorkbook wb.Close SaveChanges:=bSave End SubWhat do you think?
I also changed Excel.ActiveWorkbook to Application.ActiveWorkbook, ActiveWorkbook is a child object of the Application class, not the Excel library.. Not sure why Excel.ActiveWorkbook works? Must be because ActiveWorkbook also is a member of the hidden class Global..
Thanks for sharing, Peder. I'd go with the Help file on this one, it's actually correct.
The SaveChanges flag for the Close method of the Workbook object is optional.
True = Saves the changes to the workbook. If there is not yet a file name associated with the workbook, then FileName is used. If FileName is omitted, the user is asked to supply a file name.
False = Does not save the changes to this file.
Omitted = Displays a dialog box asking the user whether or not to save changes.
If you call the procedure I wrote and omit the bSave parameter, you'll get the following dialog:
As far as Application vs. Excel, I guess I'm used to writing the application name. But it shouldn't matter.
Hehe, seems like I was a bit quick on the trigger on this one.. You are right and I am wrong
Even though, the thing I don't like about the procedure is the variant parameter; you can pass anything to the function. Just to test I passed the Application object as the bSave parameter when calling the function and it didn't fail
I agree with you, but it's the only way to check if the parameter is missing. You could always do a Typename check or use the CBool function to check if the passed value is really boolean.
Oh, forgot bbCode for the VBA source code
Here's my take on an 2007-friendly alternative to the OpenAllWB() procedure (a bit laborious though):
'****************************************************************************** '* Author : Peder Schmedling '* Purpose : '* This procedure opens all Excel files found within a given directory. '* Input : '* strFolder, the folder to look for Excel files within '****************************************************************************** Sub OpenAllWBInFolder(strFolder As String) On Error GoTo ErrorHandler: Dim objFSO As Object Dim objDir As Object Dim objFile As Object Dim lngWbCnt As Long Set objFSO = CreateObject("Scripting.FileSystemObject") Set objDir = objFSO.GetFolder(strFolder) lngWbCnt = Workbooks.Count 'Loop all files in given directory, looking for Excel files For Each objFile In objDir.Files If objFile.Type Like "Microsoft Office Excel*Worksheet" Then Workbooks.Open objFile.Path End If Next objFile 'Warn the user if no files was found to open If lngWbCnt = Workbooks.Count Then MsgBox "There are no workbooks to open", vbOKOnly + vbInformation, _ "OpenAllWBInFolder" End If ExitProc: On Error Resume Next Set objFSO = Nothing Set objDir = Nothing Set objFile = Nothing Exit Sub ErrorHandler: Select Case Err.Number Case 5, 76 ' 5 = Invalid procedure call or argument '76 = Path not found MsgBox "Invalid path specified", vbOKOnly + vbInformation, _ "OpenAllWBInFolder" Case 429 'Can't create ActiveX 'Microsoft scripting runtime probably not installed MsgBox "'Microsoft scripting runtime' must be installed in order" _ & vbCr & "for this procedure to work.", _ vbOKOnly + vbInformation, "OpenAllWBInFolder" Case Else MsgBox "[" & Err.Source & "]" & vbCrLf & "An unexpected run-time" _ & " error occured '" & CStr(Err.Number) & "':" & vbCrLf _ & vbCrLf & Err.Description, vbExclamation, "OpenAllWBInFolder", _ Err.HelpFile, Err.HelpContext End Select Resume ExitProc: End Sub