472,789 Members | 1,042 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,789 software developers and data experts.

Export data from an ACCESS report into Excel with correct formatting

105 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?

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Public Function ExportRequest()
  4.  
  5. Dim whereCond As String
  6. Dim LCS As String
  7. Dim Contractor As String
  8. Dim PDDivision As String
  9. Dim OpArea As String
  10. Dim WorkType As String
  11. Dim appExcel As Object
  12. Dim workBook As Object
  13. Dim workSheet As Object
  14.  
  15. Forms!frmPrintPlan!cmbLCS.SetFocus
  16. LCS = Forms!frmPrintPlan!cmbLCS.Text
  17.  
  18. Forms!frmPrintPlan!cmbContractor.SetFocus
  19. Contractor = Forms!frmPrintPlan!cmbContractor.Text
  20.  
  21. Forms!frmPrintPlan!cmbPDDivision.SetFocus
  22. PDDivision = Forms!frmPrintPlan!cmbPDDivision.Text
  23.  
  24. Forms!frmPrintPlan!cmbOpArea.SetFocus
  25. OpArea = Forms!frmPrintPlan!cmbOpArea.Text
  26.  
  27. Forms!frmPrintPlan!cmbWorkType.SetFocus
  28. WorkType = Forms!frmPrintPlan!cmbWorkType.Text
  29.  
  30.  
  31. SetPlanRecordSource
  32.  
  33. DoCmd.OutputTo acOutputReport, "rptPlan", acFormatXLS, "S:\Workgroups\APC Power Delivery-Contract Services\Herbicide and Tree Trimming Database\Reports Export\Budget Plan.xls", True
  34.  
  35. ' Open an existing spreadsheet
  36. Set appExcel = GetObject("S:\Workgroups\APC Power Delivery-Contract Services\Herbicide and Tree Trimming Database\Reports Export\Budget Plan.xls")
  37.  
  38. ' Show spreadsheet on screen
  39. appExcel.Application.Visible = True
  40. appExcel.Parent.Windows(1).Visible = True
  41.  
  42. ' Autofit all columns on a worksheet
  43. Set workSheet = appExcel.Worksheets(1)
  44. workSheet.Cells.EntireColumn.AutoFit
  45. workSheet.Cells.Columns("N").ColumnWidth = 55
  46. workSheet.Range("N:N").WrapText = True
  47. workSheet.Cells.EntireRow.AutoFit
  48.  
  49. ' Turn prompting OFF and save the sheet with original name
  50. appExcel.Application.DisplayAlerts = False
  51. appExcel.Application.Save
  52. appExcel.Application.DisplayAlerts = True
  53.  
  54. ' Release objects
  55. Set workSheet = Nothing
  56. Set workBook = Nothing
  57. Set appExcel = Nothing
  58.  
  59. End Function
  60.  
  61. '*************************************************************'
  62.  
  63. Public Function SetPlanRecordSource()
  64.  
  65.     Dim rptPlanRoutineMainSQL As String
  66.     Dim rptPlanSplitYearMainSQL As String
  67.  
  68.     'Set the record source for rptPlanRoutine
  69.  
  70. 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 " & _
  71.                                "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"
  72.  
  73.  
  74. SetSQL "qryRptPlanRoutine", rptPlanRoutineMainSQL & GetPlanRoutineWhereCond
  75.  
  76.  
  77. 'Set the record source for rptPlanSplitYear
  78.     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"
  79.  
  80.  
  81. SetSQL "qryRptPlanSplitYear", rptPlanSplitYearMainSQL & GetPlanSplitYearWhereCond
  82.  
  83.  
  84. End Function
  85.  
  86.  
Any help would be greatly appreciated!!!!

Thanks in advance!!!
Oct 4 '07 #1
3 7445
nico5038
3,080 Expert 2GB
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)
Oct 6 '07 #2
jmarcrum
105 100+
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???
Oct 9 '07 #3
nico5038
3,080 Expert 2GB
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)
Oct 9 '07 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

1
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...
4
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...
6
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...
2
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...
0
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...
0
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...
3
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).
10
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...
2
hemantbasva
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...
0
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...
0
linyimin
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...
0
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 ...
14
DJRhino1175
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...
0
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...
5
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...
0
by: lllomh | last post by:
How does React native implement an English player?
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.