Need help switching active workbook in VB script

I am using code like the attached to open workbooks in Excel.  I am then parsing through a text file and then writing the data into the excel workbook.  Problem is, I want to have several workbooks open at the same time and just switch the active workbook for writing.

Keep in mind that this is a VB Script, not VBA inside Excel.
Set objExcel = CreateObject("Excel.Application")
objExcel.WorkBooks.Open fnSO5Workbook 'variable pointing to workbook
Set objSheet = objExcel.ActiveWorkbook.Worksheets("2008 Detail")

Open in new window

RSmuraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DaveCommented:
You could do it like so.

But if you set the variables like below you don't need to activate the workbook to use it, ie you could use
wb1.Sheets(1).[a1] = "10"
without actibating WB


Cheers

Dave
Set objexcel = CreateObject("Excel.Application")
 
Set wb1 = objexcel.Workbooks.Open("C:\Test1.xls")
Set WB2 = objexcel.Workbooks.Open("C:\Test2.xls")
wb1.Activate

Open in new window

0
Chris BottomleySoftware Quality Lead EngineerCommented:
Hello RSmura,

The following ought to help

Regards,
Chris
Set objExcel = CreateObject("Excel.Application") 
objExcel.WorkBooks.Open fnSO5Workbook 'variable pointing to workbook
set wb1 = objexcel.activeworkbook
Set objSheet1 = wb1.Worksheets("2008 Detail") 
objExcel.WorkBooks.Open fnSO5Workbook 'variable pointing to workbook
set wb2 = objexcel.activeworkbook
Set objSheet2 = wb2.Worksheets("2008 Detail") 
wb1.close
wb2.close
objexcel.quit

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RSmuraAuthor Commented:
OK so does your method of:

wb1.Sheets(1).[a1] = "10"

support something like the .cells function or do I have to specify .[a1] ?  I currently using

    objSheet.cells(ExcelRow,3).Value = List(inNum+1)

to write my data to the proper cell.

0
RSmuraAuthor Commented:
Fantastic answer.  Very quick too.  Thank you.  I sent you an additional question but already awarded you the points.  I'm sure I'll have some more questions soon too.  Is there somewhere to lookup all these functions like "ActivateWorkbook" and "Worksheets"?  Feel free to email me if you know of any good resources.  

Rob Smura
rob@smura.com

Thanks again!
0
Chris BottomleySoftware Quality Lead EngineerCommented:
As you may have already found referencing objsh1 for example is exactly the same as referencing "objExcel.ActiveWorkbook.workshet("diddly")" ... assuming that the active workbook is the same as objsh1's workbook.

Chris
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.