VB 6 with Excel 2007 error 3

[SunBeachWaves]

Installed VB6 application on user's PC which runs MS Office 2007. Application tries to produce an excel file. I get "Run-time error '1004', Method '~' of object '~' failed"
when trying to save excel file in format "xlExcel9795".
(See ** for code that causes the failure.)
fyi - this code works with previous versions of Excel.

If OpenExcel = True Then
With xlApp
.ActiveWindow.ScrollColumn = 1
.Cells.Select
.Selection.Columns.AutoFit
.Range("A1").Select
strMsg = "(5) After Last Column/Row! "
** .ActiveWorkbook.SaveAs FileName:=strYEApath,
** FileFormat:=xlExcel9795, CreateBackup:=False
End With

Is there another FileFormat I should be using? Is there something I need to set/change in Excel 2007? Is there some kind of download that would fix this?

TIA,

 

[MakeItSo]

I did one blind google on "xlExcel9597".
GUess what came out?
==>Over 5000 hits, one of them this little jewel:

Why are you still using the 9795 format? This format really stores two versions of the file (Excel 95 and Excel 97-2003) within the 9795 file, bloating the workbook for no good reason. Do you really need the Excel 95 format for anything?

And yes, the 9795 format is gone from 2007.

Question answered?
;-)



[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[SunBeachWaves]

MakeItSo,

I thought xlExcel9795 was the newest we had. I have xlExcel7 and xlExcel5...is one of these newer? If not, is there a place I can download a better/newer one that will work with VB6?

We are running VB6 SP6.

 

[HughLerwill]

To save files in 97>2003 format under versions of Excel 97>2007 use;

If val(xlApp.version) < 12 Then
.ActiveWorkbook.SaveAs "MyFile.xls" 'uses the default format
Else
.ActiveWorkbook.SaveAs "MyFile.xls", 56 '56 = xlExcel8 in Excel 12; xlExcel8 is not a recognized constant in versions<12
End If

 

[SunBeachWaves]

How would I acquire the ability for VB6 to recognize xlExcel8?
Is there something I can pull as a download? ...or... do I have to have a higher version of excel on my PC where I compile the VB6 application, in order for VB6 to pickup the xlExcel8 as a valid option for the .ActiveWorkbook.SaveAs FileFormat:=xlExcel8 statement?

TIA!

 

[HughLerwill]

Val(xlApp.Version) returns the version of Excel in use.
If it's = 8 then Excel 8/ Office 97 is in use

If you must use named arguments you should use
FileFormat:=56 if you are compiling in an environment with Excel 2003 (version 11) or less because as previously advised the xlExcel8 constant is not recognised in Excel versions <12 (aka 2007).

 

[SunBeachWaves]

Thanks HughLervwill! so its the constants that VB doesn't know. Where did you find the translation (56) for the fileformat? VB6 help didn't show the numeric equivalent.

TIA!

 

[HughLerwill]

>Where did you find the translation (56) for the fileformat?

From Excel 2007 VBA Help - XlFileFormat Enumeration

And now I come to look at it again; quote
<<
etc.
xlExcel8 56 Excel8
xlExcel9795 43 Excel9795
xlHtml 44 HTML format
etc.
>>
I note that xlExcel9795 DOES still appear to be listed, could be it does'nt work though, I hav'nt played with it.

 

[SunBeachWaves]

HughLerwill,
xlExcel9795 constant was definitely causing my code to fail when a user's PC had Excel 2007 on it.

Thanks... you were most helpful.

 

[BobRodes]

<Where did you find the translation (56) for the fileformat?

You can always go to the debug window and type ? ConstantName. The result will be the decimal value. E. g. "? vbokonly" will return 0.