subscript out of range -- help please

    Hi There
    I am writing a macro that flips between 3 (already open - by another application) excel files/workbooks
    problem is when I go to activate or select the workbooks I get subscript out of range


    Workbooks("C:\Users\Clive\Documents\Premiership.xlsm").Select


    filename is exactly as needed


    I think the problem is because the file is already open? (another instance of Excel) How do I get around this issue .


    regards
    Clive

    Re: subscript out of range -- help please


    As you said, you can't 'select' a workbook in another instance of Excel. The workaround(s) are a little convoluted but you can try reading http://www.ozgrid.com/forum/sh…82853&p=697241#post697241. There's a much shorter solution in the next post in that thread but you must have Word installed and it will not return a reference to the instance, just the name.


    There are other ways to activate a Window. For example, use the Windows API to search for the caption but, again, you will only be able to activate the window not get a reference you can manipulate in VBA code.


    All depends on exactly what you want to do.

    Re: subscript out of range -- help please


    Grimes


    thanks for taking the time to reply , I am relatively new to VB . I have 3 files opened by another program and I want to select each file in turn and manipulate /write to the files in a continuous loop.


    I am a bit stumped by this error. I am open to a convoluted workaround at this point :)

    Re: subscript out of range -- help please


    If the workbook is open in the same instance of Excel as your code, you just need to remove the path:


    Code
    Workbooks("Premiership.xlsm").Select


    If it's open in another instance, you can use a workbook variable and GetObject:

    Code
    Dim wb as Workbook
Set wb = GetObject("C:\Users\Clive\Documents\Premiership.xlsm")


    and then use the wb variable whenever you need to write to that workbook.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Re: subscript out of range -- help please


    Rory ,


    sorry for the stupid reply !!!


    How does getobject change the scenario?


    I set wb as the filename (understand that)


    can you please tell me how to write to Cell A1 using the WB variable


    Sheets(1).Select
    Range("F3").Select


    all using the wb variable



    much appreciated

    Re: subscript out of range -- help please


    Code
    wb.Sheets(1).Range("F3").Value2 = "something"


    for example.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Re: subscript out of range -- help please


    RORY YOU ARE A SUPERSTAR


    thanks so much for your help - I have got through that roadblock , now for the next one


    To recap if you reference a workbook opened by another application you need to use below and use wb prefix


    Dim wb As Workbook
    Set wb = GetObject("C:\Users\Clive\Documents\Premiership.xlsm")

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login