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:

DataSourceFile = Range("LastFileEdited")
Set W2bk = Workbooks (DataSourceFile)        'I receive error 9: suscript out of range
Workbooks(W2bk).Open

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:

Set wb2 = Workbooks.Open(Range("LastFileEdited"))

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

DriveForData = Range("DriveForData")
DataSourceDirectory = Range("DirectoryForStudyData")
DataSourceFile = Range("LastFileEdited")

Workbooks.Open Filename:=(DriveForData & DataSourceDirectory & DataSourceFile)

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

Workbooks.Open Filename:=(DriveForData & DataSourceDirectory & DataSourceFile)  'I try with and without this line; neither works
Set W2bk = Workbooks.Open((DriveForData & DataSourceDirectory) & (DataSourceFile))  'run type error 13: type mismatch

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.