Hi Scuba,
I'm a bit confused by your reference to the third line, since you've only
shown one line of VBA code. The space underscore ( _) line continuation
characters mean that this is all one line of code. A couple of suggestions:
1.) Declare string variables and assign the appropriate values. Use
Debug.Print statements to verify that the results look reasonable. For
example:
Dim strFileName As String
Dim strSavePath As String
Dim strFullPath As String
strFileName = [Forms]![menu]![Reports menu].Forms![SavePath]
strSavePath = "S:\ASSET DATA SERVICES\CUSTOMER SATISFACTION TEAM\" _
& "Queries Database 2007\New Database\"
strFullPath = strSavePath & strFileName & "\.xls"
Debug.Print "FileName: " & strFileName
Debug.Print "Path: " & strSavePath
Debug.Print "FullPath: " & strFullPath
Using a test spreadsheet saved in the intended path, with a pre-determined
name, you should be able to copy the FullPath from the Immediate Window,
paste it into the search box for finding Files or Folders, and locate this
file successfully.
2.) Use your new variable in the DoCmd.TransferSpreadsheet line:
DoCmd.TransferSpreadsheet TransferType:=acExport, _
TableName:="Report_Errors", FileName:=strFullPath, _
HasFieldNames:=True
Note: The "Errors" at the end of your line of code seems to correspond to
the optional UseOA argument, which I'm not sure has any real use.
3.) Verify that you have included the two very important words "Option
Explicit" as the second line of code at the top of your module:
Option Compare Database
Option Explicit
For more information on this, please see the following link:
Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions
4.) Verify that your code compiles without any errors. With any code module
open, click on:
Debug > Compile ProjectName
where ProjectName is the name of your VBA project.
5.) I recommend substituting the Uniform Naming Convention (UNC) path, in
place of the hard-coded drive letter "S:". That way, your code will not be
dependent on each user having the S drive correctly mapped.
Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
:
I have posted twice in the "import/export" forum and Mr Steele has given me a
bit of help but I still get a "438" error on the third line
DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, "Report_Errors", _
"""S:\ASSET DATA SERVICES\CUSTOMER SATISFACTION TEAM\Queries Database
2007\New Database\" & [Forms]![menu]![Reports menu].Forms![SavePath] &
".xls""", , , "Errors"
I want to let the user specify the file name before exporting.
Cheers
