we have several processes that export from access to macro eabled excel spreadsheets. But we are no longaer able to do this in Access 2007.
Angelo => Chief Rocket Scientist
I need more details for the error to decide what's the reason. Would you post it here? What do you mean by no longer to do this in Access 2007?Wednesday, July 28, 2010 12:35 AM
I also have the same issue.
In Access 2003, we exported data into an Excel 2003 file that contained VB code that reset the autofilter upon exit/save. Excel 2003 views this as a macro as does Excel 2007.
The export code is as follows in Access 2003:
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 "V:\Procurement\Reports\All Suppliers.xls"
oXLApp.Visible = True
The "macro" code in Excel 2003 is as follows:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Expand (turn off) all filters on the active worksheet
Dim Fltr As Filter
Dim iFiltr As Integer
For iFiltr = 1 To .Filters.Count
If .Filters(iFiltr).On Then
Unfortunately, it doesn't appear that Access 2007 will allow you to export to an Excel 2007 .xlsm (macro enabled) file format.
Is there some sort of work around that will allow us to export directly to a macro enabled Excel 2007 file or do we need to keep the code in Access 2003 format in our converted Access 2007 database to export to the Excel 2003 file format and then not convert the spreadsheets to up to Excel 2007? The Excel file is overwritten on a monthly basis but we want to keep the format and the filters.Thursday, August 05, 2010 9:29 PM