I
icq_giggles
I have the following code that will run when stepped through, but not from on
click event - the debugger sends me to the line withe set xlsheet is. I've
tried many different forms for this - but always something kicking out. I'm
puzzled as to why it would work in step-through, but not from the on-click
event.
Private Sub cmdExport_Click()
DoCmd.OpenQuery "qryconnections", acViewPivotTable, acEdit
DoCmd.RunCommand (acCmdPivotTableExportToExcel)
'Stop
'-----------------------------------------------------
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
If fIsAppRunning("Excel") Then
Set xlApp = GetObject(, "Excel.Application")
Else
Set xlApp = CreateObject("Excel.Application")
End If
Set xlBook = xlApp.ActiveWorkbook
Set xlSheet = xlBook.Sheets(1)
'Set xlSheet = xlBook.Activesheet
xlApp.Visible = True
With xlSheet.Columns("a:c")
.ColumnWidth = 50
.WrapText = True
End With
With xlSheet.Columns("D:IV")
.EntireColumn.AutoFit
.NumberFormat = "m/d/yyyy"
End With
ExitHere:
On Error Resume Next
' Clean up
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
Exit Sub
HandleErr:
MsgBox Err & ": " & Err.Description, , "Error in ExcelPivotTable"
xlApp.Quit
Resume ExitHere
Resume
End Sub
click event - the debugger sends me to the line withe set xlsheet is. I've
tried many different forms for this - but always something kicking out. I'm
puzzled as to why it would work in step-through, but not from the on-click
event.
Private Sub cmdExport_Click()
DoCmd.OpenQuery "qryconnections", acViewPivotTable, acEdit
DoCmd.RunCommand (acCmdPivotTableExportToExcel)
'Stop
'-----------------------------------------------------
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
If fIsAppRunning("Excel") Then
Set xlApp = GetObject(, "Excel.Application")
Else
Set xlApp = CreateObject("Excel.Application")
End If
Set xlBook = xlApp.ActiveWorkbook
Set xlSheet = xlBook.Sheets(1)
'Set xlSheet = xlBook.Activesheet
xlApp.Visible = True
With xlSheet.Columns("a:c")
.ColumnWidth = 50
.WrapText = True
End With
With xlSheet.Columns("D:IV")
.EntireColumn.AutoFit
.NumberFormat = "m/d/yyyy"
End With
ExitHere:
On Error Resume Next
' Clean up
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
Exit Sub
HandleErr:
MsgBox Err & ": " & Err.Description, , "Error in ExcelPivotTable"
xlApp.Quit
Resume ExitHere
Resume
End Sub