Databases Reference
In-Depth Information
The problem typically manifests itself when a macro is deployed across a variety of machines,
and one of the machines has a different setting for the “Hide File Extensions for known file types”
option. What then happens is that Excel will only recognize Workbooks if they are referenced with
or without the *.xls extension, depending upon the setting on the machine. If the extension types
are hidden on a machine, and you try to activate a Workbook with the command
Workbooks("Book3.xls").Activate
you will end up with an error message like the following:
FIGURE 1.2
Subscript out of range error.
“Run-time error '9' ” “Subscript out of range”
What this means is that Excel cannot find a Workbook named “Book3.xls.” This is because the
extensions are hidden on this machine, and Excel only recognizes the Workbook as “Book3.” (The
reverse condition can happen as well.) To avoid this situation, a subroutine can be created such as
the one below to activate Workbooks. If it proves to be necessary to add the extension to the
Workbook name, the subroutine will automatically do so.
Sub ActivateWorkbook(wbname$)
'Activate the desired Workbook
'Compensates for Hide Extensions of Known File Type Error 'in Excel
On Error GoTo ChangeStatts
Workbooks(wbname$).Activate
Exit Sub
ChangeStatts:
Select Case Err
Case 9
wbname$ = wbname$ & ".xls"
Workbooks(wbname$).Activate
Debug.Print "New Name: "; wbname$
Case Else
dummy = MsgBox("An Unanticipated Error No." & Str(Err) &
" Occurred
!", vbOKOnly, "Error")
End Select
End Sub
Use this subroutine whenever a different Workbook must be activated, and pass the Workbook
name using
without the “.xls” suffix. If the “.xls” suffix is required, an error will be
wbname$
