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.
PatHartman
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.
https://www.experts-exchange.com/questions/28514101/Moving-reports-from-Access-to-Excel-with-formatting.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.
https://www.experts-exchange.com/questions/28514101/Moving-reports-from-Access-to-Excel-with-formatting.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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I will try the last code to generate the pdf file.
Thnak you all.
Thnak you all.