Excel 2007: ActiveWorkbook with VBA

[caky]

Dear Experts,

I have a Excel/VBA problem I hope you can help. I have two excel workbooks open and visible on my task bar. I need to toggle between the two workbooks to extract data using VBA. I've tried the following:

1) Windows ("Workbook Name1.xlsm").activate
2) Workbooks("Workbook Name1.xlsm").Worksheets("Sheet1").Activate
3) Set WB = Workbook.open ("directory")

Both 1 and 2 yield "Subscript Out of Range" while 3 tried to reopen the workbook again.

What is the problem here? How come I can no longer toggle between workbooks?

Thanks for the helps in advance.

 

[alow]

You could use the keyboard shortcut Alt+Tab to switch between them if you just need to switch. I tried recording a macro using the View | Switch Windows button and it yielded the following code (Which you've already used):

   Windows("Book2.xlsx").Activate

That works for me, but I'm not a VBA person, really. The only suggestions I could try are to make sure the files are in the same folder to at least test it. And verify that all spaces and characters in the workbook/worksheet names are correct.

 

[Stoneboysteve]

I pulled this out of a working macro, maybe you can get the code to work by activating the sheet after you set the worksheet. It is part of a larger set of instructions I know work if the file location is correct and the sheet name exists.

Set oExcel1 = Item.Application.CreateObject("Excel.Application")
oExcel1.Visible = True
oExcel1.Workbooks.Open("H:\My Documents\Documents\SomeFile.xlt")

Set oSheet1 = oExcel1.Workbooks(1).Worksheets("SomeSheet")
oSheet1.Activate

Of course you will not need to open the workbook over and over.

Stoneboysteve