Runtime error 9 - Subscript out of range

[Kobe2932]

Hello

I have run into the following problem with my database. I have 2 excel files:

temporary.xls - this is an import of an access query, generated from access 2010, and adding / running some formatting amcro on it.

test.xls - this file already exists in a network folder, I'm opening it up from access database, and run some formatting macro on it.

My aim would be copying some information from temporary.xls to test.xls with VLOOKUP.

When clicking on the command button in access, the following process is initiated:

1, the query is run, results are automatically exported to temporary.xls file, the embedded macro is run, and the workbook is autosaved. Workbook remains open.

2, as a second step, test.xls is opened up, the formatting macro starts to run - first half of it is perfectly done, but when adding the Vlookup, it stops with Runtime error 9 - Subscript out of range error message.

It is strange to me, because I double....no, triplechecked everything that I'm referring to the correct path, correct filename and sheet name - everything is OK, Excel still does not recognize that temporary.xls is an existing and open file.

What is more strange to me that I tried to "hijack" the process somehow, thought I can solve my problem with simply copying the relevant sheet from temporary.xls to test.xls with a macro, and I can rewrite the vlookup section to search the data internally in the same file, no cross-reference to other workbooks.

Bazinga, the same error: Subscript out of range! Cannot even copy the existing file / sheet!

Any idea, what's going on ? Tried enabling Analysis ToolPak, and Analysis ToolPak - VBA in Excel, did not solve my problem.

how could I correct it ? I'll try to post the codes later, currently I'm on a different machine, but I'm interested, what can be the problem in general

 

[nschroeder]

Maybe try working with the two workbooks independently (outside of Access) and just get the Vlookup to work, then go from there a step at a time until you find out what triggers the problem.

 

[Kobe2932]

Maybe try working with the two workbooks independently (outside of Access) and just get the Vlookup to work, then go from there a step at a time until you find out what triggers the problem.

I checked it. Both file is downloaded, saved separately, no macro is run. Tried to copy the data with Vlookup manually, but cannot cross-refer to the other workbook. If I select a cell, I can enter the Lookup value, but cannot select the Table array - my selection just does not work in the other file, it does not select the cells, but only highlights them.

what can cause this ?

 

[nschroeder]

I'm guessing that you are working with two different instances of the Excel application object. Hopefully I have my terminology right. For example, if you select the Start button, open the Excel application, and open temporary.xls from there, then repeat that process for test.xls, you will have two separate instances of the Excel application open, and I don't think they can reference each other with formulas. On the other hand, if you open temporary.xls, then from there do a File-Open and open test.xls, you have two workbooks in the same application and they can communicate. The same thing can happen if you are referencing workbooks from code in an Access database. If you are opening them as separate applications, they won't know about each other. Maybe someone else can confirm or correct me.

 

[Kobe2932]

I'm guessing that you are working with two different instances of the Excel application object. Hopefully I have my terminology right. For example, if you select the Start button, open the Excel application, and open temporary.xls from there, then repeat that process for test.xls, you will have two separate instances of the Excel application open, and I don't think they can reference each other with formulas. On the other hand, if you open temporary.xls, then from there do a File-Open and open test.xls, you have two workbooks in the same application and they can communicate. The same thing can happen if you are referencing workbooks from code in an Access database. If you are opening them as separate applications, they won't know about each other. Maybe someone else can confirm or correct me.

Aah...never thought that could be the problem. I will try to update my code to have only one application open