Excel Automation

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




ExitHe
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
--
Thank you,

You haven't either opened or created a workbook yet.

This line is not correct:
Set xlBook = xlApp.ActiveWorkbook

It would be more like:
Set xlBook = xlApp.Workbooks.Open(varGetFileName, 0, True)

or
Set xlBook = xlApp.Workbooks.Add

The first opens an Excel file and the second creates a new workbook

You also have some issues getting out. As you are doing it, you are going
to leave an instance of Excel running. If the user tries to open excel, it
will appear to hang up. You have quit the application if you used the
CreateObject but not if you used the GetObject. In that case, you might
loose changes a user had made to a file they had open.

You also need to save and close the workbook. Here is an example from one
on my apps:

If varGetFileName "" Then
xlBook.SaveAs Filename:=varGetFileName
End If

Me.txtStatus.Visible = False
Me.Repaint
xlBook.Close
If blnExcelWasNotRunning = True Then
xlApp.Quit
Else
xlApp.DisplayAlerts = True
xlApp.Interactive = True
xlApp.ScreenUpdating = True
End If
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing


--
Dave Hargis, Microsoft Access MVP


"icq_giggles" wrote:

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




ExitHe
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
--
Thank you,

When you export the pivot table it opens Excel. I can't use
Transferspreadsheet because it won't hold the pivot table format. i do use
the getobject to get this open spreadsheet.

Again, the code works when stepped into (debug), just not when called from
the command button (normal).

I'm fairly new to the automation coding, but I need to take this pivot table
which holds dates as the detail area to excel and format it so it will show
the dates. If I dump directly into excel and try to create the pivot table
it won't give me the detail dates only counts, I've tried various other
options on this but if you have a better suggestion PLEASE share it. I
haven't worked much with PivotTables on either side.
--
Thank you,


"Klatuu" wrote:

You haven't either opened or created a workbook yet.

This line is not correct:
Set xlBook = xlApp.ActiveWorkbook

It would be more like:
Set xlBook = xlApp.Workbooks.Open(varGetFileName, 0, True)

or
Set xlBook = xlApp.Workbooks.Add

The first opens an Excel file and the second creates a new workbook

You also have some issues getting out. As you are doing it, you are going
to leave an instance of Excel running. If the user tries to open excel, it
will appear to hang up. You have quit the application if you used the
CreateObject but not if you used the GetObject. In that case, you might
loose changes a user had made to a file they had open.

You also need to save and close the workbook. Here is an example from one
on my apps:

If varGetFileName "" Then
xlBook.SaveAs Filename:=varGetFileName
End If

Me.txtStatus.Visible = False
Me.Repaint
xlBook.Close
If blnExcelWasNotRunning = True Then
xlApp.Quit
Else
xlApp.DisplayAlerts = True
xlApp.Interactive = True
xlApp.ScreenUpdating = True
End If
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing


--
Dave Hargis, Microsoft Access MVP


"icq_giggles" wrote:

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




ExitHe
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
--
Thank you,

Additional notes - I don't know how to get the pivottable to export and save
in excel, I know working with unsaved open workbook is probably the heart of
my problem, this is also why I haven't bothered closing Excel from here - the
user will need to do their manipulation and save the data.
--
Thank you,


"Klatuu" wrote:

You haven't either opened or created a workbook yet.

This line is not correct:
Set xlBook = xlApp.ActiveWorkbook

It would be more like:
Set xlBook = xlApp.Workbooks.Open(varGetFileName, 0, True)

or
Set xlBook = xlApp.Workbooks.Add

The first opens an Excel file and the second creates a new workbook

You also have some issues getting out. As you are doing it, you are going
to leave an instance of Excel running. If the user tries to open excel, it
will appear to hang up. You have quit the application if you used the
CreateObject but not if you used the GetObject. In that case, you might
loose changes a user had made to a file they had open.

You also need to save and close the workbook. Here is an example from one
on my apps:

If varGetFileName "" Then
xlBook.SaveAs Filename:=varGetFileName
End If

Me.txtStatus.Visible = False
Me.Repaint
xlBook.Close
If blnExcelWasNotRunning = True Then
xlApp.Quit
Else
xlApp.DisplayAlerts = True
xlApp.Interactive = True
xlApp.ScreenUpdating = True
End If
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing


--
Dave Hargis, Microsoft Access MVP


"icq_giggles" wrote:

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




ExitHe
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
--
Thank you,