Transferspreadsheet and .xlsm (1 Viewer)

[MailMan]

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.

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

(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?

 

[Pat Hartman]

You need to provide a value for the second argument so Access knows what version you want to create.

 

[mdlueck]

Sub will export to .xls and .xlsx but not to .xlsm workbooks.

I seem to recall there not being a constant which will cause the DoCmd.TransferSpreadsheet function to produce an .xlsm file format.

AcSpreadSheetType Enumeration (Access)
http://msdn.microsoft.com/en-us/library/office/ff196017.aspx

Which this page does not include:
acSpreadsheetTypeExcel12Xml
which is a valid constant.

Somewhere I remember M$ having documented that as a valid constant. hhhmmm...

 

[MailMan]

I originally had a value for the second argument. I tried acSpreadsheetTypeExcel12Xml and 10 as arguments before taking them out altogether.

I have tried changing Transferspreadsheet to OutputTo but it does not give .xlsm as an available format.

Is there a way to transfer a query straight into an existing .xlsm workbook?

 

[mdlueck]

Is there a way to transfer a query straight into an existing .xlsm workbook?

Like I said...

I seem to recall there not being a constant which will cause the DoCmd.TransferSpreadsheet function to produce an .xlsm file format.

 

[thechazm]

Instead of using transferspreadsheet you can write up a function to read you queries dynamically and output them to xlsm's or any other format you want them in.

 

[thechazm]

Instead of using transferspreadsheet you can write up a function to read you queries dynamically and output them to xlsm's or any other format you want them in.

 

[thechazm]

Instead of using transferspreadsheet you can write up a function to read you queries dynamically and output them to xlsm's or any other format you want them in.