Originally Posted by
June7
Certainly. I am sure there are a variety of ways to accomplish.
If you are writing out to Excel one field to one cell at a time, have a counter variable that increments in the loop. When it reaches 45, create a new sheet or workbook, reset the counter, repeat.
This is the syntax I am using to iterate my recordset and export the data to Excel - how would I add a counter to add a new worksheet since I am adding the worksheet and setting it to a variable, and adding the export column to a variable etc?
Code:
Set excelApp = CreateObject("Excel.Application")
Set xlWB = excelApp.Workbooks.Add
Set xlWS = xlWB.Worksheets(1)
Set xlc = xlWS.Range("A2")
Set rs = CurrentDb.OpenRecordset("Select [OrderNum], [OrderBy] FROM [Dsys] WHERE [ItemID] = '1528' AND [AExists] = 'Yes' ORDER BY [ItemID]", dbOpenDynaset)
Debug.Print rsSub.RecordCount
colNo = 1
Do While Not rs.EOF
For Each fld In rs.Fields
excelApp.Cells(2, colNo).Value = fld.Value
colNo = colNo + 1
Next fld
rs.MoveNext
Loop