Problem in Excel 2007 when Range beyond 65536 in SELECT statement

[Thomas Huang]

Dear friends,

Below is a function that used for testing in Excel 2007 VBA.

------------------------------------------------------
Sub aa()

Set conn = CreateObject("ADODB.Connection")
conn.Open "provider=Microsoft.ACE.OLEDB.12.0;extended properties='excel 12.0;imex=1';data source=" & ThisWorkbook.FullName

Sql = "select A,B from [sheet1$A66000:E66005]"

Sheet1.Range("A2").CopyFromRecordset conn.Execute(Sql)

End Sub

 

[GS]

I'd try a completely different approach. Here's how I would do it...

Sub QueryWorksheet()
Dim rsData As ADODB.Recordset
Dim sConnect As String, sSQL As String

sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" & ThisWorkbook.FullName & ";" _
& "Extended Properties=Excel 12.0;"

sSQL = "SELECT A,B FROM [Sheet1$A66000:E66005];"

Set rsData = New ADODB.Recordset
rsData.Open sSQL, sConnect, adOpenForwardOnly, adLockReadOnly, _
adCmdText

'Check to make sure you got data
If Not rsData.EOF Then
Sheet1.Range(A2").CopyFromRecordset rsData
Else
MsgBox "No records returned!", vbCritical
End If
End Sub

 

[GS]

GS laid this down on his screen :

sSQL = "SELECT A,B FROM [Sheet1$A66000:E66005];"

If the above doesn't return existing records (assumes there are records
in the specified range) then try this...

sSQL = "SELECT * FROM [Sheet1$A66000:E66005];"