Solved: How to check if Excel is run from REMOTE server

[Bob Phillips]

As for your comment on dynamic variables - what exactly did you mean? What is an example of a dynamic variable?

I wasn't talking about dynamic variables, I was referring to dynamic loading of Environmental variables.

What I mean is that it seems that Excel loads the values of the environmental variables into memory upon startup, presumably to give faster access (sic!). Because of this, if those variables are updated during an Excel session, you will not see those changes until the next time that Excel starts.

But as I said, I don't foresee a problem with that with values such as computer name.

Do you feel that this function is suitable given the following context:
[/I] - To give some context for this thread, the purpose of this thread was to batch SAS programs using Excel-VBA as a front end. SAS is only run from the SAS Server, in this case using specific VBIDE references to the SAS Server libraries. As such I want the User to close the workbook that runs the SAS programs if they are haven't opened it from the SAS server. Will the Environ variables ever cause problems for this purpose?

I am not sure I understand this, but I wouldn't expect the environmental variables to be the thing to give you problems.

[Kenneth Hobs]

VBA's Environ() will not update when reset for that session using an API method to reset it. I used API methods to set and get environment variables which worked fine. One of the four methods that I tried using WScript acted the same as Environ(). The other three worked just as well as the API method.

Not sure which SAS envrionment variable you would have that could tell you what you need. I used to use SAS on the mainframe. A friend uses PCSAS which I could check if needed.

You can check the environment variables manually or add a command button to a sheet and add this code to view with a button click. Put the first part in a Module and the Sub as the button's click event.

[vba] Declare Function FindWindow _
Lib "user32" _
Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Private Sub CommandButton2_Click()
Dim rc As Long
'Shell "cmd /c RunDll32.exe shell32.dll,Control_RunDLL sysdm.cpl,,3", vbNormalFocus
Shell "Control.exe sysdm.cpl,,3", vbNormalFocus
Do Until FindWindow(vbNullString, "System Properties") <> 0
Loop
Application.SendKeys "n", True
End Sub
[/vba]

I am not sure how you are starting Excel from SAS. You could use the registry to poke and peek for what you need. VBA's GetSetting() and SaveSetting() can be used for that.

You could pass a parameter in the call to Excel's EXE. VBA code can be used to get the command line parameter values. It takes a bit of code to do this so I avoid it generally.


Other methods could be used as well. e.g. Poke status into a TXT file.

[xluser2007]

VBA's Environ() will not update when reset for that session using an API method to reset it. I used API methods to set and get environment variables which worked fine. One of the four methods that I tried using WScript acted the same as Environ(). The other three worked just as well as the API method.

Not sure which SAS envrionment variable you would have that could tell you what you need. I used to use SAS on the mainframe. A friend uses PCSAS which I could check if needed.

You can check the environment variables manually or add a command button to a sheet and add this code to view with a button click. Put the first part in a Module and the Sub as the button's click event.

[vba] Declare Function FindWindow _
Lib "user32" _
Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Private Sub CommandButton2_Click()
Dim rc As Long
'Shell "cmd /c RunDll32.exe shell32.dll,Control_RunDLL sysdm.cpl,,3", vbNormalFocus
Shell "Control.exe sysdm.cpl,,3", vbNormalFocus
Do Until FindWindow(vbNullString, "System Properties") <> 0
Loop
Application.SendKeys "n", True
End Sub
[/vba]

Kenneth, thanks for your contribution. I have tried running your code and have the following screenshot of the outcome.

I wasn't sure how to use this exactly though. Could you please clarify (my understanding of API's is extremely novice in relation to yours ).?

I am not sure how you are starting Excel from SAS. You could use the registry to poke and peek for what you need. VBA's GetSetting() and SaveSetting() can be used for that.

I am not actually starting Excel from SAS the application, rather trying to batch run SAS Programs from an Excel workbook. Using Excel as a front end allows for more clarity and controlling in opening and batching SAS programs as required.

Given that SAS programs (macros) can only be run from teh SAS Server, I need a simple UDF to check that the Excel Application is open from the SAS Server, not the local Server.

I have clarified tis more in my reply to Bob, in my previous post.

Please let me know if you need me to specify further information.

The UDF designed in earlier posts seems to be working, but based on the issues raised about the static nature of these Environ variables, I would be keen to develop and understand and API UDF approach.

Sincere thanks and regards for your interest in this problem.

[xluser2007]

I wasn't talking about dynamic variables, I was referring to dynamic loading of Environmental variables.

What I mean is that it seems that Excel loads the values of the environmental variables into memory upon startup, presumably to give faster access (sic!). Because of this, if those variables are updated during an Excel session, you will not see those changes until the next time that Excel starts.

But as I said, I don't foresee a problem with that with values such as computer name.

Thanks for the lucid explanation Bob, I don't see a particular issue with the Computer name, if Excel is open on 2 separate servers, one local and one SAS.

I am not sure I understand this, but I wouldn't expect the environmental variables to be the thing to give you problems.

Just to clarify and make it easier to understand I can explain as follows, basically SAS is a powerful, but memory intensive software. As such, it has its own server dedicated for its operation.

To use it first you must log in to your local server.

Then you go to Start > Accessories > Remote Desktop Connection.

This pops up with a login (username, password) screen for the SAS Server. Once you pass this a vertual screen pops up to teh Sas Server, you can then toggle between the SAS Server and Local Server etc.

To run SAS from Excel (which is what I want to do) in this exercise, you need to loginto the SAS Server (based on teh above instructions), then open Excel when in teh SAS Server itself. This is what i want my function to check i.e. are you running the Excel workbook in the SAS Server or local Server, if the former, then batch the SAS using relevant shell commands, if not, then prompt the User to close the workbook and login to the SAS Server and open it from there batch to run the SAS macros from teh workbook.

Sorry for the lengthy explanation, I am learning in the process of experimenting.

The above function is working quite well from my tests, but I see Kenneth has also introduced some really interesting API approaches which seem interesting and am keen to apply.

Thanks and regards,