Solved

ms access 2000 report to excel keeping the formatting

Posted on 2014-10-17
8
281 Views
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.
0
Comment
Question by:Shen
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 34

Expert Comment

by:PatHartman
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.
0
 
LVL 26

Expert Comment

by:Nick67
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.
http://www.experts-exchange.com/Database/MS_Access/Q_28514101.html

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

Expert Comment

by:PatHartman
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.
0
Backup Your Microsoft Windows Server®

Backup 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.

 

Author Comment

by:Shen
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.
0
 
LVL 34

Assisted Solution

by:PatHartman
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
0
 
LVL 26

Accepted Solution

by:
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)"
Else
    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

0
 

Author Closing Comment

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

Thnak you all.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup 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.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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…
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…

747 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