How to access another workbook with VBA

[nmay732]

Normally I would just call Workbooks("workbookname").activate and everything works fine.

However, I am calling someone else's script which opens up a new instance of excel with the data I need. The instance of excel that my macro resides in doesn't have access to workbooks in this new instance of excel. The above code returns a "subscript not found" error.

Does anyone have a workaround for this? I can't get anything to work.

Thanks!

 

[RoryA]

Why call someone else's script, as a matter of interest?
Does the other script return anything, such as a workbook or application reference? If not, if you know the path to the workbook in question, you can use GetObject to get a reference to it.

 

[nmay732]

I'm calling his script because it goes off and fetches a lot of data from a database, then parses it. It's a rather complicated script with a lot of options. It does more than what I'm asking it to do for this particular project. It would just be a really big pain to go rewrite what has already been written. And unfortunately it doesn't return any refrences. It just opens up a new spreadsheet with the data. Which isn't saved yet, so I wouldn't be able to use a path either. = /

Thanks for the quick response = )

 

[RoryA]

Do you at least know its name? If so you can use API calls to get a reference to it.

 

[nmay732]

I believe it's just Sheet1.xlsx

How would I use an API call? I haven't used those yet.

 

[RoryA]

I believe it's just Sheet1.xlsxHow would I use an API call? I haven't used those yet.

It would not have an extension if it hasn't been saved. Is it actually book1 or sheet1, or something else?

 

[nmay732]

It just says "Sheet1" at the top. I just assumed the default extension. I guess it doesn't have one yet.