HI, I'm pretty new at programming Access. I have a query that has 1.5 million rows. I need to export 4,000 of those rows to excel and save just that 4,000 in its own workbook that increments by 1 in the name of the workbook. This export needs to perform this way until all 1.5 million are exported to a workbook.
Here is the code I have pieced together researching on the internet. I'm getting a compile error: Object Required on the colored line and I'm not sure how to code the saving of each workbook with a file name that increments by one. ie. export_1.xlsx, export_2.xlsx. I have to do this because I will have 398 workbooks to track. the reason I have to save the query results like this is because we are uploading this to a portal that only allows 4,000 rows at a time.
Here is my code. Any help would be WONDERFUL!!
Sub Export2Excel()
Dim x1APP As Excel.Application
Dim x1WB As Excel.Workbooks
Dim x1WS As Integer
'Dim filename As String
'filename = Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name))) & "Import_1" & "-" & Format(Date, "yyyymmdd") & ".xlsx"
Dim acRng As Variant
Dim xlRow As Integer
Dim qry As QueryDef
Dim rst As Recordset
Set x1APP = CreateObject("Excel.Application")
Set xlWB = Workbooks.Open("K:\Sea\Accounts Receivable\Pinterest\Sales Report Template_upld files\2013 Oct\Export_1.xlsx")
Set x1WS = ActiveSheet.Range("A1")
xlRow = (x1WS.Columns("A").End(xlDown).Row)
Set qry = CurrentDb.QueryDefs("Pinterest_Query")
Set rst = qry.OpenRecordset
Dim c As Integer
c = 1
xlRow = xlRow + 1
Do Until rst.EOF
For Each acRng In rst.Fields
x1WS.Cells(xlRow, c).Formula = acRng
c = c + 1
Next acRng
xlRow = xlRow + 1
c = 1
rst.MoveNext
If xlRow > 10 Then GoTo rq_Exit
Loop
rq_Exit:
rst.Close
Set rst = Nothing
Set x1WS = Nothing
xlWB.Close acSaveYes
Set x1WB = Nothing
x1APP.Quit