If you want all of this report data into one Excel file with multiple sheets, it can be done but it's a little tricky the way I do it. I use this VB code behind a form's button..
The report data comes from a query, right? You can have 2 queries (off of the original query) and create an Excel file with 2 corresponding tabs of data
Dim ExcelApp As Excel.Applicatio
Dim ExcelSheet, ExcelSheet2 As Excel.Workshee
Dim ExcelBook, ExcelBook2 As Excel.Workboo
Dim ExcelRows, NumRecords As Integer
Dim SaveToDirectory As Strin
Dim db As Databas
Set db = CurrentD
SaveToDirectory = "C:\Temp\
Set ExcelApp = CreateObject("Excel.Application"
DoCmd.OutputTo acOutputQuery, "Query-Monthly Data File-Data Part 1", acFormatXLS, SaveToDirectory & "TempDataFile.xls", Fals
ExcelApp.Workbooks.Open SaveToDirectory & "TempDataFile.xls
ExcelApp.ActiveWindow.Activat
Set ExcelBook = ExcelApp.ActiveWorkboo
Set ExcelSheet = ExcelBook.ActiveShee
ExcelSheet.Name = "Data Part 1
ExcelRows =
Do While ExcelSheet.Cells(ExcelRows, 1).Value <> "
ExcelRows = ExcelRows +
Loo
ExcelRows = ExcelRows -
ExcelSheet.Range("1:1").WrapText = Tru
ExcelSheet.Range("2:" & ExcelRows).WrapText = Fals
ExcelSheet.Range("1:1").RowHeight = 25.
ExcelSheet.Range("1:" & ExcelRows).Font.Name = "Times New Roman
ExcelSheet.Range("1:" & ExcelRows).Font.Size = 1
ExcelSheet.Range("1:1").Font.Bold = Tru
' now open other sheets/queries and add them in...
' Temp data sheet number
On Error Resume Nex
Kill SaveToDirectory & "TempDataFile2.xls" ' erase any prior occurance of this fil
On Error GoTo
DoCmd.OutputTo acOutputQuery, "Query-Monthly Data File-Part 2", acFormatXLS, SaveToDirectory & "TempDataFile2.xls", Fals
ExcelApp.Workbooks.Open SaveToDirectory & "TempDataFile2.xls
ExcelApp.ActiveWindow.Activat
Set ExcelBook2 = ExcelApp.ActiveWorkboo
Set ExcelSheet2 = ExcelBook2.ActiveShee
ExcelSheet2.Name = "Data Part 2
ExcelRows =
Do While ExcelSheet2.Cells(ExcelRows, 1).Value <> "
ExcelRows = ExcelRows +
Loo
ExcelRows = ExcelRows -
ExcelSheet2.Range("1:1").WrapText = Tru
ExcelSheet2.Range("2:" & ExcelRows).WrapText = Fals
ExcelSheet2.Range("1:1").RowHeight = 25.
ExcelSheet2.Range("1:" & ExcelRows).Font.Name = "Times New Roman
ExcelSheet2.Range("1:" & ExcelRows).Font.Size = 1
ExcelSheet2.Range("1:1").Font.Bold = Tru
ExcelSheet2.Move before:=ExcelSheet 'move it before the original shee
Set ExcelBook2 = Nothin
ExcelBook.SaveAs Filename:=SaveToDirectory & "Data File Name.xls", FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=Fals
ExcelApp.Qui
' don
Ti
----- Allyson wrote: ----
I am trying to export an Access 2000 report into excel. I
need it to break out onto seperate worksheets at each page
break in the report. Can you please help me do this
Thank yo
