VBA to refresh workbook on network drive linked to the current workbook on SharePoint library

[UrosV]

Hello experts, this is my first post in this forum. I've searched a lot of posts over the last two days and just can't figure this out. Brief context of my problem:

• I built a Tableau report that uses Excel as datasource.
• The excel workbook is edited by a number of users and is stored in SharePoint document library.
• Theoretically Tableau can connect to excel on SharePoint but after a number of disappointments either Tableau or my company IT are stuffing me around.
• So I decided to go put an intermediary workbook (Tableau_Data_Feed) on a network drive (which Tableau can connect to), and link that workbook to the SharePoint workbook (Milestone_Tracker). Now I need a VBA code that would refresh Tableau_Data_Feed once Milestone_Tracker is saved to keep the data in Tableau_Feed up to date.
• The code below worked fine while testing both workbooks on a network drive (I edit Milestone_Tracker, click Save, code runs opens Tableau_Data_Feed, refreshes the data table, and closes the workbook).
• The problem is that once I move Milestone_Tracker to SharePoint, after the code runs both workbooks stay open, no error message comes up, Tableau_Feed workbook is refreshed but it doesn't get saved, and Milestone_Tracker now has a different name like 338E3728. I suspect this is the temporary file name it gets assigned because in order to edit the workbook from SharePoint it has to be opened locally and when saved it gets uploaded back to the server.

This code is saved under "ThisWorkbook" object. I tried After Save and On Close events and both result in the same. What's the best way to resolve this? I'm open to other suggestions as well. Thanks

I'm using Excel 2010 and I think the SharePoint 2013 (admin rights only for sharepoint library documents, cannot code or change setting in sharepoint).

Private Sub Workbook_AfterSave(ByVal Success As Boolean)


Dim Wkbk As Workbook
Dim SDrivePath As String
Dim FileName As String


SDrivePath = "…UNC _NETWORK\PATH\"
FileName = "Milestone_Tracker.xlsx"


Set Wkbk = Workbooks.Open(SDrivePath & FileName)


Wkbk.RefreshAll
DoEvents


Wkbk.Close (True)


End Sub

 

[UrosV]

Sorry, I just realized the file name listed in the code above should read "Tableau_Data_Feed". "Milestone_Tracker" is the file that holds this code and is stored in SharePoint library. Once this file is edited and saved it opens the "Tableau_Data_Feed" file on a network drive, refreshes the data table (linked to another tab in the file being edited, the "Milestone_Tracker") and is supposed to close and save the "Tableau_Data_Feed" file, but it doesn't do it. As stated above, everything works when both files are outside SharePoint (either both on the network drive, or one on my c drive and the other on the network drive). It just does not work once it's stored in SharePoint library.

Private Sub Workbook_AfterSave(ByVal Success As Boolean)


Dim Wkbk As Workbook
Dim SDrivePath As String
Dim FileName As String


SDrivePath = "…UNC _NETWORK\PATH\"
FileName = "Tableau_Data_Feed.xlsx"


Set Wkbk = Workbooks.Open(SDrivePath & FileName)


Wkbk.RefreshAll
DoEvents


Wkbk.Close (True)

 [COLOR=#333333]End Sub
[/COLOR]