I have a project I work on frequently, the data comes in Access & I need to export to Excel. The following code always worked until my company upgraded to Windows 2010 a couple of years ago. What happens is I'll point to the subdir I want (e.g. P:\project\evaluation\output) and it will save one subdir up (e.g. P:\project\evaluation).
The code:
Sub ExporttoXL()
Dim response, today
exportdir = fncOpenFolder()
today = Format(Date, "mmddyy")
response = InputBox("What is the date for the title of the output file? (Recommend: mmddyy format)", "Output file date for name", today)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"Query001", "Output-" & response & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"Query002", "Output-" & response & ".xls"
End Sub
----------------
Public Function fncOpenFolder() As String
Dim fdlg As Object
Set fdlg = Application.FileDialog(4) 'msoFileDialogFolderPicker
With fdlg
.AllowMultiSelect = False
.Title = "Select Folder"
If .Show = -1 Then
fncOpenFolder = .SelectedItems(1)
Else
fncOpenFolder = ""
End If
End With
Set fdlg = Nothing
End Function
The FileName argument to TransferSpreadsheet is supposed to be "the file name and path of the spreadsheet you want to import from, export to, or link to." But your code is giving it only the file name without the path. The exportdir variable is not used after you give it a value from fncOpenFolder().
Revise the code and use exportdir to include the path with the file name for the workbook which you want as the export target ...
Dim strFullPath As String
strFullPath = exportdir & "\Output-" & response & ".xls"
Debug.Print strFullPath '<- view this in Immediate window; Ctrl+g will take you there
'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
' "Query001", "Output-" & response & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"Query001", strFullPath