I have an Access 2007 database and I am trying to export a query using the transferspreadsheet command to a folder on my desktop, Excel 2007. I am able to export if I use a file extension of xlsx but if I try xlsm I receive "runtime error 3027, cannot update database or object is read only". I have all the permissions to the folder.
I have changed all of the Excel macro security settings that I could find and am at a loss. The reason I need a macro enabled workbook is because it contains 3 worksheets, the exported data from Access, a pivot table and a pivot chart. I was looking for a way to refresh the pivot chart when new data was added so I added some code to the pivot chart worksheet but it told me I had to save the workbook as a macro enabled workbook so that is why I tried to export the data in the transferspreadsheet command as xlsm.

I hope I've made myself somewhat clear and any help is greatly appreciated.