Open Workbook using a variable

Hi,

I am a VBA newbie and am learning the hard way. Can anyone please help?

I am trying to run a programme with all the code in 1 workbook (CodeWbk.xls) and all the data in another (DataWbk.xls)
I need to be able to get the user to input the name of the data file he is using and then both save this and then use it as a variable so as to know which xls has the data. My codewbk.xls then should, using vba, open the correct file.

I am struggling to open a workbook with a variable. The workbook open command just doesn't seem to work for me no matter what I do. (probably just doing wrong things)

Also where and how would I set a global variable so that this workbook name (Datawbk.xls) is available to all modules.

Regards,

Alan

Does this help?

Please Login or Register  to view this content.

hth
Ajay

Thanks Ajay,
This part works. My problem is that when I try to refer to this workbook I get Run time error 13 - Type mismatch.
One example is listed here:

Workbooks(Src_wbk).sheets("Control").select
Thanks for the assistance.

Regards,

Alan

I really am sorry. I had not thought through the whole issue as outlined at Excel guru.

Will not do it again.

Alan

Hi,
Sorry again - got the code tags right this time.



Thanks to all who have helped so far. I am obviously still doing something dumb. Any ideas.

When I run this test routine it works fine up to the point where I “Set File_Name=Workbooks.Open(File_Name). Up to this point the variable File_Name reads correctly as evidenced by the MsgBox.

When the Workbooks.Open runs it automatically runs the Workbook Deactivate procedure. The system then “loses” the values in the public variables.
I have tried removing the Workbook deactivate procedure altogether and still get the same result – my variable loses its value.
As a result the MsgBox line gets “Run time error 438 – Object doesn’t support this property or method”
The following line “ File_Name.Sheets("Control").Cells(1, 26).Select “ gets the error “Run time error 1004 – Select method of range class failed”

I neglected to tell you that I am using Excel 2003 – could this be the issue?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thanks Romperstomper,
How would one then handle the next line.

I need to be able to direct the programmes to use sheets in one or another workbook by referring to a variable.

Regardsw,

Alan