| 100+ |
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!!!
| |
