VB6 with Excel

[AM003295]

I have a VB6 program that puts data into an Excel workbook, this is working good except now we are switching from Excel 2003 to 2010, and I am having trouble. I belive the problem is in closing the 2003 workbook with the 2010 excel. I don't want to convert the 2003 workbook to 2010 yet because not everone has changed to Excel 2010. is there a way that I can force Excel 2010 to save the Excel 2003 workbook as a 2003 workbook?

I think the extentions are differnet between the 2003 and 2010 Excels.

[Ron Weller]

Use SaveAs for the workbook. It has a file format option.
ActiveWorkbook.SaveAs fileFormat:= xlExcel7

[johnblack]

Yes,
Totally agree with that use Save As Option from File Menu for saving workbook. with that you can save your workbook with different extension.

[AM003295]

I take it the xlExcel7 means save as Excel 2010 ?? With or without macros??

In my VB6 program I open an existing Excel 2003, are you saying that if I use save as xlExcel7 it would than save my file as Excel 2003??

Thanks

[Hack]

This might help...

http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

[AM003295]

Hack

Thanks for the direction, however its a little over my head so maybe a little more help. I see the

VB
Copy
'Usage
Dim xlFileFormat1 As XlFileFormat = XlFileFormat.xlAddIn

but don't understand what its doing or how to use it. is it meaning to use this xlFileFormat1 as the save as file type??

Also under members it has xLExcel7 with a description of Excel 95, Hmmm ??

[AM003295]

I perfer not to use the "Save as" but would perfer to use only the "Save". That is because the "Save as" will ask my users if they want to overwrite the existing one where "Save" dose not. One less thing for users to get confused about.

[Ron Weller]

You can still use the Workbook.Close method with the save changes option, just set the format ahead of time.

ActiveWorkbook.FileFormat = xlExcel8

'xlExcel7 is 2002 format so xlExcel8 should be 2003 format

[Ron Weller]

I take it the xlExcel7 means save as Excel 2010 ?? With or without macros??

In my VB6 program I open an existing Excel 2003, are you saying that if I use save as xlExcel7 it would than save my file as Excel 2003??

Thanks

No, xlWorkbookDefault is always the current version, so if you have 2007 then that is the version is would save it as. But if you use xlExcel8 then it will use Excel8 format which I believe is version 2003. I myself only have version 2002 which includes an xlExcel7 but does not include an xlExcel8. I searched the web for the 2007 docuemntation and found that it has an xlExcel8, but no xlExcel9, so I am guessing that xlExcel8 is 2003 format.

[AM003295]

I'll give this a try,

thanks for the help.

[AM003295]

I put the following line of code in my program

ActiveWorkbook.FileFormat = xlExcel8

and its coming up with the .FileFormat is a read only property.
if I read the ActiveWorkbook.FileFormat (after opening an Excel 2003 file) into a variable I get "43"

Hmmmm looks like I can not set the file type using that property.

[AM003295]

OK, here is what I think is going on. If I have a VB6 program running on a computer with Office 2010, and the VB6 program opens a 2003 Excel program, the VB program can not "save" (not save as) that workbook without getting an error. However if I manual open that same 2003 Excel workbook in 2010 Excel, then manual save it as a 2003 Excel workbook, from then on my VB6 program can open and "save" that 2003 Excel workbook without the error.

Hopes this helps someone in the future.

[Hack]

I wonder if the same thing would happen with VB.NET - theoretically, it shouldn't, but one never knows.

Thanks for posting your solution.