Problem in Excel 2007 when Range beyond 65536 in SELECT statement
 

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
-------------------------------------------------------

The range [sheet1$A66000:E66005] is not work and error occured. Is it possible to make it work?
Thank you in advance! :)

Problem in Excel 2007 when Range beyond 65536 in SELECT statement
 

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Problem in Excel 2007 when Range beyond 65536 in SELECT statement
 

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc