Determining the version of Excel

[alepage]

I would like to obtain a sas code that will permit me to identify the version of Excel installed to run the appropriate PROC EXPORT code.  Regards,

[SASKiwi]

Wouldn't your Excel SAS export code depend on the type of Excel workbook (XLS, XLSX etc) being produced, not the version of Excel installed? 

[Reeza]

If your Excel 2010 or above using XLSX is probably safe. 

Hopefully no one is running 2003.

Can you assume a particular OS, ie Windows 7/10 or is XP/Vista/Unix a possibility?

I would tackle this by either 

1) Looking for an environment variable or system registry setting that may exist

2) Checking file paths commonly used for Excel

However, it is possible to have multiple Excel versions installed.... 

[Reeza]

---

@Reeza wrote:

If your Excel 2010 or above using XLSX is probably safe. 

Hopefully no one is running 2003.

Can you assume a particular OS, ie Windows 7/10 or is XP/Vista/Unix a possibility?

I would tackle this by either 

1) Looking for an environment variable or system registry setting that may exist

2) Checking file paths commonly used for Excel

However, it is possible to have multiple Excel versions installed.... 

---

It was suggested to you....you never did answer the OS question 🙂

The last part is still an issue. 

[RW9]

I would agree with @Reeza and @SASKiwi, Excel version is irrelevant to the code you write.  Your code - which you will have planned of course - will be targeted to deliver a certain output, that output is determined up front.  How you determine that output is part of the Software Development plan you will have designed before writing code.  

To actually create a file that is openable in Excel 2007 or later you can simply use tagsets.excelxp or one of the others - this requires no version of Excel installed as it is plain text with tags, but Excel can read this.  CSV is another option which is available in all versions.  

The difficulty comes when you need to create native XLSX (2007 and later) or XLS (all versions, though being phased out).  XLS is a binary proprietary file format.  I would really advise not to use such a thing.  XLSX is the latest Open Office (no pun intended there) file format for Excel files.  It consists of a zip file (rename .xlsx to .zip and open it and you will see this), with various XML files, subfolders with further XML files inside it.  Now assuimng you can compress files (e.g. proc document) then you could create the native XLSX files without any Excel present, however it is a ton of work.  You are probably better off targetting XLSX file format and using the appropriate tools for that.

Note, I assume that none of this is for data transfer, only for review output files correct?  If it is for data transfer then I would advise to use a proper data transfer format - CSV, XML, Dataset etc. - and dump Excel completely.

[alepage]

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

Good evening,

I need a way to get the version of Excel installed on a computer because the parameters for the proc export from a SAS table to an excel file will be different as well as the maximum number of lines in a sheet. (approximately 65000 lines in excel 2003, almost a million in excel 2013).

Regards,

[RW9]

Hi,

Sorry, you haven't answered any of the questions given.  Why is this an issue, are you running several old versions of Excel?  What is the need for it?  If your worried about size restrcitions, then that is a problem with the other software on your system not SAS.  SAS can export to a variety of formats Excel, CSV etc. and Excel is pretty compatible within itself.  Again, what is the purpose of this, if ou have a file with 65000 records then you are not producing an output report are you (or you are producing something which will never be looked at!).  So you are doing a data transfer, look into proper data transfer file formats - such as XML and CSV - both of which are plain text portable to any system and readable in any software, have no restrictions on size, and are easier for the recipient to deal with.

[alepage]

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

Hello,

It is my first time that I am contacting the SAS community.  I noticed that I will gain in efficiency if my question is more precise.  

I need to write a SAS  program mostly a proc export to export data from a SAS table to an excel file.  My code need to be versatile. So my question was if there is a SAS function that I can use to check the version of Excel installed on a computer.

If I can get this piece of information with a SAS instruction, I cant write a code like that

if excel 2003 then do

     instructions which fit with that version

end

if excel 2007 then do

     instructions which fit with that version

end

if excel 2013 then do

     instructions which fit with that version

end

In that manner, my code will be able to run independently of the version installed on a computer without any error.

Also, I don't want to go into philosophic discussion.  I am trying a way to accomodate various users for the same code.

Regards,

[Reeza]

To be frank, it would be easier to ask users to specify export specifications which could be as simple as a macro parameter defined as XLS or XLSX. Excel 2003 supports 65k and Excel 2007 supports a million + records so it seems like you're really only dealing with two alternatives. The SAS portion shouldn't change for Excel 2007+. 

So SAS has no automatic way of detecting Excel version. Essentially, this isn't a SAS problem but how do you obtain the information from the operating system. Which hopefully you can assume that this will always be Windows. If you need to account for Windows XP/Vista/7/10 & Unix you may be compounding your problem. 

As I mentioned earlier it does look like the version can be pulled from the registry settings. This would be some sort of DOS command most likely. Once you determine the DOS command you can use an X or SYSEXEC statement to execute the DOS command. However, if you're also potentially dealing with various SAS installation issues you may or may not be able to execute those commands. 

So that's another level to your problem. 

All that being said, I've pointed you to the registry command. You should be able to figure out the DOS command or ask that question in a StackOverflow or DOS forum. 

I'm not sure exactly what you're looking for beyond this. This is a community forum for users and the impetus to provide full code solution versus methods/direction is up to users and personally it's dependent on how interesting and/or the difficulty level of a problem.  Your question is very specific and crosses over from SAS to OS issues which many typical programmers may not deal with, so it's a smaller user base. There's also so many variations and limitations of a solution so developing one that would only be used by one person isn't a huge motivator. I also don't have access to a Windows machine.

I'm certain if you have specific SAS programming questions you'd get better answers. Good Luck! 

[alepage]

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

I have found the code that I was looking for.  See the link below:

http://www.sas.com/offices/europe/uk/support/sas-hints-tips/excel-proc-export.html

Regards,

[alepage]

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

Good evening,

I need a way to get the version of Excel installed on a computer because the parameters for the proc export from a SAS table to an excel file will be different as well as the maximum number of lines in a sheet. (approximately 65000 lines in excel 2003, almost a million in excel 2013).

Regards,

[Reeza]

Can you use X commands? 

There does appear to be a registry key available. Example here is for Word, but Excel is probably available. 

Look for the following entry in your Registry:

HKEY_CLASSES_ROOT\Excel.Application\CurVer

http://stackoverflow.com/questions/3266675/how-to-detect-installed-version-of-ms-office

[LinusH]

Excel version from 2007 at least are quite forward and backward compatible so why is this an issue?
Also, an Excel file can shared/forwarded to other with potentially other versions; so...
Perhaps Use csv instead, should be quite Excel version safe 😉