Access 2013 accde file can't export to acForma

Question

I have an Access 2013 database and I saved it as an accde file to give to the end users. I noticed that the Report Print Preview no longer had the export buttons on the ribbons. I am not sure why they are there in the accdb but gone in the accde file and could not find anything using Google to explain this but I did find a lot of articles saying you had to make your own ribbon for the runtime version of Access to export to Excel. I am not using the runtime but I made my own ribbon and it worked, but only for the .xls file format. When I try to save it to .xlsx I get a error:

"Run-time error '2282': The format in which you are attempting to output the current object is not available."

I have found a few posts on the web that says this works but with no detail and a lot of posts about Access 2007 bug/removed feature regarding Excel exports not working but I am using 2013 so not relevant.

So the code is simple:

DoCmd.OutputTo acOutputReport, "MyReport", acFormatXLSX, "File Name"

I have also tried adding references. I currently have following references in this order of priority: Visual Basic For Applications Microsoft Access 15.0 Object Library OLE Automation Microsoft Office 15.0 Access database engine Object Library Microsoft Internet Controls Microsoft Office 15.0 Object Library Microsoft Excel 15.0 Object Library Microsoft Data Access Components Installed Version

The short question is, is it even possible to export to XSLX with Access 2013? And if it is, what am I doing wrong?

Thanks for reading all the way down here.

Answer 1

I write it to excel via VBA, rather than use the export function. See below for an example out of a current project.

Private Sub cmdExcel_Click()
DoCmd.Hourglass 1
   'Create a new workbook in Excel
   Dim i As Long
   Dim j As Integer
   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object
   Dim tbl As Object
   Dim rng As Object
   Set oExcel = CreateObject("Excel.Application")
   Set oBook = oExcel.Workbooks.Add
   Set oSheet = oBook.Worksheets(1)

    i = 1
    j = 1
   'Transfer the data to Excel
    Do While i <= frmStockScreen.Form.fGrid.rows
        Do While j <= frmStockScreen.Form.fGrid.cols
            oSheet.cells(i, j).Value = frmStockScreen.Form.fGrid.TextMatrix(i - 1, j - 1)
            j = j + 1
        Loop
        j = 1
        i = i + 1
    Loop

    Set rng = oSheet.Range(oSheet.cells(1, 1), oSheet.cells(frmStockScreen.Form.fGrid.rows, frmStockScreen.Form.fGrid.cols))
    Set tbl = oSheet.ListObjects.Add(1, rng, , 1)
    tbl.TableStyle = "TableStyleLight9"

   oSheet.Columns(1).ColumnWidth = 10
   oSheet.Columns(2).ColumnWidth = 25
   oSheet.Columns(12).ColumnWidth = 40

   'Save the Workbook and Quit Excel
   oBook.SaveAs "V:\Database\Temp\StockDump" & GetNewLogin() & ".xlsx"

   oExcel.Quit
   Application.FollowHyperlink "V:\Database\Temp\StockDump" & GetNewLogin() & ".xlsx"
DoCmd.Hourglass 0
End Sub

Answer 2

Exporting a report to Excel 2013 in not available. Apparently it was specifically not included due to poor user experience with exporting reports. I can imagine that labels spanning across many cells etc could get messy.

If you are exporting to Excel, could you export a query into Excel ? Or just export into PDF ?

You can also use a template excel file and then export data into a range.

From this link on support.office.com scroll down to Prepare for the export operation, look under section With Formatting.

There is a foot note 1. Choose the destination workbook and file format. Note that reports can only be exported in the older *.xls file format, not in the newer *.xlsx file format.