Hi there,

I'm trying to open an excel file using a macro. The name of the fil
to be opened is variable, based on a cell in the main excel spradsheet
To clarify:

In "master.xls" I have a cell who's contents are "10097". I would lik
to run a macro which will open the file C:\my documents\test\10097.xls

Obviously, when the cell in "master.xls" changes a different fil
should be opened. How easy/hard is this? I'm a complete beginner an
I normally only generate code automatically using the record mar
function.

Thanks, To

--
TomFish7
-----------------------------------------------------------------------
TomFish79's Profile: http://www.excelforum.com/member.php...fo&userid=2629
View this thread: http://www.excelforum.com/showthread.php?threadid=39576

workbooks.open "C:\My Documents\Test\" & Workbooks( _
"Master.xls").Worksheets("Sheet1").range("B9").Val ue & _
".xls"

If you want the workbook to be opened each time you edit the cell
[Master.xls]Sheet1!B9 in the example, then you could use the change event
for Sheet1

Right click on the sheet tab of sheet1 in Master and select view code. Put
in code like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If target.count 1 then exit sub
if Target.Address = "$B$9" then
On Error Resume Next
workbooks.open "C:\My Documents\Test\" & _
range("B9").Value & ".xls"
End If
End Sub

--
Regards,
Tom Ogilvy

"TomFish79" wrote
in message ...

Hi there,

I'm trying to open an excel file using a macro. The name of the file
to be opened is variable, based on a cell in the main excel spradsheet.
To clarify:

In "master.xls" I have a cell who's contents are "10097". I would like
to run a macro which will open the file C:\my documents\test\10097.xls

Obviously, when the cell in "master.xls" changes a different file
should be opened. How easy/hard is this? I'm a complete beginner and
I normally only generate code automatically using the record maro
function.

Thanks, Tom


--
TomFish79
------------------------------------------------------------------------
TomFish79's Profile:
http://www.excelforum.com/member.php...o&userid=26295
View this thread: http://www.excelforum.com/showthread...hreadid=395764

The easy way to do it would be to record your macro with a sample file, and
then where you have the file name in the code replace it with the cell value
like in this example where I am using what is in cell A1 as the file name:

If recorded code has something like:
Workbooks.Open "C:\my documents\test\10097.xls"
Change it to read:
Workbooks.Open "C:\my documents\test\" & Range("A1").Value & ".xls"
--
- K Dales


"TomFish79" wrote:


Hi there,

I'm trying to open an excel file using a macro. The name of the file
to be opened is variable, based on a cell in the main excel spradsheet.
To clarify:

In "master.xls" I have a cell who's contents are "10097". I would like
to run a macro which will open the file C:\my documents\test\10097.xls

Obviously, when the cell in "master.xls" changes a different file
should be opened. How easy/hard is this? I'm a complete beginner and
I normally only generate code automatically using the record maro
function.

Thanks, Tom


--
TomFish79
------------------------------------------------------------------------
TomFish79's Profile: http://www.excelforum.com/member.php...o&userid=26295
View this thread: http://www.excelforum.com/showthread...hreadid=395764

That's great! So simple!

Thanks for getting back so quick...

To

--
TomFish7
-----------------------------------------------------------------------
TomFish79's Profile: http://www.excelforum.com/member.php...fo&userid=2629
View this thread: http://www.excelforum.com/showthread.php?threadid=39576