By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,661 Members | 1,319 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,661 IT Pros & Developers. It's quick & easy.

Export data from an ACCESS report into Excel with correct formatting

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?

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
Share this Question
Share on Google+
3 Replies


nico5038
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)
Oct 6 '07 #2

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???
Oct 9 '07 #3

nico5038
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)
Oct 9 '07 #4

Post your reply

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