SAS error when invoked by Excel VBA

[suncawy]

I know launching PC SAS from excel VBA might be old, but this code use to work on a 32 bit windows machine

when I used Excel Vba:

Dim SAS As Object

Set SAS = CreateObject("SAS.Application")

SAS.Visible = True

Now, that our IT department gave me a 64 bit windows machine I get the following error in SAS: "Error: Configuration file "%_SASHOME%\SASFoundation\9.4\SASV9.CFG"" is unreadable."

The funny thing is that the error occurs only when invoking SAS from excel vba.  If I start SAS by clicking on the actual program, it launches SAS normally.  The machine has SAS 9.4 64 bit installed. Also, its has office 365 ( Excel 2016).

Anybody with a solution ? is it because of the 64 bit installation of SAS ? Thanks

[ChrisNZ]

What happens when you run the command

type %_SASHOME%\SASFoundation\9.4\SASV9.CFG

[ChrisNZ]

Can you reply to the question above?

[ChrisNZ]

Also, I reckon the environment variable should be called SASHOME, not _SASHOME

[suncawy]

Not sure how to change the environment variable when its launching from excel.  Yet, when I just click on SAS to open an issue, there is no need to change the environment variable since it opens normally.

Thanks.

[Oligolas]

if %_SASHOME% resolves to a valid path when entered in the adress bar then your windows environment variable is correct. If not I would try %SASHOME%

That's one thing.

The second thing is that the paths in your config file may have changed, you will need to check them too.

[suncawy]

I'm not sure where I enter this. Do I put it in the SAS editor ? Do I put the word "type"?

[ChrisNZ]

You run this in the DOS command line window.

[suncawy]

So, the result I get is that It can't find the path "%_SASHOME%\SASFoundation\9.4\SASV9.CFG".  That makes sense because SAS 9.4 is installed in the following:  C:\Program Files\SASHOME\SASFoundation\9.4.   Yes, the initial configuration file is there and then points to \nl\en\SASV9.CFG.  It's odd that excel invokes it to that incorrect directory, but the SAS application itself opens it correctly.

[ChrisNZ]

And what do you get when you run this:

set | find "SAS"

[suncawy]

There's a lot references. Does show "C:\program files\SASHOME\x86\Secure\ccme4". Don't know what that means, but I don't see anything related to SAS in the "C:\program files(x86)".

[ChrisNZ]

Please paste the results here.

[suncawy]

[ChrisNZ]

This does not look right. It's as if SAS was not installed there.

You have none of the expected environment variables, such as SASHOME, SASROOT, SASCFG, etc.

A quick fix to make the reference VBA call work would be to create the environment variable _SASHOME  pointing to wherever the config file is. Something like:

set _SASHOME=C:\Program Files\SASHOME\SASFoundation\9.4\SASV9.CFG

You do this for the whole Windows session not just in a DOS command line Window.

But something is not right with your installation it seems.

[Oligolas]

SAS is installed but the environment variable _SASHOME is not defined. Consult your IT and describe the problem, that's part of their job. You could do it by yourself, but then it's your responsability if something goes wrong.