PDA

View Full Version : [SOLVED] How do I open variable file in Excel?



robturner
02-08-2005, 12:48 PM
How do I open a file in VBA or a macro to open a file# that is entered into cell B1?

where ACCT is just a number refering an acct# which also
is the acct # and .xls
If B1 is 12345 then it opens 12345.xls, etc.(over 100 files
on the drive)

What I would need is something like...


ActiveCell.Range("B1").select
Let ACCT = ActiveCell
Workbooks.Open Filename:="C:\subdirectories\[ACCT].xls"

But I cannot find anything that will do something like this... (Is it possible?:help )

Many thanks!!
Robert

Zack Barresse
02-08-2005, 01:01 PM
Hi Robert, welcome to VBAX!

You can use something like this ..



On Error GoTo NotOpen
Workbooks(Range("B1").Text & ".xls").Activate
Exit Sub
NotOpen:
Err.Clear
Workbooks.Open "C:\Documents and Settings\Rob\Desktop\" & Range("B1").Text & ".xls"
On Error GoTo 0

.. in a routine. This should work for you. Let us know how it works.

Btw, I moved your post into the Excel Help forum. :)

Edit: Error Handling

robturner
02-08-2005, 03:15 PM
Zack,

It works!! Thanks very much!!:bow:

I really appreciate it!!

Robert

Zack Barresse
02-08-2005, 03:21 PM
No problem at all Robert!

Btw, did you know that you can mark your own threads as Solved? Goto Thread Tools --> Mark Solved --> Perform Action.

Take 'er easy! :)