Determine Default Excel File Extension 3

[Auguy]

I'm sure this has been answered before, but is there a way to determine what the Excel default file extension is? I'm using automation and saving some Excel files using the SAVEAS command and do not want to specify the extension because different users have different versions of Excel. After all the files are saved, I email them to various people and I need to know what the exact file name is. I'm guessing that if I could get the Excel version I could map the version to a default extension, or is the default extension available somewhere in Excel?

Auguy
Northwest Ohio

 

[Mike Gagnon]

Isn't Excel either "xls" or "xlsx" ? Unless their are saving as a template.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.
ReFox XI (www.mcrgsoftware.com)

 

[Mike Lewis]

Auguy,

oExcel = CREATEOBJECT("excel.application")
? oExcel.DefaultSaveFormat

That will tell you the default format selected by the user in the Options dialogue or equivalent. This is the default for the "save as type" control in the Save As dialogue.

It doesn't directly give the extension. It tells you whether the choice is Excel workbook, text file (CSV), web page (HTM, HTML), and so on.

Is that what you're looking for?

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Auguy]

Thanks to both of you. I'd like to determine if the default extension is xls or xlsx. I guess I could look in my saved file folder for the file with one extension or the other, but that seems like the hard method and possibly time consuming. I seem to remember that there is a way of getting the Excel version number, I could work off of that.

Auguy
Northwest Ohio

 

[Mike Lewis]

You can get the version number by looking at the Version property, but I'm not sure how that will help.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[baltman]

xlWorkbookNormal value is -4143. If you ssaveas with that constant doesn't the appropriate extension get added if you don't specify? Sorry I can't test right now.

 

[Auguy]

I'm hoping I can match the version number to the default extension for a workbook saved under that version.

Auguy
Northwest Ohio

 

[baltman]

oExcel=CREATEOBJECT("excel.application")
?oExcel.Version
?oExcel.DefaultSaveFormat
oExcel.Quit

 

[Auguy]

OK, I found the following statement which I can use with the version number to determine the default extension.

"Excel saves files using a .xls extension (Excel 2003 and earlier) or .xlsx extenstion (Excel 2007 and later)."

myVersion = Val(oExcel.Version)
    Select Case myVersion
        Case Is < 8 * ExcelVersion = "Pre Excel 97"
        Case Is = 8 * ExcelVersion = "Excel 97"
        Case Is = 9 * ExcelVersion = "Excel 2000"
        Case Is = 10 * ExcelVersion = "Excel 2002"
        Case Is = 11 * ExcelVersion = "Excel 2003"
        Case Is > 11 * ExcelVersion = "Post Excel 2003"
    End Select

Thanks to all for your help.



Auguy
Northwest Ohio

 

[Mike Lewis]

Well, I suggested using the version property, but I still don't see how it solves the problem.

If the version shows up as "Post Excel 2003", how do you know if the user has chosen XLS or XLSX as the default extension?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Auguy]

Yes, you are correct Mike. For my limited number of users my simplistic approach will probably work just fine. I'm going to use Baltman's suggestion of using "xlWorkbookNormal value is -4143" in the SaveAs command combined with the version number and I will know if the extension is xls or xlsx.

Auguy
Northwest Ohio

 

[Auguy]

This is off the topic of this thread, but how soon after I email these Excel files as attachments can I delete them? I'm usually emailing around 50 individual files to 50 different people. I'd really like to clean them out of the temp folder ASAP after emailing them. Anybody have any experience with this sort of thing?

Auguy
Northwest Ohio

 

[Mike Lewis]

Yes. Assuming you know the names of the files in question, just do a DELETE FILE. Actually, you can do that even if you don't know the file names.

For example, if the files are held in the user's default temp directory, and they are all XLS files, you would do this:

lcFile = FORCEPATH("*.XLS", SYS(2023))
DELETE FILE (lcFile)

That might also delete any other XLS in the temp folder, but that shouldn't matter. By definition, they are temporary files.

It might be a good idea to wrap the DELETE command in TRY / ENDTRY construct, and ignore any error that arises. If a file cannot be deleted (perhaps because it is open in another process), you probably don't want to halt the program.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Auguy]

Thanks Mike, great advice as always.

Auguy
Northwest Ohio

 

[TamarGranor]

I see that a solution was found here, but I think there's a much easier way. Once you save the file, you can access the file name.

JUSTEXT(oXL.ActiveWorkbook.FileName) gives you the extension.

Tamar

 

[Auguy]

Thanks Tamar, so simple.

Auguy
Northwest Ohio

 

[JRB-Bldr]

"After all the files are saved, I email them to various people"
"different users have different versions of Excel"
"I'm guessing that if I could get the Excel version I could map the version to a default extension"

I don't quite understand things.

Your users have varying versions of Excel.

But you want to do a VFP Automation of Excel SAVEAS to the various Excel version formats based on the user's own specific version of Excel?

Why would you want to do that?

"After all the files are saved, I email them to various people and I need to know what the exact file name is."

By doing your SAVEAS based on the individual user's version of Excel, you would only create a situation where the users working with Excel 2007/2010 would create files which would likely not be usable by those email recipients using Excel 2000/2003.
NOTE - Without implementing the Microsoft 'patch' for Excel 2003 and earlier, those versions cannot open and/or utilize '.XLSX' files

If I were developing for an office where different versions of Excel were used, then I'd have all of my Excel files saved to the 'lowest common denominator' so that everyone could share and use the file(s) - that 'lowest common denominator' being '.XLS'

Plus by doing your SAVEAS always to the '.XLS' version it is easy to "know what the exact file name is."

Good Luck,
JRB-Bldr

 

[Auguy]

Thanks JRB-Bldr. I will reconsider my approach and discuss with the client.

Auguy
Northwest Ohio

 

[Olaf Doschke]

Hello Auguy,

Why not simply give your client an option to define what excel format is produced, maybe even per recipient. Then they can adapt to the newest excel format once their recipients can read such excel files.

Regarding when you're able to delete the excel files: If you add a mail attachment, outlook does "mail-encode" the files, they are part of the mail to be sent (Typically base64 encoded within a multipart mime mail).

Not sure whether that kind of encoding is done right away or just in time when you call the outlook mail item SEND() method, but as soon as the mail is in the outbox the file on disk is obsolete. The same goes for SAVE(), as soon as a mail item is in the draft folder it is complete and not depending on the files on disk any more.

Bye, Olaf.

 

[Auguy]

Olaf, Thanks for the email info. I had always thought that about the attachments, but was never sure.

Auguy
Northwest Ohio