I am trying to export a report from my database to Excel. Access is exporting the data, but it does not export the formatting. For instance, I have a line separating sections of my report and that line does not export to Excel with the rest of the report. How can I get the formatting to export to Excel along with the data?
Unfortunately you can't do this - as you have discovered, when you export a report into Excel it only pulls the data through.
I guess it would be possible to do this - however you'd need quite a bit of coding and playing around with the query behind the report. Alternatively, you can always use Lebans ReportToPDF function if you're using a version of Access prior to A2007 and can then export the report as a PDF - are you trying to preserve the formatting so you can email the report etc?
I am exporting this report to Excel because my director requested that I do that. My director has also requested that I do something so that anyone can run this report and it will look the same as if I had done it (meaning that the formatting would remain the same). If I can't do this through Access, is there another way that I can do it? Any suggestions?
Hmmm... yep have come across co. directors like that before... makes your job *sooo* much easier, don't they!
Other than completely re-creating this report from scratch in Excel, the only other option I would consider would be to create a PDF document "on the fly". How comfy are you with using VBA and setting up new functions?
One way may be to write a little macro in Excel to do the formating for you once the data has been exported and the form opened. You could put it onto a menu button on each pc.
The problem is that although Access may LOOK a bit like a series of spreadsheets, thats not what it is.
And accordingly you cannot format data tables in the same way as you can format a spreadsheet. Thats why you prepare reports etc, because then you can format them.
What you could do is output the access data to a clean data sheet, and then use these references in the cells on your published datasheet
You could use an Excel macro to do this easy enough
How can I make the report output as a "Clean Data Sheet"? I haven't written many macros, so could you tell me how I should write the macro you mentioned in you message?
Public Sub ModifyExportedExcelFileFormats(sFile As String, sSheet As String)
10 On Error GoTo Proc_Error
Dim xlApp As Object
Dim xlSheet As Object
20 Set xlApp = CreateObject("Excel.Application")
30 Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)
40 With xlApp
50 .Application.Sheets(sSheet).Select
60 .Application.Rows("1:1").Select
70 .Application.Selection.Font.Bold = True
80 .Application.range("A1").Select
90 .Application.Selection.AutoFilter
100 .Application.Cells.Select
110 .Application.Selection.Columns.AutoFit
120 .Application.range("A1").Select
130 .Application.Activeworkbook.Save
140 .Application.Activeworkbook.Close
150 .Quit
160 End With
Exit_Proc:
170 Set xlApp = Nothing
180 Set xlSheet = Nothing
190 Exit Sub
My code opens the sheet, bolds the columns in the first row, then autofilters the row and auto-fits the columns. When it's done, it saves and exits Excel (all behind the scenes). I just provide a message box when the export is complete.'
A little trial and error, and a some reading of the help file (especially within Excel VBA), and you can do just about anything.
LadyDi,
If what management wants is a nice formated report for people to view, then a PDF document is a very nice way to go. If management wants it in Excel so people can make changes (why change a report???) then Excel it is. But a PDF is VERY easy to make, it is just the same as printing to a printer from Access. In fact, that is the user interface that is used to create a PDF. You can buy (a lot cheaper than the time it will take to format an Excel report) a PDF printer driver and then the PDF can be either e-mailed to put in a common area on a network drive, the same as would happen to an Excel file.
I originally sent this report to my director in "Snapshot" format. She said that she would prefer to have it in Excel so that Salesreps could go into the report and filter. I am trying to use the code that GolfProRM mentioned. However, I keep getting an error stating that I have too many arguments for the "Call" statement. What do I need to do?
LadyDi,
If you get any more errors, show us your code that produced the error along with the error message. Otherwise it is just a guess when trying to help you. Ryan (GolfProRM) is doing a great job for you here.
I corrected the name, but I'm still getting the same error message. Would it have anything to do with where I put the code? I put the code on the On Open event of the report.