Finding UID and WSID

[komark]

Hi,
I am doing a VBA code in Excel to run a Stored Procedure from SQL database.
The uid (user id) and wsid (workstation id) are specific to my username and my workstation.
How do I set them up as variables so other ppl can run it.

Here is portion of my code:

___________________________________________________________
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DRIVER=SQL Server;SERVER=A;UID=B;APP=Microsoft Office 2003;WSID=C;DATABASE=D;Trusted_Connectio" _
), Array("n=Yes")), Destination:=Range("C8"))
.CommandText = Array("dbo.vlCTincidentsRPTS @selectRPT = '' " + Range("Sheet2!A1").Value + StrCellValue + Range("Sheet2!A1").Value)
___________________________________________________________

Thanks in advance!
Omar

 

[PHV]

You wanted Environ("USERNAME") and Environ("COMPUTERNAME") ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[komark]

PH,
Thank you soo much for your help

It works!!

-Omar

 

[komark]

Hi PH,
Got stuck again...I am wanting to pass a variable to the SQL end for my Stored Procedure

The variable in my case is StrCellValue. How do I pass this w/o sending the whole dB info w/ it?

-Omar
___________________________________________________________

strCellValue = Range("G3").Value
strCellValue = "vlCTincidentsRPTS " _
+ Range("Sheet2!A1").Value + Range("Sheet1!G3").Value _
+ "%" + Range("Sheet2!A1").Value


With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DRIVER=SQL Server;SERVER=A;UID=Environ(USERNAME);APP=Microsoft Office 2003;WSID=Environ(COMPUTERNAME);DATABASE=D;Trusted_Connectio" _
), Array("n=Yes")), Destination:=Range("C8"))
.CommandText = Array(strCellValue)
.Name = "Query from Howler"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
___________________________________________________________

 

[RonRepp]

Dim WSID as String
Dim UID as String
WSID= Environ("COMPUTERNAME") 
UID=Environ("USERNAME") 

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DRIVER=SQL Server;SERVER=A;[b]UID=" & UID & [/b]"APP=Microsoft Office 2003;[b]WSID=" & UID & "[/b];DATABASE=D;Trusted_Connection" _
        ), Array("n=Yes")), Destination:=Range("C8"))
        .CommandText = Array(strCellValue)
        .Name = "Query from Howler"
        .FieldNames = False
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With

I hope this helps.


Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[komark]

Thank you for your reply Ron.

But I am trying to send a variable to the sql end (stored procedure)