I have a database I converted from Access 2003 to Access 2010. In this Db I have a Sub that uses Transferspreadsheet for exporting a query into an existing Excel workbook. Recently I have been trying to use Sub to export into an .xlsm workbook. I changed code so .xls(default) and any other extrension (Optional extensionName) could be used. Sub will export to .xls and .xlsx but not to .xlsm workbooks.
(I have removed code not dealing with this problem)
When I run with no pre-existing workbook it will create for .xls and .xlxs extensions but throws an error message for .xlsm: "Cannot update. Database or object is read-only."
When run for .xlsm with a pre-existing workbook no error is given but workbook is not updated. Any ideas?
Code:
Sub ExcelReportExport(outName As String, qryName As String, Optional pName As Variant, _
Optional sName As Variant, Optional noPivotFlag As Boolean, Optional fPath As String, _
Optional extensionName As String)
On Error GoTo ExcelReportExport_Err
Dim reportName As String
If extensionName = "" Then reportName = outName & ".xls" Else reportName = outName & extensionName
'transfer data to excel workbook
DoCmd.TransferSpreadsheet acExport, , qryName, fPath & reportName
ExcelReportExport_Exit:
Exit Sub
ExcelReportExport_Err:
MsgBox Error$
Resume ExcelReportExport_Exit
End Sub
When I run with no pre-existing workbook it will create for .xls and .xlxs extensions but throws an error message for .xlsm: "Cannot update. Database or object is read-only."
When run for .xlsm with a pre-existing workbook no error is given but workbook is not updated. Any ideas?