Microsoft Access数据透视表 - 导出到Excel 2010 - VBA [英] Microsoft Access Pivot Form - Export to Excel 2010 - VBA

问题描述

如何使用VBA将Microsoft Access数据透视表格导出到具有指定网络位置的Excel？

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

类似于？

函数GlobalOngoingByDay（）

Function GlobalOngoingByDay()

DoCmd.OpenForm" frm_GlobalOngoingByDay_Pivot1"，acFormPivotTable ,,, acFormReadOnly

DoCmd.RunCommand acCmdPivotTableExportToExcel

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

DoCmd.Close acForm，"frm_GlobalOngoingByDay_Pivot1"

DoCmd.Close acForm, "frm_GlobalOngoingByDay_Pivot1"

结束函数

但我需要指定保存导出的位置。  Thx !!

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

推荐答案

嗨sdilley，

Hi sdilley,

基于在
DoCmd.RunCommand 上，我们无法指定保存位置出口。要解决此问题，我建议您尝试使用外部数据 - >导出 - > Excel，然后保存导出，然后通过以下代码运行保存的导出：

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

这篇关于Microsoft Access数据透视表 - 导出到Excel 2010 - VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！