title: DoCmd.TransferSpreadsheet Export: Let user choose save destination?
body: <p>I successfully run TransferSpreadsheet to export an Access query to an Excel file at a specified location:</p>
  
  <pre><code>Private Sub cmdExportQuery_Click()
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "trndOTQry", _ 
  "\\company.com\root\share\public\D2S\D2S\D2S_Scorecard\Trend OT.xls"
  End Sub
  </code></pre>
  
  <p>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 <a href="http://www.pcreview.co.uk/forums/transferspreadsheet-questions-t1905614.html" rel="nofollow">this link</a> with a good solution that references <a href="http://access.mvps.org/access/api/api0001.htm" rel="nofollow">this article</a>.  When I plug this code into my window:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I get the error <code>Compile Error: Sub or Function not defined</code>, it highlights <code>ahtAddFilterItem</code> in blue, and highlights <code>Private Sub cmdExportQuery_Click()</code> 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?</p>