| 100+
P: 105
|
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!!!
| |
Share this Question
| Expert 2.5K+
P: 3,072
|
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)
| |
| 100+
P: 105
|
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???
| |
| Expert 2.5K+
P: 3,072
|
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)
| | | |  Question stats - viewed: 6277
- replies: 3
- date asked: Oct 4 '07
|
