How do I activate a workbook using a global variable?

**Erikje** · 03-05-2013, 04:13 PM

Activate workbook using a variable?

Can anyone tell me why the line below is not working?

Dim ScorecardFile As String
Sub StartOpen()
ScorecardFile = "C:\Scorecard\TestingNew\PopPVs.xlsx"
Application.Run "SoftcodeFileOpen"
End Sub
Sub SoftcodeFileOpen()
Workbooks.Open Filename:=ScorecardFile
Windows("PopRatings.xlsx").Activate
Windows(ScorecardFile).Activate '************This line causes the error below
End Sub

Error:

Run-time error: 9

Subscript out of range

I've also tried the following and it gets the same error:

Windows("ScorecardFile").Activate

Can anyone tell me how to activate workbooks using a varibale like I open the workbook above?

**Jakobshavn** · 03-05-2013, 04:32 PM

Activate without the filepath:

Please Login or Register  to view this content.

**HaHoBe** · 03-05-2013, 04:36 PM

Hi, Erikje,

there´s no need to use a modal-wide variable to pass the the string, it could be done by passing the string as a parameter (this would avoid anybody starting the procedure on it´s own when no value has been assigned to the variable which would result in a run-time error.

Please Login or Register  to view this content.

There´s also no need to jump between workbooks (btw: the window will only display the name of the workbook, not the full path and name, so only the last part of the string should be used using InStrRev to find the last \).

Please add Code-Tags to your code for better readability.

Ciao,
Holger