ADO is just blowing me out of the water

[Glory]

It's so unbelievably fast at extracting data that took whole minutes.

The only problem I'm facing is when a file is placed anywhere except the "default database directory", ADO can't find it.

The error I receive reads "Microsoft Jet Database Engine Could Not Find the Object". I found this bug report on MSDN.

Is the only solution to put the file you're grabbing from in the same folder as the querying file?

 

[Glory]

Sample code for looping through workbooks to grab multiple sheets and fill a single sheet:

Public Sub Test5()
'Loops through to use the same variables multiple times
'Grabs multiple sheets from multiple books
 
Dim rsCon As Object
Dim rsData As Object
Dim szConnect As String
Dim szSQL As String
 
ThisWorkbook.Sheets("Sheet2").Cells.ClearContents
 
szSQL = "SELECT * FROM [" & "Sheet1$A:J" & "];"
 
Set rsCon = CreateObject("ADODB.Connection")
Set rsData = CreateObject("ADODB.Recordset")
 
For i = 1 To 2
 
    Select Case i
 
        Case 1
            szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & ThisWorkbook.Path & "\book1.xls" & ";" & _
                        "Extended Properties=""Excel 8.0;HDR=Yes"";"
 
        Case 2
            szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & ThisWorkbook.Path & "\book3.xls" & ";" & _
                        "Extended Properties=""Excel 8.0;HDR=Yes"";"
    End Select
 
    rsCon.Open szConnect
    rsData.Open szSQL, rsCon, 0, 1, 1
 
    a = (ThisWorkbook.Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row) + 1
 
    ThisWorkbook.Sheets("Sheet2").Range("A" & a).CopyFromRecordset rsData
 
    rsData.Close
    rsCon.Close
 
Next
 
Set rsData = Nothing
Set rsCon = Nothing
 
End Sub

 

[Sektor]

Glory,
I wonder what for did ya write so?

szSQL = "SELECT * FROM [" & "Sheet1$A:J" & "];"

Is it easier to write following way?

szSQL = "SELECT * FROM [Sheet1$A:J];"

 

[Glory]

The idea is that eventually there will be a variable in place of the range.

Do you have any ideas about the question in the original post?

 

[JWilder]

You could try on a copy of your file
Replace this line
"Data Source=" & ThisWorkbook.Path & "\book1.xls" & ";" & _
With
"Data Source=C:\Whatever\book1.xls" & ";" & _

Whatever being the folder name book1.xls appears in

 

[Sektor]

If you have Excel 2007/2010 installed, the try this connection string:

"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & sDatabaseFile & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=Yes;IMEX=1;"";"

 

[Glory]

Using the path consistently fails, which was what set me looking for other answers.

Sektor: That's exactly what I was using originally, but had to change it because I went to a machine with Excel '03 that only has Jet available.

Thanks anyway.

 

[jimrward]

sektor is correct, source just points to the folder, change the filename in the select statement, here is snip from some of my code

'****
' Open JET connection to our data file folder
'****

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & StrPathToTextFile & ";" & _
        "Extended Properties=""text;HDR=YES;FMT=Delimited"""

'****
' Build SQL strings to get unique categories in no order
'****

StrSQL = "SELECT DISTINCT ActionGroup FROM " & StrFile