Copy from one workbook to another without opening the second workbook

[JoeDogs]

Hi there,

I have created a macro that copies from one workbook and pastes into a seperate workbook but I would like to have it so that the workbook in which I am getting my info from (copying from) does not open but the macro still runs.

Im not sure if this is possible, but if it is, I would really appreciate any help!

Thanks in advance!

[offthelip]

I am not sure why you want to copy data without opening the file, but what you could do is make a copy of the source workbook using filecopy, then open the copy. You can set screen updating off while you do this so nobody can see the file which is opened. Does this meet your requirements?

[jolivanes]

Sub Get_Data_From_Closed_Workbook()
Dim i As Long, j As Long
For i = 1 To 3 '<---- 3 Sheets to copy from '<----- Change to suit
For j = 1 To 10 '<---- 10 Columns Each Sheet to copy from '<----- Change to suit
With Sheets(i)
With .Range(.Cells(1, j), .Cells(35, j)) '<----- 35 Rows here. Make as large as required
.Formula = "='C:\Joe Dogs Folder\[Joe Dogs File Name.xlsm]Sheet" & i & "'!RC" '<------Path and name of file to copy from
.Value = .Value '<----- Optional
End With
End With
Next j
Next i
End Sub

[mancubus]

Sub vbax_60204_import_data_from_closed_workbook()
'requires a reference to Microsoft ActiveX Data Objects 2.8 Library (in VBE, Tools - References)

Dim cn As New ADODB.Connection, rs As New ADODB.Recordset
Dim dbName As String, strSQL As String
Dim j As Long

With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With

dbName = "C:\MyFolder\MySubFolder\MyExcelDataBaseFileName.xlsm"

cn.Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbName & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1""")


strSQL = "SELECT * FROM [MySheetNameClosedFile$]" 'do not forget $ sign after sheet name
rs.Open strSQL, cn, adOpenStatic, adLockReadOnly

With Worksheets("MySheetNameOpenFile")
.Cells.Clear
For j = 1 To rs.Fields.Count
.Cells(1, j).Value = rs.Fields(j - 1).Name '"Fields" is a 0 based collection
Next j
.Range("A2").CopyFromRecordset rs
End With

rs.Close
cn.Close

End Sub