File opening via Macro with varying file name

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

workbooks.open "C:\My Documents\Test\" & Workbooks( _
"Master.xls").Worksheets("Sheet1").range("B9").Value & _
".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" <[email protected]> wrote
in message news:[email protected]...
>
> 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...

Tom