-1

I have a spreadsheet (macro enabled ) mounted on a sharepoint for multiple user access in centralized location.

However the macro doesn't get kicked off when opening the excel online,but the workaround for this is to open in excel mode which made the macro execute.

can anyone help me with a code to find the logged in user of sharepoint as soon as the sheet is opened?

I can add that to the code in the workbook open activity.

I tried using the Environ$("Username"),but this is not showing me the value that I am expecting as this shows the username of my laptop than the user logged in on the sharepoint.

Basically no matter what the login of the machine where the sharepoint is used,i would like to get the sharepoint logged in user name which could be a different(like my scenario)?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
user2075017
  • 457
  • 4
  • 11
  • 23
  • Show us your code. Also read https://stackoverflow.com/help/mcve –  Feb 12 '19 at 17:33
  • @peakpeak there is no specific code..I have a sharepoint site in my office and a spreadsheet which is uploaded..All I have used is to give a popup message as soon as the sheet is opened in excel..and the code consists of `msgbox(Environ$("Username"))` – user2075017 Feb 12 '19 at 17:39
  • any help please ? – user2075017 Feb 13 '19 at 06:35
  • Instead of `Environ("Username")` try `Application.Username` – horst Feb 13 '19 at 07:58
  • @horst tried that too..with no luck..both `Environ("Username")` and `Application.Username` gives same result which is my laptop name but I am looking for the sharepoint logged in user name – user2075017 Feb 13 '19 at 08:05
  • `Environ("Username")` and `Application.Username` will only return the value of the user that has the workbook itself open, so when someone other than you opens the workbook their username will be picked up by either of those events. Why do you need to have the user see a message of their username in a message box when they open the file? – Zack E Feb 13 '19 at 13:49
  • @ZackE the requirement is basically to get the user name from share point through which excel is opened so that I can write some access levels in worksheets access based on user id in later stages of opening the excel – user2075017 Feb 13 '19 at 16:42
  • Ahh ok. I would just export the user list from SharePoint to that Excel File on a hidden sheet. This would allow you to see all users in that SharePoint Site and then build your access levels around that by assigning user groups as variable(s) within excel based on `Environ("Username")`. – Zack E Feb 13 '19 at 17:49
  • @ZackE . No no..ok let me explain what it is.. there is a separate excel with macro in it.. uploaded on sharepoint site..when you click the file on sharepoint excel enable wouldn't allow it to run the macro..so we have to use an option open in excel in sharepoint so that the macro works..but however,i would like to add a access rules in the excel macro itself with the input from the sharepoint site capturing the sharepoint user name...PS: SharePoint site user name is different from the system user name as system name is what is configured.. – user2075017 Feb 13 '19 at 17:53
  • @ZackE,@horst,@peakpeak - I think I have figured out the issue and got a solution.I just analysed a bit and observed the excel thoroughly by myself..Each time I open the excel from sharepoint the last author is being updated with the user name that the sharepoint is accessed through..So tried capturing the last author and this solved the purpose.Thanks to all of you for shedding some light on this.. – user2075017 Feb 13 '19 at 19:08

1 Answers1

0

The logged in user from sharepoint can be captured by printing the last Author of the spreadsheet.

The last author is updated with the last logged in user id no matter whether it is from sharepoint or any app.

msbox(ActiveWorkbook.BuiltinDocumentProperties("Last Author"))

I hope this solution helps everyone who is facing the similar kind of issues.

Happy learning..

user2075017
  • 457
  • 4
  • 11
  • 23
  • 1
    Glad you found a solution :). I was also going to say you could also try to grab the Username in SP from the User table by connecting directly to SharePoint via the Data Tab > From Other Sources in Excel and using that connection string and record set in your VBE code. This would require SQL strings though. – Zack E Feb 13 '19 at 19:20