Exporting Access Report to Excel - formatting issues.

[Robeen]

Hi,

I've managed to skirt this issue in the past by exporting my Access Reports or Queries to pdf. However, now I need to be able to export to Excel spreadsheets while maintaining some level of control over how the data shows up [formatting etc] in the spreadsheet.

For example, I have a report that consists of two different reports. If I run the report in Access - or export it to pdf I see all the formatting [like the borders of the two reports, the Page & Report Headers and Footers of the two reports . . . and so on.
When I export the report to Excel, I just get the Headers and the Detail lines. I lose the Bold/Underline of the Headers, I have not been able to get a blank row to print between the data from teh two reports that go into making this report . . .

Can anyone point me to some literature that will describe how I can take charge of how my data shows up in Excel?



Thanks!

Robeen

[rpeare]

The only way I've found to do what you're asking is to cycle through the query driving the report and export the data one line at a time then create formulas in the vb script and do the formatting etc.
You'd have to use a method like this:

http://www.access-programmers.co.uk/...d.php?t=103457

but cycle through your query records and populate the excel cells one at a time and put the formatting in as you go along.

[Robeen]

Thanks! I will look through that and hopefully it will give me what I need.

[Robeen]

I know it's been a while - but - rpeare's pointer - as well as other similar web sites - helped me perform the formatting I needed in the spreadsheets I was outputting - after the fact - in a separate function.
I was able to do things like:
1. Locate the first instance of a particular Text value in a column.
2. Delete/Add a Row in the spreadsheet.
3. Change the data type of a column from text to numeric.
4. Format a cell or row or column [font-size, font-weight, color].
5. Put a particular text value in a particular cell based on conditions in my function . . .

I didn't go further than that when I was working on it because it was not required - but I remember putting a lot of extra tested code snippets in the function in case I ever need to do more with formatting data that I am putting into a spreadsheet.

One caveat: There are issues with creating and destroying the Excel, Range, Row & Column objects. When I was testing, if the function crashed and I didn't have my error-handling in place - it would leave the Excel Object open and then I couldn't DO anything with the actual spreadsheet. I would have to go into Windows Task Manager and manually kill the Excel instance that would be still running . . . more annoyance than anything. Taught me a lesson real quick about having the error handler in place to kill all open objects.

Thanks, rpeare!