View Full Version : Transferring data from a closed workbook

03-06-2010, 08:52 PM
Hi Everyone:

My company's head office sends a spreadsheet everyday with the following title format "todaysdate_BBH_Data" = "20100303_BBH_Data".this spreadsheet can come via a email or be transferred directly into a specific network drive for example "T:\Risk".In this worksheet everyday there is information in specific cells( always at cell CD1) that has to be transferred automatically into a master worksheet-lets call it FundPerformance.xls.The data has to be transferred to the first empty cell in column D.

eg :

20100303_BBH_Data(Sheet1:cell CD1) ------> FundPerformance(Summary:first empty cell in column D)

There is ofcourse a lot more to this project.Any help on getting me started on this I would be extremely grateful

please note for security reasons I am unfortunately unable to post the workbooks here.

03-07-2010, 01:17 AM
Hi Arunlgt:

The code below puts the formula ='T:\Risk\[20100307_BBH_Data.xls]Sheet1'!CD1 into empty cell of previously open workbook "FundPerformance.xls", sheet "Summary", column D.
Not tested, without error trapping, just for the start point:

Sub Test()

With Workbooks("FundPerformance.xls").Sheets("Summary")
With .Cells(.Rows.Count, "D").End(xlUp).Offset(1)
.Formula = "='T:\Risk\[" & Format(Date, "yyyymmdd") & "_BBH_Data.xls]Sheet1'!CD1"
.Value = .Value
End With
End With

End Sub