DoCmd.TransferSpreadsheet导出:让用户选择保存目标? [英] DoCmd.TransferSpreadsheet Export: Let user choose save destination?

问题描述

我成功运行TransferSpreadsheet将Access查询导出到指定位置的Excel文件:

I successfully run TransferSpreadsheet to export an Access query to an Excel file at a specified location:

Private Sub cmdExportQuery_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "trndOTQry", _ 
"\\company.com\root\share\public\D2S\D2S\D2S_Scorecard\Trend OT.xls"
End Sub

鉴于这是一个共享文件，我希望打开一个对话框，该对话框允许用户选择他选择的任何保存目标.我发现此链接并提供了引用本文.当我将此代码插入我的窗口时:

Given that this is a shared file, I want a dialog to open that allows the user to select whatever save destination he chooses. I found this link with a good solution that references this article. When I plug this code into my window:

Private Sub cmdExportQuery_Click()
Dim strSaveAsFilter As String
Dim strSaveAsFileName As String
strFilter = ahtAddFilterItem(strSaveAsFilter, "Excel Files (*.xls)", "*.xls")
strSaveAsFileName = ahtCommonFileOpenSave( _
OpenFile:=False, _
Filter:=strSaveAsFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "trndOTQry", _
strSaveAsFileName, , "-1", True
End Sub

我收到错误编译错误:未定义Sub或Function ，它以蓝色突出显示 ahtAddFilterItem ，并突出显示 Private Sub cmdExportQuery_Click()带有黄色箭头的黄色.我曾尝试制作一个Public Sub，但是当我单击按钮时什么也没有发生.将查询导出到Excel时，我该怎么做才能允许用户选择保存目标?

I get the error Compile Error: Sub or Function not defined, it highlights ahtAddFilterItem in blue, and highlights Private Sub cmdExportQuery_Click() in yellow with a yellow arrow. I tried making a Public Sub, but nothing happened when I clicked the button. What must I do to allow the user to choose the save destination when exporting this query to Excel?

推荐答案

还应该包含其他代码.检查这里:

There is additional code that should have also been included. Check here:

http://access.mvps.org/access/api/api0001.htm

我也发现了这一点，这对于其他有相同错误的人可能会有所帮助:

I also found this which may be helpful from someone else who had the same error:

您还获得了注释中提到的其他代码

"Did you also get the additional code mentioned in the NOTE

注意:此代码仍然需要Ken提供的GetOpenFileName例程.盖茨确保您复制了本文中的功能.

Note: This code still requires the GetOpenFileName routine provided by Ken Getz. Make sure you copy the function from this article.

将您发送到 http://www.mvps.org/access/api/api0001.htm "

这篇关于DoCmd.TransferSpreadsheet导出:让用户选择保存目标?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！