VBA - File Activate

Question

I used the following code in 2003 without an issue.  In 2010, I get a runtime error, number 9, "Subscript out of range."  There were no other changes to the code.  In fact, sometimes the program runs, but most of the time I get the error.  Not sure what the issue might be.  Anyone out there experience this problem, and have you found a workaround?

stFileNameData = InputBox("Input the name of the file containing the financial data.")

    stFileNameFullData = stFileNameData + ".xlsx"

Workbooks(stFileNameFullData).Activate

Thanks

Answer 1

Thanks Bernie, but putting application in front of Workbook did not work for me.

Answer 2

Thanks Edwin, but the workbook is open when the code is run.  I open it manually.

Answer 3

stFileNameData = InputBox("Input the name of the file containing the financial data.")

    stFileNameFullData = stFileNameData + ".xlsx"

Application.Workbooks(stFileNameFullData).Activate

XL 2013 has moved to a Single-Document-Interface from the usual Multi-Document-Interface, so when you use the Workbooks collection you need to preface it: Application.Workbooks(filename) instead of just Workbooks(filename)

If you are not typing in the full name correctly, you could use code like this to allow part of the name to be entered:

Dim w As Workbook

stFileNameData = InputBox("Input part of the name of the file containing the financial data.")

For Each w In Application.Workbooks

If w.Name Like "*" & stFileNameData & "*" Then

w.Activate

Exit Sub

End If

Next w

MsgBox "That name was not found."

Answer 4

That error was in fact because the workbook specified was not opened within Excel already.

For example, if you told Excel to activate a workbook "aaa.xlsx", but that workbook was not opened and therefore couldn't be activated.

This error will also happen in Excel 2003, not only in Excel 2010.

You may actually use an error handler in your macro to trap this error. See the example below:

Sub mymacro()

stFileNameData = InputBox("Input the name of the file containing the financial data.")

stFileNameFullData = stFileNameData + ".xlsx"

On Error GoTo error_handle

Workbooks(stFileNameFullData).Activate

Exit Sub

error_handle:

MsgBox ("The workbook " & stFileNameFullData & " cannot be located."), vbExclamation

End Sub

Regards,

Edwin Tam

Excel Power Expander - http://www.vonixx.com