Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login
Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Tek-Tips LinkedIn® Group

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site
Partner Button
(Download This Button Today!)

Feedback

"...It's fun to see others going through the same stuff I did and be able to help. It's also a way for me to stay sharp and not lose the stuff I've learned..."
More...

Geography

Where in the world do Tek-Tips members come from?
Click Here To Find Out!

Tek-Tips Shirts!

Get Tek-Tips Swag
Get your
Tek-Tips Forums
SWAG here!
Home > Forums > Programmers > DBMS Packages > Microsoft: Visual FoxPro Forum

Save excel sheet into excel 95

thread184-1687481
Read More Threads Like This One
Paco75 (Programmer)
5 Jul 12 7:36
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
MikeLewis (Programmer)
5 Jul 12 7:42
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 (Programmer)
5 Jul 12 7:51
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 (Programmer)
5 Jul 12 7:59
well unless another trouble unknown to me is causing this error...
MikeLewis (Programmer)
5 Jul 12 8:15

Quote (Paco)

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 (Programmer)
5 Jul 12 8:53

CODE --> 

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
MikeLewis (Programmer)
5 Jul 12 9:19
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 (Programmer)
5 Jul 12 9:28
Weird, here it does not work unless i save the excel file as Excel 5.0/95 Workbook :S
Paco75 (Programmer)
5 Jul 12 9:48
if i try with excel 2007 i got the following error:

"Microsoft Excel file format is invalid."
Paco75 (Programmer)
5 Jul 12 10:04
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 (Programmer)
5 Jul 12 10:05
i mean in an automated way of course
MikeLewis (Programmer)
5 Jul 12 10:55

Quote (Paco)

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:

Quote (Paco)

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:

CODE

* 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 (Programmer)
5 Jul 12 10:57
I found this link that helped me in case someone want to save in another excel format (just as with excel save as)
http://fox.wikis.com/wc.dll?Wiki~ExcelAutomation

Here is my code

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 (Programmer)
5 Jul 12 11:40
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!
MikeLewis (Programmer)
5 Jul 12 11:43
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 (Programmer)
5 Jul 12 11:52
#DEFINE xlExcel5 39
#DEFINE xlExcel7 39
#DEFINE xlExcel9795 43

i found variables here
http://fox.wikis.com/wc.dll?Wiki~ExcelConstants
Read More Threads Like This One

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft: Visual FoxPro

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close