I as well have the similar problem. In Access 2003, I exported data into an Excel 2003 file which contained VB code which rearrange the auto filter upon exit or save. Excel 2003 sights this as a macro as does Excel 2007. The export code is as below in Access 2003:
Code:
Public Function ExportSuppliersAllExcel()
Dim oXLApp As Object
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "QrySuppliersALL", "V:\Procurement\Reports\All Suppliers.xls", True
Set oXLApp = CreateObject("Excel.Application")
oXLApp.workbooks.Open "D:\Procurement\Reports\All Suppliers.xls"
oXLApp.Visible = True
Unluckily, it does not come into view that Access 2007 will permit you to export to an Excel 2007 .xlsm macro enabled file format.
Bookmarks