retrieving data from variable workbooks

[lutan]

Hi,

I am trying to retrive data from variable workbooks based on the value of a
cell contained in the receiving workbook for example

Cell B1 contains the text "June"
I would like to return values from the workbook saved in C:\.....\june.xls
and extrapolating on that if I enter August in B1 I want to retrieve data
from C:\....\August.xls

Any help would be greatly appreciated

Thanks in advance

 

[Mike H]

Try this

Sub stance()
Path = "C:\" 'change to suit
fnam = Sheets("Sheet1").Range("B1").Value & ".xls"
Workbooks.Open Filename:=(Path & fnam)
End Sub

With (say) June in B1 this will open a workbook called June.xls in the root
of C

Mike

 

[lutan]

Hi Mike,

Should I put this code into a module or into sheet 1 code, also will this
then allow me to update the receiving wokbook with data from the June
spreadsheet ie cell D6 in the main workbook needs to update with cell E17
from the June / August workbook

Thanks

Lutan

 

[Mike H]

Hi,

This now reads in the value from E17 in the June workbook and writes that to
D17 in the workbook from which the code is run. It will run as worksheet or
module code.


Sub standard()
Path = "C:\" 'change to suit
fnam = Sheets("Sheet1").Range("B1").Value & ".xls"
Workbooks.Open Filename:=(Path & fnam)
myValue = ActiveSheet.Range("E17").Value
ActiveWorkbook.Close savechanges = False
Sheets("Sheet1").Range("D17").Value = myValue
End Sub