ms access 2000 report to excel keeping the formatting

Posted on 2014-10-17
Last Modified: 2014-10-21
is there a way to output a ms access 2000 report from a form (using the docmd.openreport ....)   to an excel file keeping the formatting.
Question by:Shen
  • 3
  • 2
  • 2
  • +1
LVL 36

Expert Comment

ID: 40386785
No.  The exports to Excel and Word are less than satisfactory.  The only way to do this is with OLE automation.  You can use TransferSpreadsheet to transfer the data but then you will need to write VBA code to add any formatting and totaling.
LVL 26

Expert Comment

ID: 40387134
No.  To get satisfactory results you must marshal the data in Access and put it into Excel in a desired fashion and then fix Excel's formatting as desired.  One, more involved, approach to doing so is here.  This Asker required specific data to be placed in specific columns.  That approach involved walking down a recordset field by field, record by record, tracking the current cell, and putting the data into cells one at a time.  Afterward, code to format can be applied.

TransferSpreadsheet will knock query data into Excel from the Access side.  CopyFromRecordset will pull data from a defined Access recordset if Excel is the application being automated and manipulated.

But, uiltimately, no.  If you want good-looking spreadsheets, it's DIY.
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 166 total points
ID: 40391798
If you're creating a new Excel workbook, then you can also use a Template file in Excel, if your report formatting won't change. Just create the Template file to look like you want, and then create a new Workbook based on that template. You'd still have to move the data over to Excel using Automation, as the other Experts have suggested. I've done this several times with heavily formatted Excel "reports", and it works fairly well.
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

LVL 36

Expert Comment

ID: 40392153
I once had a very complicated report that I built in Access but the client wanted to keep the report to a single page and nothing I did in Access would keep it from rolling over to a second page.  I ended up adding the export code into the format event of the Access report.  Since all the calculations were done, I just copied the data row by row, cell by cell.  At the end, I went back and did some basic formatting and set the option to fit to page which was the whole purpose of the exercise.

I don't have the code handy but if it will suit your purpose, I can dig it out tomorrow evening if no one has offered a better option before that.

Author Comment

ID: 40392185
After reading the replies, automation looks very complicated for me.  I think i will generate another form where the user enters additional information and then output the form/report to a pdf file.
LVL 36

Assisted Solution

PatHartman earned 167 total points
ID: 40392244
Exporting the report to a PDF retains all formatting so as long as you don't need to modify the exported file, PDF is by far the best solution.  But the export to PDF is not without pitfalls.  OutputTo does not have the where option that OpenReport and OpenForm have so to get a PDF with just the selected data, you will need criteria in the report's RecordSource query.

Where SomeID = Forms!theformthatrunstheexport!txtSomeID
LVL 26

Accepted Solution

Nick67 earned 167 total points
ID: 40392468
After reading the replies, automation looks very complicated for me.
It is relatively complex, but after you master it you have a VERY handy tool in your arsenal for when someone demands Excel output.  Any time some wants your data in throwaway format, Excel is the go-to choice, so it is not like you will be learning something as a one-off.  But the choice is yours.

Kicking something out as a PDF, so long as it only needs to be read-only is easiest.  One of the very nice things that gets around the pitfalls that @PatHartman notes is to open the report in Preview and THEN export it.  Reports exported as PDF's will automagically pick up the report's .Caption property as the filename IF they are in Preview when exported.  This allows you to fully automate the filename (not the path!) without having to code that in procedure that does the export.

Private Sub SaveAsOfficePDF(stDocName As String)
Dim FormatValue As String
If Application.Version > 11 Then
    FormatValue = "PDF Format (*.pdf)"
    FormatValue = acFormatRTF
End If

    DoCmd.OpenReport stDocName, acPreview
    DoCmd.OutputTo acOutputReport, stDocName, FormatValue, "SomeValidFilePath\" & Reports(stDocName).Caption & ".pdf"
    DoCmd.Close acReport, stDocName, acSaveYes
End Sub

Open in new window


Author Closing Comment

ID: 40394347
I will try the last code to generate the pdf file.

Thnak you all.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question