File opening via Macro with varying file name

[TomFish79]

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

 

[Tom Ogilvy]

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

 

[K Dales]

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"

 

[TomFish79]

That's great! So simple!

Thanks for getting back so quick...

To