Activate a workbook using a variable

[RaunLGoode]

I want to start in Workbook "BookA"
Save the name of " BookA" as variable "wbA"
Activate Workbook "BookB"
and return to Workbook "BookA"
I am using the following code in workbook

'( from Workbook "BookA" )
Sub AtoBtoA ()

Dim wbA as String

' Set variable to Active workbook name minus the file extension
wbA = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)

'go to "BookB"
Windodows("BookB").Activate

' return to original workbook ("BookA") THE NEXT LINE IS WHERE IT HANGS UP with a RunTime Error 9 "Subscript out of Range" error message
Windows(wbA).Activate


' etc

End Sub
Could somebody out there tell what I should using instead of the "Windows(wbA).Activate" and also why this doesn't work.
I have also tried "Workbooks(wbA).Activate" and get the same result

 

[Brianwarnock]

I think that you need the full name BookA.xls in 2002, but isnt it different in 2007?

Brian

 

[SOS]

Actually, I believe you would need:

Workbooks.(wbA).Activate

 

[RaunLGoode]

I should have mentioned that I am using Oxxice 2003 with XP Pro

 

[Brianwarnock]

Being a disbelieving sod I just tried it, this works in 2002

Sub AtoBtoA()

Dim wbA As String

' Set variable to Active workbook name minus the file extension
'wbA = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)
wbA = ActiveWorkbook.Name
'go to "BookB"
Windows("BookB.xls").Activate

' return to original workbook ("BookA") THE NEXT LINE IS WHERE IT HANGS UP with a RunTime Error 9 "Subscript out of Range" error message
Windows(wbA).Activate


' etc

End Sub


Actually it hung on the first activate of BookB until I added the xls

 

[chergh]

He should just use 2 workbook variables and use these.

 

[RaunLGoode]

By "workbook variables" I assume you mean declaring them as "Dim wbA as Workbook"
I was having trouble setting the variable as the active workbook. The examples I found declared them using something like "Set wbA = WorkBook(name of fileas a string) which also returned an error

Using Office 2003/XP Pro

 

[Brianwarnock]

I'm puzzled as to what is the aim of this . I assume that alot takes place before he returns to the original workbook. I actually coded a message box between the 2 windows activate statements to prove it worked, stripped of the c**p this is the code that worked.

Sub AtoBtoA()

Dim wbA As String

wbA = ActiveWorkbook.Name
'go to "BookB"
Windows("BookB.xls").Activate
msgbox "HI" ' to cause a pause and allow check that BookB.xls is on top
Windows(wbA).Activate

End Sub


Brian

 

[chergh]

Why are you removing the extension in the first place?

Anyway I guess you are running this code from one of the workbooks so for that one you can use:

dim wba as workbook

set wba = thisworkbook

For the other workbooks use:

set wbb = workbooks("test.xls")

'or if the file isn't open

'set wbb = workbooks.open("c:\path\test.xls")