We help IT Professionals succeed at work.

Working with CreateOleObject('Excel.Application') - I need some help.

hidrau
hidrau asked
on
Hello guys,

is there any way to see what excel version is installed ?

having excel 2007, is there any way to save the file in excel 2003?

thanks
Comment
Watch Question

Top Expert 2011
Commented:
Debug.Print Application.Version

Open in new window


Gives you the version - 12.0 is Excel 2007, 11.0 is 2003, etc.

You can use Workbook.SaveAs to save in the older format - there is a FileFormat parameter:
ActiveWorkbook.SaveAs "fgfgf.xls", XlFileFormat.xlExcel8

Open in new window

Author

Commented:
I tried this

        Excel.WorkBooks[1].SaveAs(arq, XlFileFormat.xlExcel8);

but delphi is complaining that XlFileFormat is undeclared identifier
Top Expert 2011

Commented:
Yes it would be unless you have a reference to the type libraries. It equates to numeric 56 - which should work for you.

Author

Commented:
ok,

for a version excel 95, do you know what is the number?

Author

Commented:
andrewssd3:,

Could you give me an example of working? create a simple excel file.

In  my computer I have the excel 2003 but the system will be installed in computer that have excel 2007.

I'd like to insert another value for excel 95 so that I can test it.

this is my code

        Excel.WorkBooks[1].SaveAs(arq, 56);
Top Expert 2011
Commented:
XlFileFormat.xlExcel9795 is 43.  You can find these yourself as I'm doing by going into the VBA Immediated window and typing
?XlFileFormat.xlExcel9795 

Open in new window

Intellisense will also give you the available list after you type XlFileFormat.

The version numbers for Excel are listed here: http://www.cpearson.com/excel/versions.htm.  95 is Version 7.0.  

In general if you just want to save in the default format for the version you are on, you can just use SaveAs without specifying a format and it will default correctly.  Your problem is with 2007 and later where the extensions have changed (all previous versions will be .xls), and you will need to be aware of whether the workbook contains macros or not to know wether to save as - so it will be either .xlsx (no macros), or .xlsm (macros) -
?XlFileFormat.xlOpenXMLWorkbook
 51 
?XlFileFormat.xlOpenXMLWorkbookMacroEnabled
 52 

Open in new window

If you know your workbook will not contain macros you could have some code like this (in VBA - don't know Delphi)
    Dim xlApp As Excel.Application
    Const FILENAME As String = "c:\excel\wbk1"
    
    Set xlApp = Application
    
    If Val(Application.Version) < 12 Then
        xlApp.Workbooks(1).SaveAs FILENAME:=FILENAME & ".XLS"
    Else
        xlApp.Workbooks(1).SaveAs FILENAME:=FILENAME & ".XLSX"
    End If

Open in new window

If it does have macros then you would need to specify the format of 52:
        xlApp.Workbooks(1).SaveAs FILENAME:=FILENAME & ".XLSM", FileFormat:=52

Open in new window

Author

Commented:
thanks

Explore More ContentExplore courses, solutions, and other research materials related to this topic.