Save excel sheet into excel 95

[Paco75]

Hi,

I use the import function to convert a excel file to dbf file. But it only works with excel 95 files. So if i have excel files from latest versions (97, 2003, 2007...) i have to manually save them in excel 95 files.

I there a way to save a excel file into another format programmatically?

thanks

 

[Mike Lewis]

Paco,

Are you sure about that?

The IMPORT command (not a function) and APPEND FROM both work with workbooks saved in Excel 97 to 2003. It's only 2007 and later that give problems.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Paco75]

Yep im sure if i save the file in 97-2003 and run the program i got a message saying that excel has stopped working and wil be closed.

 

[Paco75]

well unless another trouble unknown to me is causing this error...

 

[Mike Lewis]

i got a message saying that excel has stopped working and wil be closed

That suggest you are not using IMPORT. The message looks like one generated by Excel itself.

Perhaps you could let us know exactly how you are going about this import.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Paco75]

MyFile = GETFILE("Excel *.xls:XLS","Import the excel file:","Import",0,"Choose the file to import")
IF NOT EMPTY(MyFile) THEN 
	IMPORT FROM JUSTFNAME(MyFile) TYPE XL8  && imports the XLS into a DBF
ENDIF

 

[Mike Lewis]

Paco, I just tried your code, using workbooks that were saved in 2007 and 2000. It worked fine in both cases.

However, you need to remove the JUSTFNAME(). VFP needs to know the full name plus extension, even though it knows it's TYPE XL8.

Furthermore, this command doesn't actually fire up Excel, so the message you saw must have been caused by something else.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Paco75]

Weird, here it does not work unless i save the excel file as Excel 5.0/95 Workbook :S

 

[Paco75]

if i try with excel 2007 i got the following error:

"Microsoft Excel file format is invalid.

 

[Paco75]

Is there a way to open and save the file in excel 95? even if it opens excel and close it it would be acceptable.

 

[Paco75]

i mean in an automated way of course

 

[Mike Lewis]

if i try with excel 2007 i got the following error: "Microsoft Excel file format is invalid."

I told you earlier that you can't use the native VFP import methods with files saved in Excel 2007 or above. That applies even if you save it in 2007 format, or 97 - 2003 format, but not 95/5.0. Which leads to your next question:

Is there a way to open and save the file in excel 95?

Yes, you can do that through Automation, with all versions of Excel. Just call your workbook object's SaveAs method, passing the filename to save to, and the ID of the file format, which in this case happens to be 43. In other words:

* Assume loWorkbook is your workbook
loWorkbook.SaveAs("c:\data\myworkbook.xls", 43)

* Then close Excel in the normal way

Obviously, that's not a complete bit of working code, but you should be able to figure the rest of it out for yourself.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Paco75]

I found this link that helped me in case someone want to save in another excel format (just as with excel save as)
[URL unfurl="true"]http://fox.wikis.com/wc.dll?Wiki~ExcelAutomation[/url]

Here is my code

MyFile = GETFILE("Excel *.xls:XLS,XLSX","Importer le fichier excel:","Importer",0,"Choisissez le fichier à importer")

MyNewFile = JUSTPATH(MyFile)+"\New"+JUSTFNAME(MyFile)

oExcel = CreateObject("Excel.Application")
if vartype(oExcel) != "O"
  * could not instantiate Excel object
  * show an error message here
  return .F.
ELSE 
	oWorkbook = oExcel.Application.Workbooks.Open(MyFile)
	if val(oExcel.Version) > 7
	    oWorkbook.SaveAs(MyNewFile, 39) && xlExcel5
	else
	    oWorkbook.SaveAs(MyNewFile)
	ENDIF
	oExcel.quit() && Close excel instance
ENDIF
oExcel = .Null. && To avoid leaving open excel instance...

IF NOT EMPTY(MyNewFile) THEN 
	IMPORT FROM (MyNewFile) TYPE XL8  && imports the XLS into a DBF
ENDIF

 

[Paco75]

I seem to use the wrong value for excel5 files... you seem to say it is 43 not 39... but it solves the problem i got previously... maybe the excel was having bad formatting and saving it corrected it. Anyways, thanks for the help!

 

[Mike Lewis]

You're welcome, Paco. I got the figure of 43 from the VBA Help file. I didn't test it.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Paco75]

#DEFINE xlExcel5 39
#DEFINE xlExcel7 39
#DEFINE xlExcel9795 43

i found variables here
[URL unfurl="true"]http://fox.wikis.com/wc.dll?Wiki~ExcelConstants[/url]