none
Microsoft Access Pivot Form - Export to Excel 2010 - VBA RRS feed

  • Question

  • How do I export a Microsoft Access Pivot Form to Excel with a specified Network Location using VBA?

    Something like??

    Function GlobalOngoingByDay()

    DoCmd.OpenForm "frm_GlobalOngoingByDay_Pivot1", acFormPivotTable, , , acFormReadOnly
    DoCmd.RunCommand acCmdPivotTableExportToExcel

    DoCmd.Close acForm, "frm_GlobalOngoingByDay_Pivot1"

    End Function

    But I need to specify the location to save the export.  Thx!!

    Thursday, January 12, 2017 5:51 PM

All replies

  • Hi sdilley,

    Based on DoCmd.RunCommand, we could not specify the location to save the export. For a workaround, I would suggest you try External Data->Export->Excel, and save the Export, and then run the saved export by below code:

    DoCmd.RunSavedImportExport "Export-tblUser" ‘Export-tblUser is the name of export operation

    If you want to change the path of export, you could try below code.

    With CurrentProject.ImportExportSpecifications("Export-tblUser")
        Debug.Print .XML
        '.XML = Replace(.XML, varSavedPathName, varNewPathName) ‘you could use this new path to replace the saved path
        .XML = Replace(.XML, "tblUser.xlsx", "tblUser1.xlsx") ‘for this, I made a test to replace the file name
        Debug.Print .XML
    End With

    Best Regards,

    Edward


    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.

    Friday, January 13, 2017 5:46 AM
  • Hi Edward,

    Worked wonderfully!!!  Thanks a bunch!!!


    sdilley

    Friday, January 20, 2017 12:14 PM
  • Hi Sdilley,

    I am glad it works for you, and I would suggest you mark my solution as answer to close this thread, and then other who run into the same issue would find the solution easily.

    Best Regards,

    Edward


    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.

    Monday, January 23, 2017 7:36 AM