'Item not found in this collection' while exporting table data to Excel

[SachAccess]

Hi,

Am trying to use below code in my file. I am trying to split the data for every 50,000 records.
I am getting bug at 'Set qdef = CurrentDb.QueryDefs("Chunk" & i)' this line.
It says, Item not found in this collection.

numChunks is showing as 32
maxnum is correct
File is present, path is correct.

When macro enters the loop for first time, before throwing bug, it has actually modified the excel file.
One worksheet is created in the file with name as 'Chunk', 50,001 records are pasted here.
However not able to proceed.. Am not able to understand this.
Can anyone please help me in this.



Copied from below URL

Split MS Access table into parts and export into Excel using VBA

I have an Access table of about 50000 records which I require to split into preferably 3 parts and export these parts into individual excel files or sheets using VBA. I require this, as these Excel

stackoverflow.com

Sub ExportChunks()
    Dim rs As Recordset
    Dim ssql As String
    Dim maxnum As Long
    Dim numChunks As Integer
    Dim qdef As QueryDef
    
    ssql = "SELECT COUNT(Id) FROM NewTable"
    Set rs = CurrentDb.OpenRecordset(ssql)
    
    maxnum = rs.Fields(0).Value  'total number of records
    'add 0.5 so you always round up:
    numChunks = Round((maxnum / 50000) + 0.5, 0)
    
    'On Error Resume Next 'don't break if Chunk_1 not yet in QueryDefs
    
    ssql = "SELECT TOP 50000 * FROM NewTable"
    CurrentDb.QueryDefs.Delete "Chunk"
    Set qdef = New QueryDef
    qdef.SQL = ssql
    qdef.Name = "Chunk"
    CurrentDb.QueryDefs.Append qdef
    CurrentDb.QueryDefs.Refresh
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Chunk", "D:\MMM\Personal Documents\MyAccess\Chunk.xlsx"

    
    For i = 2 To numChunks
        ssql = "SELECT TOP 50000 * FROM NewTable WHERE ID NOT IN (SELECT TOP " & (i - 1) * 50000 & " ID FROM NewTable)"
        Set qdef = CurrentDb.QueryDefs("Chunk")
        qdef.SQL = ssql
        CurrentDb.QueryDefs.Refresh
        Set qdef = CurrentDb.QueryDefs("Chunk" & i)
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, qdef.Name, "D:\MMM\Personal Documents\MyAccess\" & qdef.Name & ".xlsx"
    Next i
End Sub

 

[Minty]

That seems very inefficient to me - to select the top 50000 records to populate an IN clause in the sub query.

If your table has a unique ID Field make your initial select with that as an order ASC on it, then select the top 50000, store the max ID from that ordered list and simply select the next 50000 with an ID above that, rinse and repeat.
The id field doesn't need to be sequential just unique. The sorting will take care of the rest.

 

[SachAccess]

Hi @Minty thanks a lot for the help.