Sub ExtractData() Dim cnn As ADODB.Connection Dim rs1 As ADODB.Recordset Dim WBCopyFrom As String, rngCopyFrom As String Dim rngCopyTo As String Dim PathName As String Dim strSQL1 As String 'Initiailze variables 'WBCopyFrom = "TestingTesting.xls" '<--This works WBCopyFrom = "TestingTesting.xlsx" '<--This does not work rngCopyFrom = "rngFrom" rngCopyTo = "rngTo" 'Create connection to closed workbook PathName = ThisWorkbook.Path & "\" & WBCopyFrom Set cnn = New ADODB.Connection With cnn .Provider = "Microsoft.Jet.OLEDB.4.0" .ConnectionString = "Data Source=" & PathName & ";Extended Properties=Excel 8.0;" .CursorLocation = adUseClient .Open '<-- Error: "External table is not in the expected format." End With 'Create SQL string strSQL1 = "SELECT * FROM [" & rngCopyFrom & "]" 'Create recordset Set rs1 = New ADODB.Recordset 'Open recordset rs1.Open strSQL1, cnn, adOpenStatic, adLockOptimistic 'Copy to worksheet Sheet1.Range(rngCopyTo).CopyFromRecordset rs1 'Clean up rs1.Close cnn.Close End Sub
|consolidate from multiple folders||5||39|
|Microsoft 365 versus MicroSoft 2013/2016||8||62|
|Update As Well As Add||6||34|
I need to trigger Application.WorksheetFunct
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
13 Experts available now in Live!