Problem in Excel 2007 when Range beyond 65536 in SELECT statement

  • Thread starter Thread starter Thomas Huang
  • Start date Start date
T

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
 
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 laid this down on his screen :
sSQL = "SELECT A,B FROM [Sheet1$A66000:E66005];"
Click to expand...

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];"
 
Back
Top