TransferSpreadsheet

[Guest]

Can someone tell me how to code the transferspreadsheet cmd so that the user
enters the path & file name - it needs to be a variable.

 

[Brendan Reynolds]

Here's a quick-and-dirty solution using InputBox. (It supplies a default
path/name, but it's just a default, the user can change it). For a more
sophisticated, robust, and user-friendly solution, you could replace the use
of InputBox with the Windows Open File dialog - see the code at the URL
below ...

Public Sub TestSub4()

Dim strPathName As String
strPathName = InputBox("Where do you want to save the file?", _
"Save Where?", CurrentProject.Path & "\Test.xls")
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblTest",
strPathName

End Sub

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