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

Hi All,

At work we use Excel 2003 and SAS (A Statistical/ database software).

Basically SAS is set up on a REMOTE SAS Server, which requires a login from your local server.

Excel is availible on the Local Computer, Local Server, and the SAS Server i.e. a remote server which you login from your local server.

Is there a simple way to check whether the User is using Excel from the SAS i.e. REMOTE Server?

Any help appreciated?

regards

Check what the Application.Path is.

Quote:

---

Originally Posted by xld

Check what the Application.Path is.

---

Bob, I just tried your suggestion.

Both local server and SAS Server returned:

C:\Program Files\Microsoft Office\OFFICE11

I was wondering whether there is way to do it using some "Environ" variables that are peculiar to Server names?

Actually I just tried running Environ("Computername") in Excel, on both servers, and the SAS Server Excel returns a string containg SAS inside this variable e.g. it returns a string of the form "***-SAS-YYY"

So I gues the "Checking"Part could simply be function like:

[vba]
Function IsSASServer() As Boolean
On Error GoTo IsSASServer_Error
IsSASServer = False
If InStr(1, Environ("Computername"), "SAS", vbTextCompare) > 0 Then

IsSASServer = True

End If
On Error GoTo 0
Exit Function
IsSASServer_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure IsSASServer"

End Function
[/vba]

Q1: What you think about the above logic i.e. do you feel it is robust? Although it seems to work, is there a more rigorous way to check?

Q2: Also how could the structure of the above code and error-handling be improved.

Thanks for your help.

That seems robust, although I must admit to have never used, nor needed to use, such a technique. The Environ variables are loaded when Excel starts, it is not dynamic, but you probably don't need them to be dynamic.

But I fail to see why Application.Path returned the same value. It should be the path that the application is in, so if you start Excel on a server, you should get a server path.

[vba]Sub Test()
Dim IsSASServer As Boolean
IsSASServer = InStr(1, Environ("Computername"), "SAS", vbTextCompare) > 0
MsgBox IsSASServer
End Sub
[/vba]

It would be worth a function if you pass the data ("SAS") to be checked.

Quote:

---

Originally Posted by mdmackillop

[vba]Sub Test()
Dim IsSASServer As Boolean
IsSASServer = InStr(1, Environ("Computername"), "SAS", vbTextCompare) > 0
MsgBox IsSASServer
End Sub
[/vba]
It would be worth a function if you pass the data ("SAS") to be checked.

---

md, thanks for your help.

This is similar to the Function I was trying to create in post #4. Is this what you meant. Do you agree with the error handling used - is any of it redundant or unnecessary?

Quote:

---

Originally Posted by xld

That seems robust, although I must admit to have never used, nor needed to use, such a technique. The Environ variables are loaded when Excel starts, it is not dynamic, but you probably don't need them to be dynamic.

But I fail to see why Application.Path returned the same value. It should be the path that the application is in, so if you start Excel on a server, you should get a server path.

---

I'm unsure of why the Application.Path method did not change in the SAS Server either. As I'm relatively inexperienced in Network programming (apart from using them), its difficult for me to understand why.

As for your comment on dynamic variables - what exactly did you mean? What is an example of a dynamic variable? Do you feel that this function is suitable given the following context:
- 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?

thanks and regards

Quote:

---

Originally Posted by xluser2007

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.

Quote:

---

Originally Posted by xluser2007

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.

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.

Quote:

---

Originally Posted by xld

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.

Quote:

---

Originally Posted by xld

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,

Quote:

---

Originally Posted by 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]

---

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 :)).?

Quote:

---

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.

The Environ variables are static, but surely it does not matter. The computer name will not (should not) change probably ever, certainly not during a session will it? As such, I don't see any need for an approach other than what you have.

Quote:

---

Originally Posted by xld

The Environ variables are staic, but surely it does not matter. The computer name will not change probably ever, certainly not during a session will it? As such, I don't see any need for an approach other than what you have.

---

Thanks Bob, appreciate your insights as always.

I was just keen to see an alternative approach as well, for learning purposes at least via Kenneth's API suggestion.

Though the current approach on limited testing seems to work well.

Thanks to contributions from Experts such as yourself, I am slowly developing a front end for running SAS through Excel.

If it works as planned, it will be a really nice way to automate stuff for the form going forward, with limited maintenance (hopefully).

I'll slowly start putting up the many queries I have for it to get your thoughts/ feedback on it. :)

sincere regards,

You can use something like this if you need to check if sas is running.

[VBA]
Sub IsSASRunning()
If ISexeRunning("sas.exe") then
MsgBox "Yes, SAS is running."
Else
MsgBox "No, SAS is not running."
End If
End Sub

Function ISexeRunning(EXEName As String) As Boolean
Dim tf As Boolean
Dim oShell As Object, oWMI As Object, cApps As Object
Dim sQuery As String
Set oShell = CreateObject("Shell.Application")
Set oWMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")
sQuery = "Select Name From Win32_Process Where Name='" & EXEName & "'"
Set cApps = oWMI.ExecQuery(sQuery)
tf = cApps.Count > 0
Set cApps = Nothing
Set oWMI = Nothing
Set oShell = Nothing
ISexeRunning = tf
End Function[/VBA]

Quote:

---

Originally Posted by Kenneth Hobs

You can use something like this if you need to check if sas is running.

[vba]
Sub IsSASRunning()
If ISexeRunning("sas.exe") then
MsgBox "Yes, SAS is running."
Else
MsgBox "No, SAS is not running."
End If
End Sub

Function ISexeRunning(EXEName As String) As Boolean
Dim tf As Boolean
Dim oShell As Object, oWMI As Object, cApps As Object
Dim sQuery As String
Set oShell = CreateObject("Shell.Application")
Set oWMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")
sQuery = "Select Name From Win32_Process Where Name='" & EXEName & "'"
Set cApps = oWMI.ExecQuery(sQuery)
tf = cApps.Count > 0
Set cApps = Nothing
Set oWMI = Nothing
Set oShell = Nothing
ISexeRunning = tf
End Function[/vba]

---

Thanks Kenneth, I'm sure this function will come in handy when I start to develop this front end application further.

Really appreciate your help and insights into this query.

regards