Pivot table export to Excel

[KirRoyale]

I am trying to export an Access pivot table (a pivot table view from a query) to Excel. Whenever I did this manually, the version that appeared in Excel was just the table, not the pivot table.
So, I used the following VBA code:
Private Sub ExportPivot_Click()
DoCmd.OpenQuery "FileName ", acViewPivotTable, acEdit
DoCmd.RunCommand acCmdPivotTableExportToExcel
DoCmd.Close acQuery, "FileName "
End Sub
This works ok but I would like the file to be saved to the current directory in Excel using additional code similar to:
Dim stDocName As String
stDocName = CurrentProject.Path & "\" & "FileName"
but I have not been able to incorporate that successfully.
Could someone please advise how I can include the code to save the file and open it in Excel?

 

[Rx_]

Just a guess on my part. Check the Pivot Table Export code. It is not something I use.
My guess is that you have an async process of two Excel objects. So using the Get Object might pull in the orginal instance so its object can be automated.
Please post your results, it will be interesting.

Sub ExportPivotTableFindXLInstance()
      ' Dim Rx as Guessing
      Dim ObjXL As Excel.Application
      Dim ObjXLWB As Excel.Workbook
10    DoCmd.OpenTable "MyTable", acViewPivotTable, acReadOnly
20    DoCmd.RunCommand acCmdPivotTableExportToExcel
30    On Error Resume Next
40    Do Until Not ObjXL Is Nothing ' find open instance of XL
50        Set ObjXL = GetObject(, "Excel.Application")
60    Loop ' might want an exit criteris
            ' unless you have an i7 Pentium 3rd Generation - hear they run an infinate loop in 30 seconds
 
70    Set ObjXLWB = ObjXL.Workbooks(1)
80    ObjXLWB.SaveAs "F:\XLSavedAs.HTM", Excel.XlFileFormat.xlHtml ' save as HTM or your favorite type
90    ObjXLWB.Close
100   ObjXL.Quit
110   Set ObjXL = Nothing
120   Set ObjXLWB = Nothing
End Sub

 

[KirRoyale]

RX,
Thank you for the reply. I’m sorry but I’m at a very low level and have a couple of basic questions:
1. I was planning to have a button on a form to start the code. How should I start the code that you supplied? I get errors if I try to link this code to a form.
2. I wanted to save as an Excel 2010 (XLSX) file type. For the code in line 80, should I put the whole path and file name (or the CurrentProject.Path & "\" & "FileName) in the double quotation marks "F:\XLSavedAs.HTM".
Thanks and regards,