Software

Access 2007 doesn't export to Excel


You cannot export a report to an Excel format in Access 2007

This might be a bit of a gotcha if you upgrade a database that exports Access reports to Excel.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

14 comments
rgerald
rgerald

Even with SP2 installed, the export to Excell seems to lose information from group headers and footers. It also outputs the data in a funky format.

Jay.Ryan
Jay.Ryan

I have been notified by a MS employee that I work with that they were involved in a legal issue and that is the reason it was left out of 2007. However, apparently it has been cleared up and this ability will be added back in with SP2 for Office 2007. Just thought you would like to know.

erins.ghost
erins.ghost

There's a product called Prism (the company is called SiSense). It's somewhat of a hybrid between Access and Excel. It lets you create live/standalone reports, dashboards and business applications kind of like Access - only without any scripting, programming or SQLing...

peter_j_hellewell
peter_j_hellewell

I'm having problems using the Docmd.Transferspreadsheet command to sen data from Access 2003 or 2007 to Excel 2007. I can do it one time, but the second time it fails because of the error "Cannot expand named range".....The command is supposed to override the data in Excel and readjust the associated range of the named range...but it doesn't....Is anybody else having this problem?

BJMaMo
BJMaMo

I said 'WHAT???!!!' This does seem like a major step backwards and I routinely export information from Access to Excel - this does not bode well for me.

re
re

Seems like a major deficiency.... Has this been reported elsewhere... has MS commented or suggested a fix? Interestingly... there was a MS ad touting a positive review by one of the major mags right next to your post Ray

cdockery
cdockery

I have 2007 service pack 2 and i do not have that functionality available. Is there a reg setting or something that needs to be tweaked to make this happen? Thank you

jpjones23
jpjones23

Here's the code I put together. It "opens" the database (it's nothing more that the full path of the database that's already open), creates an excel object, and executes a query that spins the results out to Excel. The resultant data in the spreadsheet needs to be re-formatted but a macro can easily do that. I include a nonsense set of 3 characters ([-]) - no parentheses)) in those fields that are empty. A replace all makes than all empty but doing this ensures positional accuracy. Here's the code. ' Open DB, execute query, call subroutine to write out query records On Error GoTo HandleErrors Dim wrkJet As Workspace Dim dbsChangeRequest As Database Dim rstTemp As Recordset Dim xl As Excel.Application Dim intI As Integer Dim intJ As Integer Dim intK As Integer ' Open Microsoft Jet and ODBCDirect workspaces, Microsoft ' Jet database, and ODBCDirect connection. Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet) Set dbsChangeRequest = wrkJet.OpenDatabase("D:\Database\GAD_QMS_CR_ProjectDatabase_Access2000.mdb") ' Debug.Print "Opening forward-only-type recordset " & _ ' "where the source is a QueryDef object..." Set rstTemp = dbsChangeRequest.OpenRecordset( _ "All_Open_CRs_Export_Query", dbOpenForwardOnly) intK = 1 intJ = 1 Set xl = CreateObject("Excel.application") xl.Workbooks.Add xl.Visible = True Do While Not rstTemp.EOF ' Cells(rowIndex, ColumnIndex) For intI = 0 To rstTemp.Fields.Count - 1 If Len(rstTemp.Fields(intI)) > 0 Then xl.ActiveSheet.Cells(intK, intJ) = rstTemp.Fields(intI) & " " Else xl.ActiveSheet.Cells(intK, intJ) = rstTemp.Fields(intI) & "[-]" End If intJ = intJ + 1 Next intI skipLoop: rstTemp.MoveNext intK = intK + 1 intJ = 1 Loop ExitHere: Set rst = Nothing Set xl = Nothing rstTemp.Close dbsChangeRequest.Close wrkJet.Close Exit Sub HandleErrors: Stop If (Err.Number = 2001) Then ' DoCmd.OpenForm "Save Error - Operation Cancelled" ElseIf (Err.Number = 1004) Then Resume Next Else MsgBox Err & " " & Error$ ' DoCmd.OpenForm "Save Error - On Close" End If ' GoTo skipLoop Resume 'Next

home4ktt
home4ktt

I believe you get the error message "cannot expand named range" if the destination workbook/sheet already exists and the existing range has to be expanded to allow for the extra data being pasted. If the destination workbook/sheet already exists, try deleting (or renaming) it and then export from Access.

ssharkins
ssharkins

Why don't you post your code so we can take a look at it and maybe test it?

ssharkins
ssharkins

I can't offer any word of explanation -- I know as much as MS offered in the article I linked to. :( I'll try to keep up with this and post more as I learn more -- if there's anymore to learn.

heifler
heifler

To easily export data from Access 2007 or earlier versions to Excel see www.AccessToExcel.com This tool will automate this for you.

markroach25
markroach25

I intend to setup a macro function to copy statistics from several webpages into a excel spreadsheet. After calculating and sorting these statistics, I need to be able to identify which webpage the individual data came from, or even better, be able to link each statistic with a contact in Microsoft contacts. Any help appreciated!!

Editor's Picks