Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
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
Join the community of 500,000 technology professionals and ask your questions.