Don't want to create two files (1 Viewer)

[mdjks]

I am exporting an Access query to Excel and formating the workbook but having a problem with Excel opening twice. The formating is working fine. Any suggestions on where I am opening Excel twice would be greatly appreciated.

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

 

[jamesmor]

I think these two lines are both telling your xlApp to open a workbook, the second telling it what sheet to open.

Set xlBook = xlApp.workbooks.Add(pA)
Set xlSheet = xlApp.workbooks.Open(pA).sheets(1)p

basically making your app open twice to do the work of once.