VBA in Excel: define file to open (I retrieve data from 2 Excel files)

**zeno1** · 08-14-2012, 05:48 PM

Hello,

I have 2 Excel files open, file (A) is a data source file and file (B) is a file with various sheets being created with VBA.
My VBA needs to be clear which of both files it refers to when reading data, and I believe that is my problem.

I have the following written:

Please Login or Register  to view this content.

How should I correct my VBA so that it opens the specified file? And if the file is already open, to select it?
Thank you for your help.

**JBeaucaire** · 08-14-2012, 09:17 PM

If there is a named range in your workbook called LastFileEdited, then you can set the variable this way as you open the file:

Please Login or Register  to view this content.

Be warned, this will only work if the string in that cell is the full path and name of a file.

**zeno1** · 08-15-2012, 09:33 AM

Thank you for your proposition JBeaucaire.

The way I'm trying to make reference this particular Excel file easier is by defining it with 'W2bk' name.

Long naming (that I prefer to avoid):

Please Login or Register  to view this content.

To avoid this long naming, I'm trying to call the file 'W2bk' so that I can reference it in the VBA code.

When writing your suggestion, I still receive error message 13

Please Login or Register  to view this content.

Thank you for your help.

**JBeaucaire** · 08-15-2012, 12:19 PM

I showed you in my post how to do it all at once, set the variable and open the workbook simultaneously.

You're receiving errors because either:

1) A mismatch in the data variable types. (are all thress of those variables you're trying to use declared as STRING?)
or
2) The strings in those variables do not properly resolve to an existing file.