locked
Access 2007 exporting to Excel .xlsm format

    Question

  • 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
    Friday, October 16, 2009 9:37 PM

All replies

  • 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
    With ActiveSheet.AutoFilter
    For iFiltr = 1 To .Filters.Count
    If .Filters(iFiltr).On Then
    .Range.AutoFilter field:=iFiltr
    End If
    Next iFiltr
    End With
    End Sub

    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