Running Code on sharepoint

[Bagsy]

I have some excel workbooks with VBA to amongst other things run code “onopen event” to open a workbook with a database I use for data validation. I have been asked to upload these to sharepoint but how do I get the code to open the documents in excel rather than a web page, and how do I find the filepath to the actual excel document, rather than a path to a web page, so I can modify the filepath I set up to try it out.

Change this, which is the filepath I used to set it all up

       Set WB = Workbooks.Open("C:\Users\Gary Baker\Documents\Lion Engineering\W4G\W4G\Operational Data\_Databases\Customer List.xlsx", True, True)

To (this is just an example)

       Set WB = Workbooks.Open("Sharepoint:\Documents \Operational Data\_Databases\Customer List.xlsx", True, True)

I can manually open each file in the app on sharepoint and my data validation will work.

Can anybody please tell me if this is actually achievable

Many thanks

Gary

 

[severynm]

The general approach I have taken is to grab the username from excel and append that to the SharePoint path.

    Dim user As String
    user = Replace$(Application.UserName, " ", ".")
    openPath = "C:\Users\" & user & "\SharePointFolderStructure\Filename.xlsx"

With the caveats that a) everyone must have synced the SharePoint site at the same location (which they probably should have done anyways), and b) everyone's Office username must be in the same format "Firstname Lastname", which is easily changeable via File -> Options -> General. There's other ways you can get around point b (using Environ("username") ... see this post for the difference), but i've found the former to be cleaner for me, especially as where I'm at people's windows usernames are set up slightly differently depending on IT policy at the time period they were hired in.

 

[Bagsy]

The general approach I have taken is to grab the username from excel and append that to the SharePoint path.

    Dim user As String
    user = Replace$(Application.UserName, " ", ".")
    openPath = "C:\Users\" & user & "\SharePointFolderStructure\Filename.xlsx"

With the caveats that a) everyone must have synced the SharePoint site at the same location (which they probably should have done anyways), and b) everyone's Office username must be in the same format "Firstname Lastname", which is easily changeable via File -> Options -> General. There's other ways you can get around point b (using Environ("username") ... see this post for the difference), but i've found the former to be cleaner for me, especially as where I'm at people's windows usernames are set up slightly differently depending on IT policy at the time period they were hired in.

Thanks for that very helpful