We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
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
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
|If condition and Date Ranges||13||44|
|add a column label to a list object using VBA||2||18|
|Bulk update/recode of survey text responses to numerical values via a lookup table using macros||8||35|
|Help to find the duplicates||8||11|