Hi, I run this macro to copy data from one file to another. This macro works fine…except that it opens the tcmochesty file…when copying the data. Is there any way of doing this without opening the file…thks
Sub DataExtract()
Dim objWrkBk, objSht1, objSht2, objSht3
Set objWrkBk = GetObject("U:\FIFX\EOD\PVBP\Eamonn\" & Format(Range("A1").Value, "mm_dd_yy") & "_tcmochesty.xls")
Dim testdata2 As Collection
Set testdata2 = New Collection
Set objSht2 = objWrkBk.Worksheets("USD")
For i = 1 To 40
testdata2.Add (objSht2.Cells(28 + i, 6).Value)
Worksheets("Sheet1").Cells(34 + i, 7) = testdata2.Item(i)
Next i
End Sub
Sub DataExtract()
Dim objWrkBk, objSht1, objSht2, objSht3
Set objWrkBk = GetObject("U:\FIFX\EOD\PVBP\Eamonn\" & Format(Range("A1").Value, "mm_dd_yy") & "_tcmochesty.xls")
Dim testdata2 As Collection
Set testdata2 = New Collection
Set objSht2 = objWrkBk.Worksheets("USD")
For i = 1 To 40
testdata2.Add (objSht2.Cells(28 + i, 6).Value)
Worksheets("Sheet1").Cells(34 + i, 7) = testdata2.Item(i)
Next i
End Sub