Macro to export query to Excel

[HeatherD25]

Hi,

I have a database that needs to work in Access 2003 and 2007. I needed to
export a report, but because of the limitation of 2007 to export a report I
set up a macro to export the query to excel to just give the data for the
report.

I created the macro in 2007 and it won't work in 2003. I think because the
"Output Format" of the macro has different selections in the two version.
2003 has "Microsoft Excel 97-2003 (*.xls)" and 2007 has "Excel 97 - Excel
2003 Workbook (*.xls)". There isn't a common selection on either of them so
they would be the same regardless of the version I'm using.

Any ideas what I can do?

Thanks!!
Heather

 

[pietlinden]

Hi,

I have a database that needs to work in Access 2003 and 2007.  I neededto
export a report, but because of the limitation of 2007 to export a reportI
set up a macro to export the query to excel to just give the data for the
report.

I created the macro in 2007 and it won't work in 2003.  I think becausethe
"Output Format" of the macro has different selections in the two version. 
2003 has "Microsoft Excel 97-2003 (*.xls)" and 2007 has "Excel 97 - Excel
2003 Workbook (*.xls)".  There isn't a common selection on either of them so
they would be the same regardless of the version I'm using.

Any ideas what I can do?

Thanks!!
Heather

use TransferSpreadsheet? Use the Automation code at Access Web?
www.mvps.org/access
look up "Excel" and you'll find it. But as far as having code work in
2003 and 2007 without any modification, I doubt it. So check for the
version of Access and then use conditional compilation... Gee, that
sounds FUN!

 

[HeatherD25]

How do I do a conditional statement using the version?

I created 2 macros -- one that works for 2003 and one that works for 2007.

I tried this and it didn't work:

Dim DBversion as Integer

DBversion = CurrentDb.Version

If DBversion > 12 Then
DoCmd.RunMacro MacroExport2007
Else
DoCmd.RunMacro MacroExport2003
End If

Help??????????