Chris,
The following assumes:
1. he query to be named MyQuery
2. the the primary key field in it named ID
3. a criterion in the ID field: > GetMinID()
4. a second query named ExportQuery, which is a copy of MyQuery, with
the TopValues property set to 65535
This done, paste the following code in a general module if you use an
existing one make sure to put the public variable declaration in the
declarations section at the top, before the first sub or function) and
make sure you have an appropriate DAO reference. To do this, while in
the VBA editor go Tools > References and look for a "Microsoft DAO 3.X
Object Library" among the checked ones at the top of the list. If you
don't see one, then scroll down to find it and check it; "appropriate"
is 3.51 for A97, 3.6 for A2K or later (so 3.6 for you).
If your query / field names are different, change as required. Change
the target folder, filename and sheet names as required.
Public MinID As Long
Sub Export_Query()
Dim db As DAO.Database
Dim rst As DAO.Recordset
MinID = 0
TotalRec = DCount("[ID]", "MyQuery")
If TotalRec = 0 Then
MsgBox "No records found", vbExclamation, "Export Aborted"
Exit Sub
End If
SheetCount = TotalRec \ 20000
If TotalRec / 20000 > SheetCount Then
SheetCount = SheetCount + 1
End If
Set db = CurrentDb
For i = 1 To SheetCount
strSQL = "SELECT TOP 20000 * FROM MyQuery"
strSQL = strSQL & " ORDER BY ID"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"ExpQuery", "C:\MyFolder\MyWorkBook.xls", False, "Export" & i
Set rst = db.OpenRecordset(strSQL)
rst.MoveLast
MinID = rst.Fields("ID")
rst.Close
Next
Set rst = Nothing
Set db = Nothing
End Sub
Function GetMinID()
GetMinID = MinID
End Function
Note: this assumes a numeric ID field. In case your ID field is text,
then change the declaration from Public MinID As Long to:
Public MinID As String
and the original value assignment from MinID = 0 to:
MinID = ""
HTH,
Nikos
Chris said:
Hi,
ok, point taken as rgds multiposting...
I would much prefer to undertake this in VBA...
can you help...??
Rgds...Chris
:
See answer in macro NG.
Note: Pls do not multi-post. When in need to post to several groups (a
rather rare occurrence) cross-post instead (one posting, several NG
names in the recipient box). It makes replies appear in all NG's, making
life easier for all.
Nikos
Chris Gorham wrote:
Hi,
I'm exporting the result of a querie into Excel using TransferSpreadsheet.
Clearly I'm limited by the number of rows in Excel. I can detect the number
of required rows using Dcount but then I would like to dump the balance of
the data into the next sheet in the same Excel workbook. How can I acheive
this...??
I use Access 2000.
Thks....Chris
