Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! :) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 - ADO - More than 65536 rows - Problem ? | Excel Programming | |||
Excel 2007: UDF can't reference row below 65536 | Excel Programming | |||
How do I get my Excel 2007 worksheet to go beyond 65536 rows? | Excel Worksheet Functions | |||
Excel 2007 - ADO - More than 65536 rows - Problem ? | Excel Programming | |||
Why Excel 2007 still have the 65536 raw limit? | Excel Discussion (Misc queries) |