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 34

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.
LVL 34

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.
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.


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 34

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

867 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now