Export report to Excel and format (2000/XP)

Topic

New Reply

Having painstakingly created a report to mimic output previously typed into an Excel spreadsheet, I’ve now been told the output is also required in Excel.

I have exported queries/tables before using transfer spreadsheet and done some basic formatting (such a column A width = x, text= 12pt BOLD etc). This was a few good years ago thought.

Just wondering if someone has an example of doing this where their report includes group headings and the detail section may contain subreports.

Edit: Have had a quick look at report as exported fromt he file menu – it doesn’t look ‘too’ bad. However, I have 2 major issues.

1. Text is truncated – huge problem as there is a memo sized ‘remarks’ column
2. The second column contains text similar to 1/1, 1/1a, 1/2, 1/2a, 1/2c etc – the text without letters has turned into numbers e.g. 1/1 =39448, 1/2 = 39479, 1/3 = 39508 ???????

Reply | Quote

Replies

Thinks you may be up for quite a deal of VBA programming to do what you want.

With that text field you could create the illusion of a text field by appending a space to the end of that field in the access query.

Reply | Quote

I think you’d be better off exporting a table or query, and modifying the resulting worksheet as needed.

Reply | Quote

That’s kind of what I’m after really.

I’ve tried various things and it almost looks ok with mimimal manual modification needed. Unfortunately, on closer inspection I’ve found on exporting a report with many subreports (each one as a column), the subreport data gets put in the same column in excel.

Fortunately, there are <500 records so even if I come up with some clunky inefficient code it shouldn't take too long to run it.

I think I need to do something with nested recordset loops, i.e first record, paste the columns into excel, move to query for 1st related table, paste data etc, somehow counting so I can move around cells in the worksheet using relative offsets.

Reply | Quote

Good luck! Feel free to ask questions if needed.

Reply | Quote

Thanks, I don’t think it’s going to be as hard as I thought.

I found this https://www.planet-source-code.com/vb/scrip…=47409&lngWId=1%5B/url%5D and this http://www.aspfree.com/c/a/Database/Data-A…from-Excel-VBA/%5B/url%5D pretty helpful.

Reply | Quote