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!!!
3 7445
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)
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???
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)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Steven Stewart |
last post by:
I have a user who has been using Excel for a while to keep statistics
and print reports. She finds using it cumbersome because of long
formulas and a lot of copying and pasting.
I have designed...
|
by: Gary Wright |
last post by:
I have an Access 2K database split into front and back. Quite often the
users want to do some data analysis that I have not created a report for so
they want to export some subset of the data into...
|
by: Robin Cushman |
last post by:
Hi all,
I need some help -- I'm working with an A2K database, using DAO, and
am trying to read records into a Crystal Report and then export it to
a folder on our network as an Excel...
|
by: PerryC |
last post by:
Is there a way to export an MS Access Report to Word/Excel that looks
EXACTLY like it appears in Access? When I export to Excel, only
certain data appears, titles, headings... all missing. The...
|
by: hank755_ca |
last post by:
Hello,
I have a report in Access which I want to export to Excel with the
formatting intact. The formatting is rather complex with several
headings and fields with borders. I have tried the...
|
by: JayDawg |
last post by:
If anyone is willing to provide some consulting pro-bono, or for a very
small charge I would really love the asistance.
If anyone can fix my problem here online that would be even better.
Here...
|
by: yovation |
last post by:
Hi,
I have a 3 table database.
1 parent
1 child
1 child of child
I would like to get the data into excel as 1 sheet (similar to a
grouped report).
|
by: ConnollyS |
last post by:
Is it possible to export a report from Access to Excel and keep all the formmating.
i.e. Lines, boxes etc...
Or do you need to create an excel template and then use a macro or some VB code to...
|
by: hemantbasva |
last post by:
Note We need to have a template on server for generating report in multiple sheet
as we do not had msoffice on server
moreover this require a batch job to delete excel file created by the...
|
by: Rina0 |
last post by:
Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...
|
by: linyimin |
last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
|
by: Taofi |
last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same
This are my field names
ID, Budgeted, Actual, Status and Differences
...
|
by: DJRhino1175 |
last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this -
If...
|
by: Rina0 |
last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
|
by: DJRhino |
last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer)
If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _
310030356 Or 310030359 Or 310030362 Or...
|
by: lllomh |
last post by:
How does React native implement an English player?
|
by: Mushico |
last post by:
How to calculate date of retirement from date of birth
|
by: DJRhino |
last post by:
Was curious if anyone else was having this same issue or not....
I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
| |