Exporting Formatted Report to Excel (1 Viewer)

[LadyDi]

Exporting Formatted Reports to Excel

I've never created a module. How do I do that? Do I just go to the "Module" screen, click create new Module, and paste all the code that you mention or just the code for the public sub?

 

[GolfProRM]

You want just the public sub.

You are correct about how to create a module. You don't want to use the exact same name as the function though (it can confuse Access).

To use that code as a module, you'll want to change the word sub to function. I've updated the code below -- I also realized I left off the end line - that could have created an issue.

Public function ModifyExportedExcelFileFormats(sFile As String, sSheet As String)

    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 function

end function

 

[LadyDi]

Now, I am getting an error message that reads "Subscript Out of Range". After I click OK to that, it highlights the line that reads:

50 .Application.Sheets(sSheet).Select


What should I do now?

 

[GolfProRM]

The value you enter as the second parameter in the call should match the sheet name in the excel file. Double check that value.

 

[LadyDi]

Now I am getting the error message "Month End On Hold Already Exists". When I click OK to this message, it highlights this line of code:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Month End On Hold", "\\usnfs0\RoyerD\2008 Projects Currently On Hold", True

What should I do now?

 

[GolfProRM]

That's because you can't export to a sheet that already exists.

You have two options - you can either create a unique name for the spreadsheet (like a date-stamp on the spreadsheet name) or you can delete the old spreadsheet before exporting the new data.

I've got date-stamp code if you want it, but I'm not sure if you can delete a spreadsheet with VB code.

 

[LadyDi]

Exporting Formatted Reports to Excel

I would like the date stamp code.

Then I have one more question. The report that I am exporting to Excel had totals and subtotals (based on the salesrep and the salesrep's area). Is there anyway to get those totals and subtotals to export to Excel as well?

Thank you so much for your help and your patience!

 

[GolfProRM]

Okay -- put these lines right before your transferspreadsheet line

Dim stamp As String
stamp = Month(Date) & Day(Date) & Year(Date)

Then, to date stamp the spreadsheet, you do this:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Month End On Hold", "\\usnfs0\RoyerD\2008 Projects Currently On Hold" & stamp & ".xls", True
Call ModifyExportedExcelFile("\\usnfs1\RoyerD\2008 Projects Currently On Hold" & stamp & ".xls", "Month End On Hold")

 

[GolfProRM]

Then I have one more question. The report that I am exporting to Excel had totals and subtotals (based on the salesrep and the salesrep's area). Is there anyway to get those totals and subtotals to export to Excel as well?

I'm guessing so, although I've never done it. If I didn't have a bunch of work to get done today, I'd do a google search and see what I could find. You might try doing a search to see if anyone has built the code to do it.

 

[Timtropolis]

I would like the date stamp code.

Then I have one more question. The report that I am exporting to Excel had totals and subtotals (based on the salesrep and the salesrep's area). Is there anyway to get those totals and subtotals to export to Excel as well?

Thank you so much for your help and your patience!

Hi LadyDi,

I was looking for a thread that had to deal with this as I'm going thru the same exercise at the moment. What I have found is that unfortunatley you cannot export footer information. My assumption is that excel can only recognize so much. I've tried to put various info in my report footers but nothing comes out in excel. If someone knows a way to get output from the footers, please feel free to correct me as I'm a bit stumped on this one.

All this being said, the report export function does do a nice job of taking your formatting and grouping and keeping it intact.

 

[LadyDi]

Exporting Formatted Reports to Excel

I am trying to put the code you mention above in another database. However, when I try to run it, it takes forever. Do you have any idea what I could be doing wrong? I just copied and pasted the code you show and then I changed the query name and the file name to reflect the information in my database.

 

[DreamGenius]

@GolfProRM

Thanks for posting this. I've fiddled around with it a little, including putting in the missing Proc_Error, and my exported data is now nicely formatted to the end-users requirements.

You've saved me a lot of time!

 

[XLvm3X]

yes i know... resurrecting a dead thread!

i feel like im soo close here but i keep getting compiler errors, either object required errors or expected end function error

Function ExportWFormatting()
nme = Format(Now, "mmddhhnnss")


    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "PrevMonthQ", "\\rhino\users\Common\Manufacturing Machines\OEE\DTreports\" & nme & ".xlsx", True
    Call ModifyExportedExcelFileFormats("\\rhino\users\Common\Manufacturing Machines\OEE\DTreports\" & nme & ".xlsx", "DTReport")
        
    Public Sub ModifyExportedExcelFileFormats(sFile As String, sSheet As String)

    On Error GoTo Proc_Error
    
    Dim xlApp As Object
    Dim xlSheet As Object
    
    Set xlApp = CreateObject("Excel.Application")
    Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)
    
        With xlApp
            .Application.Sheets(sSheet).Select
            .Application.Rows("1:1").Select
            .Application.Selection.Font.Bold = True
            .Application.Range("A1").Select
            .Application.Selection.AutoFilter
            .Application.Cells.Select
            .Application.Selection.Columns.AutoFit
            .Application.Range("A1").Select
            .Application.ActiveWorkbook.Save
            .Application.ActiveWorkbook.Close
            .Quit
       End With




Exit_Proc:
    Set xlApp = Nothing
    Set xlSheet = Nothing
      Exit Function
      

End Function