Export data from an ACCESS report into Excel with correct formatting

[jmarcrum]

I want to export a report (that contains two separate queries, 1. Current year data, and 2. split-year data) from access into excel, but everytime I run my code and export the data to excel, it looks like garbage.

I have maximize and minimize buttons on the left side of the excel spreadsheet that I don't want. I want separate tabs on the bottom of the spreadsheet that show the two queries separately from one another. But I don't know how to do all of this?

Here's my code...what can I do?

Option Compare Database
 
Public Function ExportRequest()
 
Dim whereCond As String
Dim LCS As String
Dim Contractor As String
Dim PDDivision As String
Dim OpArea As String
Dim WorkType As String
Dim appExcel As Object
Dim workBook As Object
Dim workSheet As Object
 
Forms!frmPrintPlan!cmbLCS.SetFocus
LCS = Forms!frmPrintPlan!cmbLCS.Text
 
Forms!frmPrintPlan!cmbContractor.SetFocus
Contractor = Forms!frmPrintPlan!cmbContractor.Text
 
Forms!frmPrintPlan!cmbPDDivision.SetFocus
PDDivision = Forms!frmPrintPlan!cmbPDDivision.Text
 
Forms!frmPrintPlan!cmbOpArea.SetFocus
OpArea = Forms!frmPrintPlan!cmbOpArea.Text
 
Forms!frmPrintPlan!cmbWorkType.SetFocus
WorkType = Forms!frmPrintPlan!cmbWorkType.Text
 
 
SetPlanRecordSource
 
DoCmd.OutputTo acOutputReport, "rptPlan", acFormatXLS, "S:\Workgroups\APC Power Delivery-Contract Services\Herbicide and Tree Trimming Database\Reports Export\Budget Plan.xls", True
 
' Open an existing spreadsheet
Set appExcel = GetObject("S:\Workgroups\APC Power Delivery-Contract Services\Herbicide and Tree Trimming Database\Reports Export\Budget Plan.xls")
 
' Show spreadsheet on screen
appExcel.Application.Visible = True
appExcel.Parent.Windows(1).Visible = True
 
' Autofit all columns on a worksheet
Set workSheet = appExcel.Worksheets(1)
workSheet.Cells.EntireColumn.AutoFit
workSheet.Cells.Columns("N").ColumnWidth = 55
workSheet.Range("N:N").WrapText = True
workSheet.Cells.EntireRow.AutoFit
 
' Turn prompting OFF and save the sheet with original name
appExcel.Application.DisplayAlerts = False
appExcel.Application.Save
appExcel.Application.DisplayAlerts = True
 
' Release objects
Set workSheet = Nothing
Set workBook = Nothing
Set appExcel = Nothing
 
End Function
 
'*************************************************************'
 
Public Function SetPlanRecordSource()
 
    Dim rptPlanRoutineMainSQL As String
    Dim rptPlanSplitYearMainSQL As String
 
    'Set the record source for rptPlanRoutine
 
rptPlanRoutineMainSQL = "SELECT tblFeeder.*,tblTypeofPlan.TypeofPlan AS  WorkType, tblDivisions.DivisionName, DatePart('q',tblFeeder.PlanDate) AS Qtr, DatePart('yyyy',tblFeeder.PlanDate) AS [Year], Format(DateAdd('m',(tblFeeder.DefaultCycle),tblFeeder.PlanDate),'yyyy') AS NextYear, Format(DateAdd('m',(tblFeeder.DefaultCycle),tblFeeder.PlanDate),'m/d/yyyy') AS NextPlan, (tblFeeder.UTMiles*tblFeeder.UTCost) AS UTTotal, (tblFeeder.MTMiles*tblFeeder.MTCost) AS MTTotal, (tblFeeder.RLMiles*tblFeeder.RLCost) AS RLTotal, (tblFeeder.RTMiles*tblFeeder.RTCost) AS RTTotal, qryCombineTreeTrimming.StartDate, Left([tblTypeofPlan].[TypeOfPlan],1) AS type, tblOpArea.PDDivision, tblCrews.Contractor, tblCrews.CrewCode " & _
                               "FROM (tblOpArea INNER JOIN (((tblDivisions INNER JOIN tblFeeder ON tblDivisions.Division = tblFeeder.Division) LEFT JOIN qryCombineTreeTrimming ON tblFeeder.FdrID = qryCombineTreeTrimming.FdrID) LEFT JOIN tblTypeofPlan ON tblFeeder.TypeOfPlan = tblTypeofPlan.TypeID) ON tblOpArea.OpAreaID = tblFeeder.OpArea) LEFT JOIN tblCrews ON tblFeeder.Crew = tblCrews.CrewID"
 
 
SetSQL "qryRptPlanRoutine", rptPlanRoutineMainSQL & GetPlanRoutineWhereCond
 
 
'Set the record source for rptPlanSplitYear
    rptPlanSplitYearMainSQL = "SELECT tblPlanSplitYearFeeders.*, tblFeeder.FeederName, tblFeeder.FeederNumber, [SYUTCost]*[SYUTMiles] AS SYUTTotal, [SYMTCost]*[SYMTMiles] AS SYMTTotal, [SYRTCost]*[SYRTMiles] AS SYRTTotal, [SYRLCost]*[SYRLMiles] AS SYRLTotal, [SYUTMiles]+[SYRTMiles]+[SYRLMiles]+[SYMTMiles] AS SYTotalMiles, tblFeeder.DefaultCycle, tblFeeder.LCS, tblFeeder.OpArea, tblCrews.CrewCode, tblCrews.Contractor, tblOpArea.PDDivision FROM ((tblFeeder INNER JOIN tblPlanSplitYearFeeders ON tblFeeder.FdrID = tblPlanSplitYearFeeders.FdrID) LEFT JOIN tblCrews ON tblPlanSplitYearFeeders.Crew = tblCrews.CrewID) INNER JOIN tblOpArea ON tblFeeder.OpArea = tblOpArea.OpAreaID"
 
 
SetSQL "qryRptPlanSplitYear", rptPlanSplitYearMainSQL & GetPlanSplitYearWhereCond
 
 
End Function
 
 

Any help would be greatly appreciated!!!!

Thanks in advance!!!

[nico5038]

Using the Access "Save report as excel" will indeed cause a troublesome speadsheet being build.

I would start with saving the report queries as "regular" queries and use those in a Docmd.transferspreadsheet command where the target range is indicated.

Getting the idea ?

Nic;o)

[jmarcrum]

i think i do understand what you're saying...

do you mean something like...

'Export 1st report to MS Excel in 1st worksheet
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryRptPlanRoutine", "rptPlanRoutineMainSQL" & strDateTime & ".xls"
DoCmd.Beep

and....

'Export 2nd report to MS Excel in 2nd worksheet
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryRptPlanSplitYear", "rptPlanSplitYearMainSQL" & strDateTime & ".xls"
DoCmd.Beep


Will this put them both in the same Excel file, but on two separate worksheets though???

[nico5038]

You're close, but using the same .xls for the two commands will cause the first one to be overwritten. You'll need to use the "Range" possibility to move the data into separate ranges of the same excel sheet. Just check the parms of the command.

Nic;o)