ExportWithFormatting using Excel 2007?!

[accessmatt]

Hello,



I'm using the ExportWithFormatting Macro but, strangely, it only lists Excel 2007 and not 2010 as an option. Moreover, the formatting is messed up when I export this way. Any ideas why 2010 or CSV isn't an option? See attached screengrab.

[Dal Jeanis]

I'm assuming from your question that you do have Excel 2010 installed on your machine.

Check your references in Access to make sure that you have the Excel 2010 checked, and available to be used by Access. (I don't know why missing 2010 would keep you from outputting to a CSV, though, so this may not be it.)

[accessmatt]

Hello Dal, thanks for trying. Yes I do have 2010 both Access & Excel. Which setting do you mean by my references in Access? thanks,

[Dal Jeanis]

In Access, bring up the Visual Basic programming window (alt-shift-f11? or alt-f11?) and look under Tools -> References.

Make sure the "Microsoft Office Automation" for your version of Excel... V14, I believe... is checked.

[accessmatt]

Dal,

Thank you, but I don't see anything named "Microsoft Office Automation" in there. I tried selecting anything that looked close or was referring to Office v 14 but no luck.

[Dal Jeanis]

Hmmm. There is a grand section of items that start with Microsoft. It should be somewhere in there - here's the ones I have checked. These are copied from my Excel 2010, since I don't have Access installed on this particular machine.

"Microsoft Access 14.0 Object Library"
"Microsoft Excel 14.0 Object Library"
"Microsoft Office 14.0 Object Library"
"Visual Basic for Applications"
"OLE Automation"

Hopefully this helps.

[accessmatt]

Hi Dal, truly appreciate your showing me that but I don't have an option for Excel 14.0 OL or OLE Automation. I'm guess this probably now a question for my IT department right?

Thanks,

[Dal Jeanis]

Curious. Yes, I'd start there. Security may have locked something down regarding references and add-ins. Be sure to explain to them the actual root problem that you're trying to solve, the "export to CSV" problem, rather than just the latest question.

Oh, by the way, I missed the Excel comment in your initial post, and focused on the CSV part. Format-wise, Excel 2010 uses the Excel 2007 format, so that's the right export to XLSX. However, I don't see Excel 2007 in the selection list you posted.

What specific formatting is messed up with the export to XLSX?

[accessmatt]

Thanks Dal. Basically the data (sans the headers) in Excel are light grey font. I'd have to clear formats or change font to black. It's just a strange user experience.

[Dal Jeanis]

Okay, that's probably coming across from the format of the report that you are exporting.

If you don't want to change the report itself, then it's easy enough to code a button to use VBA to export with formatting, then open the exported file and update the font color to black. Your choice.