ms access 2000 report to excel keeping the formatting

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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
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.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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.
ShenAuthor Commented:
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.
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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ShenAuthor Commented:
I will try the last code to generate the pdf file.

Thnak you all.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.