Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.datamap
|
|||
|
|||
Query based on logged on user (Newbie)
I'm pulling data into excel from a SQL database. Rather than create several
identical workbooks where each queries a different users information, can I add some code at some level that matches the [User_Name] field in the data to the Windows logged in user name on the computer. Both are first intial + last name. The one workbook would have several different data sources (different SQL Views from the same database) and each would refresh automatically upon open. If I can, is there some simple code anyone could offer me on this? Thanks for any direction at all! Tim |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel.datamap
|
|||
|
|||
Query based on logged on user (Newbie)
Hi Tim,
Tim Miller wrote: I'm pulling data into excel from a SQL database. Rather than create several identical workbooks where each queries a different users information, can I add some code at some level that matches the [User_Name] field in the data to the Windows logged in user name on the computer. Both are first intial + last name. The one workbook would have several different data sources (different SQL Views from the same database) and each would refresh automatically upon open. If you're using SQL Server, there is an suser_sname() function that will return the current system user. If your query is using integrated authentication (network credentials used instead of SQL Server login), then you could add WHERE [User_Name] = suser_sname() to your queries in order to filter by the connecting user. -- Regards, Jake Marx www.longhead.com [please keep replies in the newsgroup - email address unmonitored] |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.datamap
|
|||
|
|||
Query based on logged on user (Newbie)
Thanks Jake,
That IS very useful. Unfortunately however, I log into it via a SQL Server logon. My application that uses the database needs it that way (although I'd like to change that). Any other options? It makes sense to me that some code in the excel worksheets could do this, where it applied the appropriate filter programatically on open but before refresh. Sounds good when I say it, huh! Unfortunately I don't have a clue. Tim "Jake Marx" wrote in message ... Hi Tim, Tim Miller wrote: I'm pulling data into excel from a SQL database. Rather than create several identical workbooks where each queries a different users information, can I add some code at some level that matches the [User_Name] field in the data to the Windows logged in user name on the computer. Both are first intial + last name. The one workbook would have several different data sources (different SQL Views from the same database) and each would refresh automatically upon open. If you're using SQL Server, there is an suser_sname() function that will return the current system user. If your query is using integrated authentication (network credentials used instead of SQL Server login), then you could add WHERE [User_Name] = suser_sname() to your queries in order to filter by the connecting user. -- Regards, Jake Marx www.longhead.com [please keep replies in the newsgroup - email address unmonitored] |
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel.datamap
|
|||
|
|||
Query based on logged on user (Newbie)
Hi Tim,
OK - that does make things a bit trickier, but you should still be able to get it to work. You can get the current user's network id with this: Debug.Print Environ("Username") This should work in most situations, but I prefer to use the API directly: Public Declare Function GetUserName Lib "advapi32.dll" Alias _ "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long Public Function sCurrentUser() As String Dim sUserNm As String * 256 Dim nActualLen As Integer On Error GoTo ErrHandler If GetUserName(sUserNm, 256) Then nActualLen = InStr(sUserNm, vbNullChar) - 1 If nActualLen 0 Then sCurrentUser = Left$(sUserNm, nActualLen) Else sCurrentUser = sUserNm End If End If ExitRoutine: Exit Function ErrHandler: Resume ExitRoutine End Function Once you have the user's network id, you should be able to add it to your WHERE clause on the client side in order to filter your results by user. -- Regards, Jake Marx www.longhead.com [please keep replies in the newsgroup - email address unmonitored] Tim Miller wrote: Thanks Jake, That IS very useful. Unfortunately however, I log into it via a SQL Server logon. My application that uses the database needs it that way (although I'd like to change that). Any other options? It makes sense to me that some code in the excel worksheets could do this, where it applied the appropriate filter programatically on open but before refresh. Sounds good when I say it, huh! Unfortunately I don't have a clue. Tim "Jake Marx" wrote in message ... Hi Tim, Tim Miller wrote: I'm pulling data into excel from a SQL database. Rather than create several identical workbooks where each queries a different users information, can I add some code at some level that matches the [User_Name] field in the data to the Windows logged in user name on the computer. Both are first intial + last name. The one workbook would have several different data sources (different SQL Views from the same database) and each would refresh automatically upon open. If you're using SQL Server, there is an suser_sname() function that will return the current system user. If your query is using integrated authentication (network credentials used instead of SQL Server login), then you could add WHERE [User_Name] = suser_sname() to your queries in order to filter by the connecting user. -- Regards, Jake Marx www.longhead.com [please keep replies in the newsgroup - email address unmonitored] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting Logged in User Name in formula... | Excel Discussion (Misc queries) | |||
Getting Logged in User Name in formula... | Excel Worksheet Functions | |||
Auto Updating the Logged in User | Excel Discussion (Misc queries) | |||
How do you reference the logged on user name in excel? | Excel Discussion (Misc queries) | |||
Find Out Logged in User | Excel Programming |