Automatic export query to (formatted) Excel (1 Viewer)

[Rob_Belgium]

Hi Access giants,

I would like to run a query in Access (2002 & 2010), (button on a form) with the possibility to automatically export the results to a formatted Excel file with functions like 'wrap text', colored headings etc. I also noticed that for example values (columns) with type 'time' are shown as 1-1-1900 in excel after export.
Who can help me out?

Thanks all in advance for your help.

Cheers,

Rob, Belgium

 

[mdjks]

I am doing exactly that and having a problem with Excel opening twice but the formating is working fine. Any suggestions on where I am opening Excel twice would be greatly appreciated.

To get the formatting record a macro of what you want in Excel and use that code within Access.

Private Sub btnVACR_Click()
Dim uName As String
uName = Environ("username")

If Not IsNull(Me.lstMajor.Value) Then
Dim mjR As String
mjR = Me.lstMajor.Value
If Not IsNull(Me.lstComm.Value) Then
Dim coM  As String
coM = Me.lstComm.Value
End If
End If
Dim dAt As String
dAt = " " & Format(Now(), "mm-dd-yy")

Dim pA As String
pA = "D:\Documents and Settings\" & uName & "\Desktop\VACR " & dAt & ".xlsx"


DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
"VACRReportOnDemand", pA, True

Dim xlApp, xlBook As Object
Dim xlSheet As Object

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.workbooks.Add(pA)
Set xlSheet = xlApp.workbooks.Open(pA).sheets(1)

xlApp.Visible = True

With xlApp
.Application.sheets("VacrReportOnDemand").select
xlApp.range("G:H,J:J,L:L").select
 xlApp.Selection.Style = "Comma"
xlApp.range("M:M").select
xlApp.Selection.Style = "Percent"
xlApp.range("I:I").select
    xlApp.Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
     xlApp.Cells.select
    xlApp.Cells.EntireColumn.AutoFit
    xlApp.Selection.AutoFilter
     xlApp.range("A2").select
     xlApp.ActiveWindow.FreezePanes = True
  
End With
Set xlApp = Nothing
Set xlBook = Nothing




End Sub

 

[Rob_Belgium]

Thank you very much! You really helped me out!

Cheers!

Robbert