• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 788
  • Last Modified:

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

0
RSmura
Asked:
RSmura
  • 2
  • 2
1 Solution
 
Dave BrettCommented:
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 BottomleyCommented:
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
 
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 BottomleyCommented:
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now