excel - MS Access：将多个查询导出到单独的 Excel 文件中

Question

我需要将一些查询从 Access 导出到 Excel。（11 个表格到 11 个无格式 Excel 文件）

我正在使用的数据库有一个前端“用户”数据库，以及一个包含大量数据的后端数据库。

我能够成功使用以下代码：

Private Sub Command6_Click()
'Create Genie Files for Temp Deskop DB and Export Files to Excel

UID = Environ("Username")
excelsavepath = "C:\Documents and Settings\" & UID & "\Desktop\Monthly Audits - Desktop\"
    
DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "CIDCASTDATA", excelsavepath & "CIDCASTDATA.xlsx, True"

MsgBox "All CIDCASTDATA havs been exported to the Monthy Audits-Desktop."
DoCmd.SetWarnings True

End Sub

但是，当我尝试将多个放在一起时，出现错误：

运行时错误 3027：无法更新。数据库或对象是只读的。

Private Sub Command7_Click()
'Create Genie Files for Temp Desktop DB and Export Files to Excel

UID = Environ("Username")
excelsavepath = "C:\Documents and Settings\" & UID & "\Desktop\Monthly Audits - Desktop\"
    
DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "CIDCASTDATA", excelsavepath & "CIDCASTDATA.xlsx, True"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "CIDORGUNITS", excelsavepath & "CIDORGUNITS.xlsx, True"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Cost Center List", excelsavepath & "Cost Center List.xlsx, True"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "EmployeeChief", excelsavepath & "EmployeeChief.xlsx, True"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Job Catalog-DLP", excelsavepath & "Job Catalog-DLP.xlsx, True"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Org Unit Pull from SAP", excelsavepath & "Org Unit Pull from SAP.xlsx, True"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "PA List", excelsavepath & "PA List.xlsx, True"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "SAP Position Pull-ALL WDPR", excelsavepath & "SAP Position Pull-ALL WDPR.xlsx, True"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "SAP Position Pull-ALL WDPR_1", excelsavepath & "SAP Position Pull-ALL WDPR_1.xlsx, True"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "SAP Position Pull-ALL WDPR_2", excelsavepath & "SAP Position Pull-ALL WDPR_2.xlsx, True"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "SAP Position Pull-ALL WDPR_3", excelsavepath & "SAP Position Pull-ALL WDPR_3.xlsx, True"

MsgBox "All Genie Files have been exported to the Monthy Audits ."
DoCmd.SetWarnings True

End Sub

无论我是在前端数据库还是后端数据库中创建查询，都会发生此错误。但是，当我逐个文件手动导出到 Excel 时，这两个数据库都没有问题。

相反，我尝试在 Access 中使用宏生成器，但不知道如何/是否可以使用它来导出未格式化的 Excel 文件。

标签： excelvbaexport-to-excelms-access-2016