Hi I have some code that iterates through a query equal to the number of times that your data count is divided into intNbrRecs
I was doing this so that I could automate the export of Access table data (with a count of more than excel can handle) into an excel spreadsheet over multiple worksheets. So normally I would set the intNbrRecs at 65K, but for testing, I set it at 5 for now..(note, I will add the autmation of putting the 'between and" numbers into the query later)..
Anyway, the division works and it does iterate through separating the data its reading from a query (via parameter op "between and"). So, if you have 20 records and intNbrRecs is 5, then the query is run 4 times, each time asking you to enter in the starting data number (my records in the data table are numbered 1-20). So the first time would be 1-5, then 6-10 etc.
What I want is to save it to one excel sheet over multiple worksheets, but its only giving me 1 worksheet..
I've seen this before, but can't remember what I have to do to make it do that..Here's the code:
Public Sub Export_Data()
Dim fp As String
Dim fn As String
Dim intNbrWorksheets As Double
Dim fldToCount As String
Dim strObjName As String
Dim intNbrRecs As Long
Dim i As Integer
Dim strExpObjName As String
fp = "Z:\GoldPointe\MemAcct\Martin_Benson\Martin\Reports\Adhoc\quickies\"
fn = "Rpt_Data_" & Format(Date, "yyyymmdd") & ".xls"
strExpObjName = "qry_output_Mbrship_4"
fldToCount = "MBR00_SUBS_SSN"
strObjName = "tbl_Mbrship_By_Grp_3"
'intNbrRecs = 65000
intNbrRecs = 5
intNbrWorksheets = DCount(fldToCount, strObjName) Mod intNbrRecs
If intNbrWorksheets > 0 Then
intNbrWorksheets = Int(DCount(fldToCount, strObjName) / intNbrRecs) + 1
Else
intNbrWorksheets = Int(DCount(fldToCount, strObjName) / intNbrRecs)
End If
'For i = 1 To 1
For i = 1 To intNbrWorksheets
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, strExpObjName, fp & fn, True
Next i
End Sub
I was doing this so that I could automate the export of Access table data (with a count of more than excel can handle) into an excel spreadsheet over multiple worksheets. So normally I would set the intNbrRecs at 65K, but for testing, I set it at 5 for now..(note, I will add the autmation of putting the 'between and" numbers into the query later)..
Anyway, the division works and it does iterate through separating the data its reading from a query (via parameter op "between and"). So, if you have 20 records and intNbrRecs is 5, then the query is run 4 times, each time asking you to enter in the starting data number (my records in the data table are numbered 1-20). So the first time would be 1-5, then 6-10 etc.
What I want is to save it to one excel sheet over multiple worksheets, but its only giving me 1 worksheet..
I've seen this before, but can't remember what I have to do to make it do that..Here's the code:
Public Sub Export_Data()
Dim fp As String
Dim fn As String
Dim intNbrWorksheets As Double
Dim fldToCount As String
Dim strObjName As String
Dim intNbrRecs As Long
Dim i As Integer
Dim strExpObjName As String
fp = "Z:\GoldPointe\MemAcct\Martin_Benson\Martin\Reports\Adhoc\quickies\"
fn = "Rpt_Data_" & Format(Date, "yyyymmdd") & ".xls"
strExpObjName = "qry_output_Mbrship_4"
fldToCount = "MBR00_SUBS_SSN"
strObjName = "tbl_Mbrship_By_Grp_3"
'intNbrRecs = 65000
intNbrRecs = 5
intNbrWorksheets = DCount(fldToCount, strObjName) Mod intNbrRecs
If intNbrWorksheets > 0 Then
intNbrWorksheets = Int(DCount(fldToCount, strObjName) / intNbrRecs) + 1
Else
intNbrWorksheets = Int(DCount(fldToCount, strObjName) / intNbrRecs)
End If
'For i = 1 To 1
For i = 1 To intNbrWorksheets
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, strExpObjName, fp & fn, True
Next i
End Sub
