Exporting Formatted Report to Excel

[LadyDi]

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?

 

[adaytay]

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?

Hope this helps,

Ad

 

[GaryPanic]

quickie on this why are you exporting to xls??
most reports can be in within Access,

should you need to export to xls then you have to use output to etc and format the heading in VB -

 

[LadyDi]

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?

 

[adaytay]

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?

Ad

 

[ted.martin]

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.

 

[LadyDi]

I do not know much about VBA code. I know the basics, but that is about it. I wouldn't even know where to begin to create a pdf file "on the fly".

 

[LadyDi]

Exporting Formatted Reports to Excel

How could I write a macro in Excel to copy the formatting over from Access?

 

[gemma-the-husky]

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

 

[LadyDi]

Exporting Formatted Reports to Excel

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?

 

[gemma-the-husky]

without programming

just open the query

then export /save as/ select an xls format

then you can import it into excel

---------
programatically you use

docmd.transferspreadsheet, and the parameters include source query, destination file name, output column headers etc

-----------
onvce you have the xls sheet open it, and copy it all into your production excel document

then you can copy and paste etc

as long as the format is guaranteed to be of a certain nature, you can speed the excel processing with macros

 

[GolfProRM]

With a little VBA code, you can do quite a bit of formatting from within Access.

In my case, I've got a transfer spreadsheet command followed by a call of my modify format function:

170    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "QueryName", "\\server\filename.xls", True
180    Call ModifyExportedExcelFileFormats("\\server\filename.xls", "QueryName")

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.

 

[GolferGuy]

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.

 

[LadyDi]

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?

 

[GolfProRM]

What is your call statement?

Make sure you have quotes around the location of the spreadsheet.

 

[GolferGuy]

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.

 

[LadyDi]

Exporting A Formatted Report to Excel

My call statement looks like this

Call ModifyExportedExcelFile("\\usnfs1\RoyerD\2008 Projects Currently On Hold.xls", "Month End On Hold")

The specific error message says "Wrong number of arguments or invalid property assignment".

 

[GolfProRM]

Did you change the name of the function?

The function I provided is ModifyExportedExcelFileFormats

You just have ModifyExportedExcelFile

 

[LadyDi]

Exporting Formatted Reports to Excel

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.

 

[GolfProRM]

You should create a module where you can then paste the guts of the function.

I would think you can call it from the On_Open event, but I've never tried.