Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies. Only from Platform Scholar.
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.
Join the community of 500,000 technology professionals and ask your questions.