Export to Excel

[rkalapura]

I have a report in Access 2010 which I often need to send to Excel. I am using a command button to export it to excel. It first open the report, export with formating and then close the report. It does everything fine in the macro command sequence.



My problem is when I open the excel report, which is exported, the whole report open up in "Grey-out". I could see only the headers. Then, if I select the whole report in Excel and change the font color to black it is good - it looks normal.

Any body know why it is happening? Anything I am doing wrong or anything I need to do before I start the export process?

Note: "ExportwithFormatting" macro command has many option like Object Type, Object Name, Output Format, Output File, Auto Start, Template, Encoding and Output Quality. Is that anything I need to do with "Encoding". I couldn't find any option in the drop down. Can you please tell what this encoding do?

Thank you and God Bless You.

[lzhao7573]

I tried to export a report to Excel, it retain the original format in Access.

[roaftech]

I used to do this a lot when analysing traffic violations (up to 40,000 per month!); using the data analysis abilities in Access and then transferring the outcome to Excel for its superior formatting and graphing facilities. IIRC, I used to export from Access in .csv format and then open that file in Excel, using a workbook with several worksheets. The first worksheet was formatted to receive the block of csv data; the subsequent sheets were set up to reformat the data as required and then subsequent sheets contained the graphs, report tables etc. This process does assume that you are transferring a matching dataset each time - the values and the number of records can change but the columns must remain consistent for this to work. It's not essential but it also helps if you understand Excel's array formulas.