SAS error when invoked by Excel VBA

Reply
Contributor
Posts: 24

SAS error when invoked by Excel VBA

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

Trusted Advisor
Posts: 1,508

Re: SAS error when invoked by Excel VBA

What happens when you run the command

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

Trusted Advisor
Posts: 1,508

Re: SAS error when invoked by Excel VBA

Can you reply to the question above?

Trusted Advisor
Posts: 1,508

Re: SAS error when invoked by Excel VBA

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

Contributor
Posts: 24

Re: SAS error when invoked by Excel VBA

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.

Frequent Contributor
Posts: 102

Re: SAS error when invoked by Excel VBA

[ Edited ]

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.

 

 

________________________

- That still only counts as one -

Contributor
Posts: 24

Re: SAS error when invoked by Excel VBA

I'm not sure where I enter this. Do I put it in the SAS editor ? Do I put the word "type"?
Trusted Advisor
Posts: 1,508

Re: SAS error when invoked by Excel VBA

You run this in the DOS command line window.

Contributor
Posts: 24

Re: SAS error when invoked by Excel VBA

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.

Trusted Advisor
Posts: 1,508

Re: SAS error when invoked by Excel VBA

And what do you get when you run this:

set | find "SAS"

Contributor
Posts: 24

Re: SAS error when invoked by Excel VBA

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)".

Trusted Advisor
Posts: 1,508

Re: SAS error when invoked by Excel VBA

Please paste the results here.

Contributor
Posts: 24

Re: SAS error when invoked by Excel VBA

Capture.JPG

Trusted Advisor
Posts: 1,508

Re: SAS error when invoked by Excel VBA

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.

Frequent Contributor
Posts: 102

Re: SAS error when invoked by Excel VBA

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.

________________________

- That still only counts as one -

Ask a Question
Discussion stats
  • 14 replies
  • 135 views
  • 0 likes
  • 3 in conversation