I have a report that, for too many reasons to explain, I have been asked to put into Excel. I have quite a bit of structure/formatting in my report, so a straight dump will not due.
I was told once that I would need to make a template in Excel that would be called up whenever I needed to run the report.
As you are aware, if you export a report to Excel, you loose most if not all the formatting.
If you want a formatted report, you'll have to export the data and build a report in Excel. You might have the data in one work sheet and the report in a second worksheet.
I ran into this problem on a previous assignment. The client wanted a version of the Access Report in Excel and wanted all the formatting to be the same (colors, bolding, alignment, etc).
Since I don't like to have things scattered about and like to keep everything centralized to my MS Access environment, I came up with a solution that I thought was rather slick (at least I felt it was slick). I already had the report created that was being printed from within Access. So what I added was VBA coding on the OnPrint events of each section to drive Excel Automation. Basically, as the report printed, it built an excel spreadsheet as it went. It took painstaking coding and a lot of testing to make sure everything lined up correctly. It became even more fun when they wanted the Totals to contain formulas rather than an exact dump from the database (this was so they could "massage" the numbers and see how the totals will change).
Unfortunately, the manner in which I did this would be hard to put into a demo fashion because it doesn't do much good without data to back it up. THe data is confidential in nature and would surely get me in a load of trouble.
My suggestion would be to do a search on Excel Automation code. Pretty much anything you can do through the Excel Macro VBA coding you can cause to happen in Access.
Here is a sample snippet that you can put in a global module and execute to see that what it does
Code:
Public Function CreateXLS()
Dim xls As Excel.Application
Dim strFileLocation As String
Set xls = New Excel.Application
With xls
.Workbooks.Add
.Visible = True
.DisplayAlerts = False 'Stops all messages from appearing
End With
xls.Range("A1").Select
xls.ActiveCell.Value = "Hi There"
xls.ActiveCell.Offset(1, 1).Select
xls.ActiveCell.Value = "Hi There Again"
Set xls = Nothing
End Function
I don't know if this points you in the right direction. Basically I have a counter variable that increments everytime the report prints a new line of data. This value (a Long Datatype) is what I use in my references to cells. I.E.:
Code:
xls.Range("A" & lngCounter).Select
It should also be noted that this will not be a quick output. The more rows to be created the longer it takes. I had one report that took 20 minutes to generate the Excel Spreadsheet, but their logic was that it beat doing it by hand (which was taking them a day to do)
A tip:
If you need to know how to do some formatting stuff (i.e. bolding, underlining, borders, centering, color, etc.). I will open a new Excel SPreadsheet, start a macro recording session, perform the actions I want to automate and then stop the recording session. Then you can Edit the macro to see the VBA code it generates. This code can almost be copied and pasted to your Access VBA "as-is". The only edits you need to make is adding your Object variable (in my example above it was xls). Excel doesn't need to put that there because it is already referencing the object by default because it is running within Excel.
As to my Wolverine Avatar:
Click on the 'User CP' button above
Click on the 'Edit Options' Link listed across the top
Scroll to the bottom and notice the Avatar section. Click the 'Change Avatar' button
Make sure the 'No' option for 'Use Avatar' isn't selected
Make sure the 'Yes' option for the 'Use Custom Avatar' is selected
Now you can choose to either link to an off-site image or upload the image. If you link to an off-site image then you put in the url in the 'You can enter an URL of your avatar:' field. If you plan on uploading the image, then click on the 'Browse' button in the 'You can upload an avatar from your computer:' field.
"start a macro recording session, perform the actions I want to automate and then stop the recording session. Then you can Edit the macro to see the VBA code it generates."
harra,
This is one of the best tips I've gotten off the board in a while. Thanks.
