Creating an Excel file in vba but saving in a .xls format

[Randy]

We currently have a process that creates an excel spreadsheet in the
following manner:
If (intFileType = 3) Then 'Excel File

Dim oXLApp As Excel.Application
Dim oXLWBook As Excel.Workbook
Dim oXLWSheet As Excel.Worksheet

Set oXLApp = New Excel.Application

Set oXLWBook = oXLApp.Workbooks.Add

Set oXLWSheet = oXLWBook.Worksheets.Add

End If

'does stuff here to fill up the spreadsheet

If (intFileType = 3) Then 'Excel File

oXLWSheet.SaveAs strFileName '
oXLApp.Quit

End If

The machines that are running this process presently have both office xp &
office 2007 installed and when the 2002 version of access runs the process it
is creating the excel file with a .xlsx extension. The references in access
2002 are using the Excel 10.0 object library. I have 2 questions.

1): Can I force the Access 2002 version to create the file as an .xls and
2): In access 2007 how do I `save down` to the excell 97-2003 format
(.xls). Most of the people we send these to are using older versions of
office and will not be able to open the 2007 version of excel.

Any help would be greatly appreciated.

 

[Stefan Hoffmann]

hi Randy,

oXLWSheet.SaveAs strFileName '
oXLApp.Quit

Take a look at the OH, search for [FileFormat]:

Sub SaveAs(Filename As String, [FileFormat], [Password],
[WriteResPassword], [ReadOnlyRecommended], [CreateBackup], [AddToMru],
[TextCodepage], [TextVisualLayout], [Local])

The machines that are running this process presently have both office xp &
office 2007 installed and when the 2002 version of access runs the process it
is creating the excel file with a .xlsx extension. The references in access
2002 are using the Excel 10.0 object library.

Don't use early binding, then it will work.


mfG
--> stefan <--

 

[Randy]

Thank you for your reply Stefan, unfortunately I am getting an error when I
try to use the fileformat enumeration. The error that generates is runtime
error '1004' application-defined or object-defined error. I don't receive
this error when I don't use the file format enumeration (xlExcel9795) whicj I
found here http://msdn2.microsoft.com/en-us/library/bb241279.aspx. I also
change my code to be late binding I think.:

If (intFileType = 3) Then 'Excel File
Dim oXLApp As Object
Dim oXLWBook As Object
Dim oXLWSheet As Object

Set oXLApp = CreateObject("Excel.Application") 'I also tried
Excel.Application.10
Set oXLWBook = oXLApp.Workbooks.Add
Set oXLWSheet = oXLWBook.Worksheets.Add

End If


oXLWSheet.SaveAs strFileName, xlExcel9795
oXLApp.Quit

Not really sure if that was the correct way to do late binding

Thanks

hi Randy,

oXLWSheet.SaveAs strFileName '
oXLApp.Quit

Take a look at the OH, search for [FileFormat]:

Sub SaveAs(Filename As String, [FileFormat], [Password],
[WriteResPassword], [ReadOnlyRecommended], [CreateBackup], [AddToMru],
[TextCodepage], [TextVisualLayout], [Local])

The machines that are running this process presently have both office xp &
office 2007 installed and when the 2002 version of access runs the process it
is creating the excel file with a .xlsx extension. The references in access
2002 are using the Excel 10.0 object library.

Don't use early binding, then it will work.


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Randy,

Not really sure if that was the correct way to do late binding

http://www.granite.ab.ca/access/latebinding.htm



mfG
--> stefan <--