none
Export pivot table from access into excel & file path RRS feed

  • Question

  • Hi,

    I have the code to export to pivot table, but I cannot perform "File Save As" with my current code. I have tried multiple versions to save the open HTM doc as an .xlsx in a specific file path. Please assist as I have searched the web for answers and it seems the community is short on this type of question. I've also tried to export using transferspreadsheet but the .xlsx doesn't stay as a pivot table.

    My code stops at "ObjXLwb.SaveAs fileName:=(fpath), FileFormat:=56" with the error "Object doesn't support this property or method."

    My code is as follows within MS Access 2010.

    Function PTView()

    'open data sheet as read only tivot tables
    '    DoCmd.OpenQuery "qry_2_AC", acViewPivotTable, acEdit
    '    DoCmd.RunCommand acCmdPivotTableExportToExcel
    '    DoCmd.Save acForm, "qry_2_AC"
    '    DoCmd.TransferSpreadsheet acExport, 10, "qry_2_AC", acPath & myValue & "_AC Variance" & ".xlsx", True, "AC"
    '    DoCmd.Close acQuery, "qry_2_AC"
    '
    'Dim ObjXLa, ObjXLws, ObjXLwb
    Dim blnObjectCreated As Boolean
    Dim ObjXLa As Object 'Excel Application
    Dim ObjXLws As Object
    Dim ObjXLwb As Object
    Dim fpath As String

    fpath = "Z:\Reports_&_Audits\Variance\Saved Files\AC\Testing.xlsx"
    blnObjectCreated = False
    'Set ObjXLa = Nothing
    'Set ObjXLws = Nothing
    'Set ObjXLwb = Nothing
    'Set xl = Nothing
    If Err Then
    Err.Clear
    Set ObjXLa = CreateObject("Excel.Application")
    If Err Then
    MsgBox "Can't get Excel Application"
    Else
    blnObjectCreated = True
    End If
    End If
    'strFileName = ObjXLa.GetOpenFilename("Microsoft Excel bestanden (*.xlsx*),*.xlsx*")
    'ObjXLa.DisplayAlerts = False
    'Set ObjXLwb = ObjXLa.Workbooks
    'ObjXLwb.Open 'strFileName
    DoCmd.OpenQuery "qry_2_AC", acViewPivotTable, acReadOnly
    DoCmd.RunCommand acCmdPivotTableExportToExcel

    'On Error Resume Next
    'Do Until Not ObjXLa Is Nothing ' find open instance of XL
    'Set ObjXLa = GetObject(, "Excel.Application")
    'Loop ' might want an exit criteris
    'ObjXLwb.Open
    'ObjXLa.Visible = False
    'ObjXLa.Worksheets(1).Activate
    'Set ObjXLws = ObjXLa.Worksheets(1)
    With ActiveWorkbook
    ObjXLwb.SaveAs fileName:=(fpath), FileFormat:=56
    ObjXLwb.SaveAs fpath
    ObjXLwb.Close False
    If blnObjectCreated = True Then
    ObjXLwb.Quit
    End If
    Set ObjXLa = Nothing
    Set ObjXLwb = Nothing
    End With

    '    DoCmd.OpenQuery "qry_2_Baltimore", acViewPivotTable, acEdit
    '    DoCmd.OpenQuery "qry_2_Cincinnati", acViewPivotTable, acEdit
    '    DoCmd.OpenQuery "qry_2_Cleve-Thistledown", acViewPivotTable, acEdit
    '    DoCmd.OpenQuery "qry_2_Illinois_Region", acViewPivotTable, acEdit
    '    DoCmd.OpenQuery "qry_2_Iowa_Region", acViewPivotTable, acEdit
    '    DoCmd.OpenQuery "qry_2_NOLA", acViewPivotTable, acEdity
    '    DoCmd.OpenQuery "qry_2_Northern_Kansas_Region", acViewPivotTable, acEdit
    '    DoCmd.OpenQuery "qry_2_Northern_NV", acViewPivotTable, acEdit
    '    DoCmd.OpenQuery "qry_2_Tunica", acViewPivotTable, acEdit
    '    DoCmd.OpenQuery "qry_2_Vegas", acViewPivotTable, acEdit


        
    End Function

    Tuesday, January 10, 2017 7:00 PM

Answers

  • Hi Legzen34,

    I can also run this code without referencing HTML library.

    I try to add the HTML library to check whether it gives me any error.

    but then also code working correctly without error.

    can you tell me which error you got?

    I suggest you to create a new module to test the above code and let me know about the results.

    if it give you error then I will try to find a different solution for this issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Legzen34 Thursday, January 12, 2017 9:08 PM
    Thursday, January 12, 2017 7:03 AM
    Moderator

All replies

  • Hi Legzen34,

    you can refer the code mentioned below.

    Sub demo()
    Dim xlTmp As Excel.Application
     Set xlTmp = New Excel.Application
     xlTmp.Workbooks.Open "C:\Users\v-padee\Desktop\demo.html"
     xlTmp.Visible = True
     xlTmp.ActiveWorkbook.SaveAs FileName:="C:\Users\v-padee\Desktop\demo.xlsx", FileFormat:=xlWorkbookDefault
    
    End Sub

    change the file format according to your requirement to avoid the error and properly save the file without corruption.

    Reference:

    Workbook.SaveAs Method (Excel)

    XlFileFormat Enumeration (Excel)

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 11, 2017 1:40 AM
    Moderator
  • Deepak, thanks for your assistance. Unfortunately it debugs at:

    xlTmp.Workbooks.Open "C:\Users\jalcorn\Desktop\demo.html"

    I tried manipulating it with no success. I have the excel object and html object library selected.

    Wednesday, January 11, 2017 6:56 PM
  • Hi Legzen34,

    I can also run this code without referencing HTML library.

    I try to add the HTML library to check whether it gives me any error.

    but then also code working correctly without error.

    can you tell me which error you got?

    I suggest you to create a new module to test the above code and let me know about the results.

    if it give you error then I will try to find a different solution for this issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Legzen34 Thursday, January 12, 2017 9:08 PM
    Thursday, January 12, 2017 7:03 AM
    Moderator
  • Thank you are correct. I revamped my code. Started from the beginning using what you provided and it worked. Thank you so much for your expertise and assistance. 
    Thursday, January 12, 2017 9:08 PM