Transferring data from a closed workbook

[arunlgt]

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.

[ZVI]

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

Regards,
Vladimir