VBA Export from Access 2007 to Excel 2003

[sbrent]

I have been trying to google this all day and I know it's got to be something simple... :banghead:

I am using Office 2007 but many of my users are still using 2003. I need to export from Access using VBA to Excel and specify the verion of Excel (and no, apparently they can't download the plugin that would allow them to read the files).

After adding the data to multiple worksheets I am using "xlb.SaveAs Filename" to save the file.

Does anyone know if there is a parameter that would allow me to specify the export version?


Other issues that are not critical but would be nice to solve:

- xl.worksheet.add places the new worksheet before the active sheet. Any way to make it add it AFTER the active sheet?

- Can I use VBA to format the columns to expand to fit the data? I am using the following code to export column headers and data:

'export field names as column headings
For intCounter = 0 To Sample.Fields.Count - 1
xl.activesheet.cells.Item(1, intCounter + 1) = Sample.Fields(intCounter).Name
Next intCounter

'copy contents of table to excel spreadsheet
xl.activesheet.range("A2").select
xl.activesheet.range("A2").CopyFromRecordset Sample


Thanks in advance for your time.
Shannan

[SoftwareMatt]

If you are using TransferSpreadsheet then you add the version in the spreadsheet type as follows:
docmd.TransferSpreadsheet ,acSpreadsheetTypeExcel8...

For save as use file format as follows:
ActiveWorkbook.SaveAs fileFormat:=xlExcel7

Check this link: http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.xlfileformat.aspx

To set the columns to expand use this:
xl.Columns("A:L").AutoFit
xl.Columns("A:L").WrapText = True