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屋!