topic Re: SAS error when invoked by Excel VBA in SAS Programming

SAS error when invoked by Excel VBA

suncawy — Mon, 26 Jun 2017 01:32:51 GMT

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

Re: SAS error when invoked by Excel VBA

ChrisNZ — Mon, 26 Jun 2017 03:25:53 GMT

What happens when you run the command

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

Re: SAS error when invoked by Excel VBA

ChrisNZ — Mon, 26 Jun 2017 03:28:58 GMT

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

Re: SAS error when invoked by Excel VBA

suncawy — Mon, 26 Jun 2017 13:53:29 GMT

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.

Re: SAS error when invoked by Excel VBA

Oligolas — Mon, 26 Jun 2017 14:39:09 GMT

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.

Re: SAS error when invoked by Excel VBA

ChrisNZ — Mon, 26 Jun 2017 23:44:43 GMT

Can you reply to the question above?

Re: SAS error when invoked by Excel VBA

suncawy — Mon, 26 Jun 2017 23:57:13 GMT

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

Re: SAS error when invoked by Excel VBA

ChrisNZ — Tue, 27 Jun 2017 01:15:58 GMT

You run this in the DOS command line window.

Re: SAS error when invoked by Excel VBA

suncawy — Tue, 27 Jun 2017 03:13:29 GMT

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.

Re: SAS error when invoked by Excel VBA

ChrisNZ — Tue, 27 Jun 2017 06:17:05 GMT

And what do you get when you run this:

set | find "SAS"

Re: SAS error when invoked by Excel VBA

suncawy — Tue, 27 Jun 2017 14:19:59 GMT

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

Re: SAS error when invoked by Excel VBA

ChrisNZ — Tue, 27 Jun 2017 21:41:01 GMT

Please paste the results here.

Re: SAS error when invoked by Excel VBA

suncawy — Tue, 27 Jun 2017 22:34:17 GMT

Re: SAS error when invoked by Excel VBA

ChrisNZ — Tue, 27 Jun 2017 22:53:44 GMT

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.

Re: SAS error when invoked by Excel VBA

Oligolas — Thu, 29 Jun 2017 12:08:35 GMT

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.