I want to be able to export a query to an Excel Spreadsheet from a form where the user can define the name of the spreadsheet and also to place the spreadsheet in a defined folder. The button is called cmbCreateTable. The report name and the export path are typed into the relevant text boxes on the form frm_AddDataBetweenDates
This is the code I'm using:
Code:
Private Sub cmbCreateTable_Click()
'get the report name from the form
Dim ReportName As String
ReportName = [Forms]![frm_AddDataBetweenDates]![txtSpreadsheetName]
'get the path where the spreadsheet will be outputted to
Dim ExportPath As String
ExportPath = [Forms]![frm_AddDataBetweenDates]![txt_SpreadsheetExportLocation]
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"qry_AllValues_BetweenDates_MN", ExportPath, True , ReportName
MsgBox "Data Exported to Excel", vbOKOnly
End Sub
Now, this has failed miserably so far. Firstly, with this code, I get the VBA error 3051, it says it " can't open the file C:\Documents and Settings\Mark\Desktop. It is already opened exclusively by another user, or you need permission to view its data". Now, if I type "Test Spreadsheet" into the box marked "txt_SpreadsheetExportLocation" it will output a spreadsheet called "Test Spreadsheet" to my documents folder. I'm pretty sure the problem lies in my usage of the DoCmd.TransferSpreadsheet and I've looked at lots of other examples and can't fathom where I'm going wrong. Any ideas?
Using transferSpreadsheet acExport problems
