Need help switching active workbook in VB script

Posted on 2008-11-04
Last Modified: 2013-12-26
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

Question by:RSmura
    LVL 50

    Expert Comment

    by:Dave Brett
    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


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

    Open in new window

    LVL 59

    Accepted Solution

    Hello RSmura,

    The following ought to help

    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") 

    Open in new window


    Author Comment

    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.


    Author Closing Comment

    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

    Thanks again!
    LVL 59

    Expert Comment

    by:Chris Bottomley
    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.


    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
    Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now