Export Data from a closed workbook

Hi:

Find the attached.

You will have to add the following references in your VBA Editor:

• Microsoft ActiveX Data Objects 6.1 Library

Sub test()

Dim rsData As ADODB.Recordset

rsFile$ = ThisWorkbook.Path & "\" & Sheet1.Range("C1") & ".xlsx"
strConn$ = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                    "Data Source=" & rsFile & ";" & _
                    "Extended Properties=""Excel 12.0;HDR=Yes"";"
 
rsSQL$ = "SELECT * FROM [Sheet1$]"
Set rsData = New ADODB.Recordset

rsData.Open rsSQL, strConn, , adOpenUnspecified, adLockUnspecified
Sheet1.Range("E4").CopyFromRecordset rsData

End Sub

Note: I am assuming that you will be storing all your files in the same folder as your macro file.

Thanks