SQL in Excel seems to truncate output

[crozfader]

I have the following code I wrote a long time ago based on information found on the internet.

What it does is execute a SQL query on a given excel sheet and copies the results to the Destination sheet.

To use the following, I have a Sub that calls the executeSqlQuery sub, for example:

Public sub testQuery()
executesqlQuery "SELECT * from [Sheet2$]", "Sheet3"

end sub


I just noticed that this doesn't work 100% well. Following the example, if sheet2 has 100,000 lines, the procedure only returns 18000 lines (and copies these lines to sheet3)... It seems that it just truncates the result recordset for some reason... I don't know how to fix it really.

Any suggestions? I think it worked correctly at some point.

By the way, you have to add a reference to Microsoft Active X objects (2.8) for this work.


Public Sub executesqlQuery(query As String, destination As String)

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection

path = ThisWorkbook.FullName
MsgBox path

With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & path & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"
.Open
End With


Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset


'Dim myQuery As String

'myQuery = InputBox("Enter SQL QUERY:")


'On Error Resume Next
myQuery = query


rs.Open myQuery, cn, adOpenStatic, adLockOptimistic

destinationsheet = destination


ThisWorkbook.Sheets(destination).Cells.Clear

On Error Resume Next
ThisWorkbook.Sheets(destination).Range("A1").Value = rs.Fields(0).Name

For i = 1 To 300
ThisWorkbook.Sheets(destination).Range("A1").Offset(0, i).Value = rs.Fields(i).Name
Next i

ThisWorkbook.Sheets(destination).Range("A2").CopyFromRecordset rs

rs.Close
Set rs = Nothing

cn.Close

End Sub

 

[crozfader]

Bump?

 

[xenou]

Is your data well structured -

• no blank rows or blank columns anywhere inside the grid
• consistent data types -- all numbers, all text, or all dates in each column
• avoids numeric text values in the same column as normal text values - these are always trouble spot with Excel queries.

What version of Excel are you using? Also do you think the query has behaved this way before, and you didn't notice, or is this a sudden, "new", issue?

ξ