Querying more than 65536 rows error in Excel 2013
Older Excel versions (prior to 2007) indeed have a limit of some 65k+ rows per worksheet. Run your code and reference any object Lib starting w/Excel 2007 and up (max 1,048,576 rows per worksheet, Lib version correspondingly 12.x and up). Pertinent to your case, try to use a notation [Sheet1$A:A]
instead of [Sheet1$A1:A65537]
Rgds,
Is it possible to see more than 65536 rows in Excel 2007?
Here is an interesting blog entry about numbers / limitations of Excel 2007. According to the author the new limit is approximately one million rows.
Sounds like you have a pre-Excel 2007 workbook open in Excel 2007 in compatibility mode (look in the title bar and see if it says compatibility mode). If so, the workbook has 65,536 rows, not 1,048,576. You can save the workbook as an Excel workbook which will be in Excel 2007 format, close the workbook and re-open it.
Issue with ODBC Object connection - Open limitation to 65k rows
I think this is because you are calling old version library through this part of connection string:
Provider=Microsoft.ACE.OLEDB.12.0
You should try
Provider=Microsoft.ACE.OLEDB.16.0
Upd: Answer was here Excel as database - query more than 65536 rows? interesting. You cannot mention rows, or you'll get error.
Excel VBA ADO query loop for too many rows
Thanks to Xabier and Alan for their contributions to the solution.
Xabier for the cleaner code.
Alan for identifying the underlying issue.
The issue is that when the original table gets split onto the new sheet to account for the excess rows, even though the sheet exists, the ADO was not recognizing it yet. It's not until you leave the current sub that it recognizes it (at least that is my understanding from all of the discussion, testing, and ultimately my solution).
So, as a high level summary:
To account for too many rows and getting the "Access cannot find your table" error message, I would let the first 60k run on the current sheet and then copy the next 60k (or less) to a new sheet.
In order for the ADO to recognize the newly created sheet, I placed the connection and recordset functionality into a separate sub and called it from within my original sub by passing any parameters that I needed it to have to run successfully.
I then came back to my original sub, deleted the newly created sheet, and then looped through this process again until I had accounted for the entire original sheet.
So, for example, 140k rows would run the first 60k on the original sheet, run the next 60k off of a new sheet, and the last 20k off of another new sheet.
The key really was to put the recordset into a new sub and call it, and this was only necessary because the ADO was not seeing the newly created sheets without first leaving the original sub.
Thanks for all input, and here is my code below in case you are interested. Please note the code will look similar (with some modifications) to the cleaner version that Xabier posted.
Sub Risk_Init_Pivot(FA_PQ As String, Risk_Init As String, SubChannel As String, MyMonth As String)
Application.ScreenUpdating = False
Dim SheetRange1 As Range, MyRange As Range
Dim SR1_LastRow As Double, SR1_LastColumn As Double, NewRowCount As Double, SR1_FirstRow As Double
Dim i As Integer, j As Integer, MyLoop As Integer
Dim Table1 As String, MySQL As String
Dim wsOrigin As Worksheet, wsTarget As Worksheet, MySheet As Worksheet
Set wsTarget = Sheets("Risk Init Pivot")
Set wsOrigin = Sheets("Fanned File")
'Initiate
wsTarget.Cells.ClearContents
'Find Range Coordinates Dynamically
If wsOrigin.AutoFilterMode Then
If wsOrigin.FilterMode Then wsOrigin.ShowAllData
End If
SR1_LastRow = wsOrigin.Cells(wsOrigin.Rows.Count, "A").End(xlUp).Row
SR1_LastColumn = wsOrigin.Cells(SR1_LastRow, wsOrigin.Columns.Count).End(xlToLeft).Column
MyLoop = WorksheetFunction.RoundUp(SR1_LastRow / 60000, 0)
NewRowCount = 0
For j = 1 To MyLoop
'First Time
If SR1_LastRow > 60000 Then
NewRowCount = SR1_LastRow - 60000
SR1_LastRow = 0
SR1_EndRow = 60000
SR1_FirstRow = 1
'Set the tables equal to the respective ranges
Set SheetRange1 = wsOrigin.Range("A" & SR1_FirstRow & ":" & Cells(SR1_EndRow, SR1_LastColumn).Address)
'Pass the table address to a string
Table1 = SheetRange1.Address
'Convert the string into a query table - have to get rid of dollar signs for it to work
Table1 = "[" & wsOrigin.Name & "$" & Replace(Table1, "$", "") & "]"
'Does this until NewRowCount falls into last time
ElseIf NewRowCount > 60000 Then
NewRowCount = NewRowCount - 60000
SR1_FirstRow = SR1_EndRow + 1
SR1_EndRow = SR1_FirstRow + 59999
Sheets.Add After:=wsOrigin
Set MySheet = ActiveSheet
wsOrigin.Rows("1:1").Copy
MySheet.Rows("1:1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
wsOrigin.Range("A" & SR1_FirstRow & ":" & Cells(SR1_EndRow, SR1_LastColumn).Address).Copy
MySheet.Range("A2").PasteSpecial xlPasteValues
Set MyRange = MySheet.UsedRange
'Set the tables equal to the respective ranges
Table1 = MyRange.Address
'Convert the string into a query table - have to get rid of dollar signs for it to work
Table1 = "[" & MySheet.Name & "$" & Replace(Table1, "$", "") & "]"
'Last Time
ElseIf (NewRowCount > 0) And (NewRowCount <= 60000) Then
SR1_FirstRow = SR1_EndRow + 1
SR1_EndRow = SR1_FirstRow + NewRowCount
NewRowCount = 0
Sheets.Add After:=wsOrigin
Set MySheet = ActiveSheet
wsOrigin.Rows("1:1").Copy
MySheet.Rows("1:1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
wsOrigin.Range("A" & SR1_FirstRow & ":" & Cells(SR1_EndRow, SR1_LastColumn).Address).Copy
MySheet.Range("A2").PasteSpecial xlPasteValues
Set MyRange = MySheet.UsedRange
'Set the tables equal to the respective ranges
Table1 = MyRange.Address
'Convert the string into a query table - have to get rid of dollar signs for it to work
Table1 = "[" & MySheet.Name & "$" & Replace(Table1, "$", "") & "]"
'Does this the first time if under 60k rows
Else
SR1_FirstRow = 1
'Set the tables equal to the respective ranges
Set SheetRange1 = wsOrigin.Range("A" & SR1_FirstRow & ":" & Cells(SR1_LastRow, SR1_LastColumn).Address)
'Pass the table address to a string
Table1 = SheetRange1.Address
'Convert the string into a query table - have to get rid of dollar signs for it to work
Table1 = "[" & wsOrigin.Name & "$" & Replace(Table1, "$", "") & "]"
End If
Call MyRecordset(Table1, FA_PQ, SubChannel, MyMonth, wsTarget)
If Not MySheet Is Nothing Then
Application.DisplayAlerts = False
MySheet.Delete
Application.DisplayAlerts = True
End If
Next j
'Tidying the sheet
wsTarget.Cells.AutoFilter
wsTarget.Columns.AutoFit
Sheets("Control Sheet").Activate
Application.ScreenUpdating = True
End Sub
Sub MyRecordset(Table1 As String, FA_PQ As String, SubChannel As String, MyMonth As
String, wsTarget As Worksheet)
Dim MyConn As ADODB.Connection
Dim MyRecordset As ADODB.RecordSet
Dim i As Integer
Dim LastRow As Double
'Set Up Connection Details
Set MyConn = New ADODB.Connection
MyConn.CommandTimeout = 0
Set MyRecordset = New ADODB.RecordSet
MyConn.Open "Provider = Microsoft.ACE.OLEDB.12.0;" & _
"Data Source = " & Application.ThisWorkbook.FullName & ";" & _
"Extended Properties = ""Excel 12.0 Xml;HDR=YES;IMEX=1"";"
Set MyRecordset.ActiveConnection = MyConn
'SQL Statement
MySQL = Sheets("Control Sheet").Range("C14").Value
MySQL = Replace(MySQL, "@Table1", Table1)
MySQL = Replace(MySQL, "@Year", Sheets("Control Sheet").Range("C5").Value)
MySQL = Replace(MySQL, "@FA_PQ_Input", FA_PQ)
MySQL = Replace(MySQL, "@SubChannel", SubChannel)
MySQL = Replace(MySQL, "@MyMonth", MyMonth)
'Run SQL
MyRecordset.Open MySQL, MyConn, adOpenKeyset, adLockOptimistic
'Paste Data with headers to location
If wsTarget.Range("A2").Value = "" Then
wsTarget.Range("A2").CopyFromRecordset MyRecordset
Else
LastRow = wsTarget.Cells(wsTarget.Rows.Count, "A").End(xlUp).Row
wsTarget.Range("A" & LastRow + 1).CopyFromRecordset MyRecordset
End If
For i = 0 To MyRecordset.Fields.Count - 1
wsTarget.Cells(1, i + 1) = MyRecordset.Fields(i).Name
With wsTarget.Cells(1, i + 1)
.Font.Bold = True
.Font.Size = 10
End With
Next i
MyRecordset.Close
Set MyRecordset = Nothing
MyConn.Close
Set MyConn = Nothing
'Putting Nulls in the blanks
wsTarget.Cells.Replace What:="", Replacement:="0", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
End Sub
Related Topics
How to Use a SQL Update Statement to Add 1 Year to a Datetime Column
Can the "In" Operator Use Like-Wildcards (%) in Oracle
Comma-Separated List as a Result of Select Statement in Oracle
How to Combine Two Rows and Calculate the Time Difference Between Two Timestamp Values in MySQL
Workaround for Ora-00997: Illegal Use of Long Datatype
SQL Query of Haversine Formula in SQL Server
Selecting Distinct Values for Multiple Columns
Ora-30926: Unable to Get a Stable Set of Rows in the Source Tables When Merging Tables
Merge Identical Databases into One
Not Deferrable Versus Deferrable Initially Immediate
Oracle SQL - Sum and Group Data by Week
How to Get a Value from Previous Result Row of a Select Statement
Combine Consecutive Date Ranges
SQL Oracle Left Join and Subquery Error: Ora-00905: Missing Keyword
Left Outer Join and an Additional Where Clause