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

[zeno]

Hello,

I have just created with VBA a new Excel file (A) that I will develop further (i.e. run more VBA on it).
I also have a source Excel file (B) in which I have source data to run VBA scripts.

So it is important to specify from which of both Excel files I read data (A or B) or write in (only A) in my VBA.

I try the following VBA but this is giving me an error message (error 9: suscript out of range), probably because it is unclear from which file to search the data.

[source]
DataSourceFile = Range("LastFileEdited")
Set W2bk = Workbooks (DataSourceFile) 'error 9: suscript out of range
Workbooks(W2bk).Open
[/source]

Can you please indicate how I should correctly write this VBA?
Thank you for your help.

 

[JoeMo]

Hello,

I have just created with VBA a new Excel file (A) that I will develop further (i.e. run more VBA on it).
I also have a source Excel file (B) in which I have source data to run VBA scripts.

So it is important to specify from which of both Excel files I read data (A or B) or write in (only A) in my VBA.

I try the following VBA but this is giving me an error message (error 9: suscript out of range), probably because it is unclear from which file to search the data.

[source]
DataSourceFile = Range("LastFileEdited")
Set W2bk = Workbooks (DataSourceFile) 'error 9: suscript out of range
Workbooks(W2bk).Open
[/source]

Can you please indicate how I should correctly write this VBA?
Thank you for your help.

When you try to set W2bk to be a workbook that is not open you generate an error. Open the workbook first, then set W2bk to it.

 

[zeno]

When you try to set W2bk to be a workbook that is not open you generate an error. Open the workbook first, then set W2bk to it.

JoeMo,

I already had file A open when I ran the script.
If I understand you correctly, I write the line 3 before line 2, which gives the following:

[source]
DataSourceFile = Range("LastFileEdited")
Workbooks(W2bk).Open 'JoeMo: Put this line before Set W2bk = ...
Set W2bk = Workbooks (DataSourceFile) 'error 9: suscript out of range
[/source]

When I run this, I receive error 13: type mismatch.

 

[JoeMo]

JoeMo,

I already had file A open when I ran the script.
If I understand you correctly, I write the line 3 before line 2, which gives the following:

[source]
DataSourceFile = Range("LastFileEdited")
Workbooks(W2bk).Open 'JoeMo: Put this line before Set W2bk = ...
Set W2bk = Workbooks (DataSourceFile) 'error 9: suscript out of range
[/source]

When I run this, I receive error 13: type mismatch.

You can't use W2bk until you set it as an object. Try it like this:

DataSourceFile = Range("LastFileEdited")
Workbooks(DataSourceFile).Open    'JoeMo: Put this line before Set W2bk = ...
Set W2bk = Workbooks (DataSourceFile)

 

[zeno]

Thank you for your suggestion JoeMo.
But the idea is to define W2bk as the specific file, including the (a) drive, (b) the directory, and (c) the file name, so I have those 3 parts/names defined for W2bk. DataSourceFile is only 1 element of them.

I have tried the following:

Set W2bk = Workbooks((DriveForData & DataSourceDirectory) & (DataSourceFile))  ' I receive error message 9 suscript out of range
Workbooks.Open (W2bk)

Thank you!