Basically I'm taking data from an Access 2000 database and dumping it in Excel. Although this concept works for Excel 2003, but not Excel 2007. I'm using Vb6 for this process.

Sample Code:

'The dialog box that asks the user where to save the document or object
With CommonDialog1
.InitDir = customer_root
.DefaultExt = ".xlsx"
.Filter = "Excel Workbook (*.xlsx)|*.xlsx"
.ShowSave
.CancelError = True 'If the user clicks cancel then the program exits
the sub
End With

ExcelName = "Excel 8.0;DATABASE=" 'I've tried setting this to 12.0 but no luck.

sql = "SELECT " & NewData & " INTO [" Excel 8.0;DATABASE=C:\Test.xlsx].[12_2_2009]] " & _
" FROM NewTable " & _
" WHERE (Line < 900) OR (Line >= 900 AND Action01 = 1) ORDER BY EndStep"

Debug.Print sql

objDB.Execute sql