Formatting exported query for Excel (1 Viewer)

I have a query I want exported to Excel. I then want to format the Excel file for column width, font, font size, bolding, etc. I created the macro in Access 2007 to export the query and then open in Excel. I then converted the macro to VBA. No problems there. Tested and everything is working. In Excel I recorded a macro doing all the formatting changes I want.

This is where my confusion comes in. I am not sure what needs to be done in Access to automate the formatting from the Access side. I have searched the forum but most of the posts I see are trying to format the exported data and also accomplish something else. I am not sure what code is for the formatting and what code is for the other stuff.

Below is the VBA code to export the query to Excel:

Private Sub butExcelAllUsers_Click()
On Error GoTo butExcelAllUsers_Click_Err
DoCmd.OutputTo acOutputQuery, "qryMyTasksAllUsers-ForExcel", "Excel97-Excel2003Workbook(*.xls)", "", True, "", 0, acExportQualityPrint

butExcelAllUsers_Click_Exit:
Exit Sub
butExcelAllUsers_Click_Err:
MsgBox Error$
Resume butExcelAllUsers_Click_Exit
End Sub


Can you help me with the additional code needed to perform the formatting once the query has been exported? Like I said, I already have the Excel VBA code for all my formatting, I just need the Access code to make it work.

This has some formatting in it. I like that module, it works well for me.

After more searching I found some code which I thought would do the trick. I am now getting a compile error: sub or function not defined. The export to Excel is working just fine. I just need help on the formatting issues.

Here is the code behind my button:

Private Sub butExcelAllUsers_Click()
On Error GoTo butExcelAllUsers_Click_Err
DoCmd.OutputTo acOutputQuery, "qryMyTasksAllUsers-ForExcel", "Excel97-Excel2003Workbook(*.xls)", "C:\TGG Workflow\MyTasks-AllUsers.xls", True, "", 0, acExportQualityPrint
Call ModifyExportedExcelFileFormats("C:\TGG Workflow\MyTasks-AllUsers.xls")
butExcelAllUsers_Click_Exit:
Exit Sub
butExcelAllUsers_Click_Err:
MsgBox Error$
Resume butExcelAllUsers_Click_Exit
End Sub

This is the code in my module:

Option Compare Database
Public vStatusBar As Variant

Public Sub ModifyExportedExcelFileFormats(sFile As String)
On Error GoTo Err_ModifyExportedExcelFileFormats
Application.SetOption "Show Status Bar", True
vStatusBar = SysCmd(acSysCmdSetStatus, "Formatting export file... please wait.")
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("qryMyTasksAllUsers-ForExcel").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "Task Status"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Acct#"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Category"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Sub-Category"
Range("F2").Select
Range("G:G,J:J").Select
With Selection
.HorizontalAlignment = xlCenter
End With
Range("J1").Activate
Selection.NumberFormat = "mm/dd/yyyy;@"
Range("J1").Select
ActiveCell.FormulaR1C1 = "Complete By"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Difficulty"
Range("I1").Select
ActiveCell.FormulaR1C1 = "Priority"
Rows("1:1").Select
Selection.Font.Bold = True
Columns("A:J").Select
Columns("A:J").EntireColumn.AutoFit
ActiveWindow.ScrollColumn = 1
Columns("C:C").Select
With Selection
.HorizontalAlignment = xlLeft
End With
Range("A2").Select
End With
Set xlApp = Nothing
Set xlSheet = Nothing
vStatusBar = SysCmd(acSysCmdClearStatus)
Exit_ModifyExportedExcelFileFormats:
Exit Sub
Err_ModifyExportedExcelFileFormats:
vStatusBar = SysCmd(acSysCmdClearStatus)
MsgBox Err.Number & " - " & Err.Description
Resume Exit_ModifyExportedExcelFileFormats

chappy68 said:

After more searching I found some code which I thought would do the trick. I am now getting a compile error: sub or function not defined. The export to Excel is working just fine. I just need help on the formatting issues.

Click to expand...

It seems like the export is NOT working just fine if you get a compile error. Were in your code is the error? Also, when you paste code, use the code tool to wrap it.

Let me clear this up. When I say the the export to Excel is working, I mean the query is exported to Excel and the file opens and all my data is present. The problem is when I try to run the code to format the Excel file. The red hi-lited code is what comes up when the code bombs. I have also attached a pic of the error screen.


I uploaded a pic of the where the error occurs. I am letting my ignorance show when I tell you that I don't know how to use any of the debugging tools. Thanks for the code info. I obviously didn't know about that either.

I tried the site you suggested but I couldn't split out the code the export the file and the code to format the exported file.

Access does not know what Range is. You have to say that Range is a feature of Excel. In the link I gave you, you can see how that was done:


For your code, it looks like you should have


Edit- the link I gave you, you really don't have to break out the export and the modify parts. There is no reason to seperate them. All you need to do is

I think I just need to start over. I have to much code going around that is mixed with the site you suggested and the one I found in the forum. Once I get it setup, I will see if the problems still exist.

Thanks for the help so far. Will keep you informed.