Exporting a Report to Excel

  • Thread starter Thread starter DDBeards
  • Start date Start date
D

DDBeards

I have a program that allows the user to select a report from a drop down
that is followed by a filter option window that I have used and it works
great. However this new project requires that each of the reports have the
option to be exported to excel. In the past I have used the following code
with just the first two Cases. stDocName is the name of the report set by the
dropdown and sFilter is the requested filter built by the users selections.
My problem is Case 3! This will do just what I want accept I can not pass it
the filter, so all the records are moved, not the filtered list. Please help.

Select Case SpitWhat 'Output Type
Case Is = 1 'Preview
DoCmd.OpenReport stDocName, acViewPreview, , sFilter,
acWindowNormal
Case Is = 2 'Print
DoCmd.OpenReport stDocName, acNormal, , sFilter, acWindowNormal
Case Is = 3 'Excel
DoCmd.SetWarnings False
DoCmd.OutputTo acOutputReport, stDocName,
"MicrosoftExcelBiff8(*.xls)", "", True, "", 0
DoCmd.SetWarnings True
End Select

Thanks in advance, Chris
 
I export an Excel spreadsheet to Excel each morning when a supervisor signs
on. It sends yesterday's list of work-product sorted by user. This helps
her keep track of who is doing things right and who's work needs improvement.
Code follows to create a new file name with the date embedded.
dtmDateOn = (Date - 1)
strMonthPart = Left(dtmDateOn, 2)
If Right(strMonthPart, 1) = "/" Then
strMonthPart = Left(strMonthPart, 1)
strDayPart = Mid(dtmDateOn, 3, 2)
If Right(strDayPart, 1) = "/" Then
strDayPart = Left(strDayPart, 1)
Else
strDayPart = Left(strDayPart, 2)
End If
strYearPart = Right(dtmDateOn, 4)
Else
strMonthPart = Left(strMonthPart, 2)
strDayPart = Mid(dtmDateOn, 4, 2)
If Right(strDayPart, 1) = "/" Then
strDayPart = Left(strDayPart, 1)
Else
strDayPart = Left(strDayPart, 2)
End If
strYearPart = Right(dtmDateOn, 4)
End If
strDateOn = strMonthPart & "-" & strDayPart & "-" & strYearPart
'this code sends the file to Excel into file S\\\mm-dd-yyyyActivity.xls
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7,
"qryAmericorpsScheduledForPaula", "\\name\name\name\name\name\Paula'sList" &
strDateOn & "Activity.xls", , "PaulasDailyList"

which is a CORE file within SharePoint. Hope this helps.

EarlCPhillips
Volunteer Programmer At Food Bank
Harvesters Community Food Network
 
Maybe exporting the query which is the report's recordsource would support
the filter option?
 
Back
Top