• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

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.
0
Shen
Asked:
Shen
  • 3
  • 2
  • 2
  • +1
3 Solutions
 
PatHartmanCommented:
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
 
Nick67Commented:
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
 
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
PatHartmanCommented:
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
 
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.
0
 
PatHartmanCommented:
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
 
Nick67Commented:
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
 
ShenAuthor Commented:
I will try the last code to generate the pdf file.

Thnak you all.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now