Sharepoint Copy

[Takes2ToTango]

Hi,

Is there a way to get VBA/Excel to close any active copies of a sharepoint file?

My scenario is that a laptop will have a sharepoint copy of the excel file open. However, every 30 mins I want my master copy to open, close sharepoint, update all queries and then reopen the sharepoint.

Is this possible?

 

[Tupe77]

I don't have a sharepoint environment available and my experience with sharepoint with Excel is very limited.
So if the problem concerns the sharepoint environment, I'm the wrong person to help.

If the question mainly concerns the processing of workbooks, then I can try to help.


My apologies for any quirks, English is not my native language. "So I blame Google translate for all my goofs."

 

[Tupe77]

I don't know if this is of any use, but here is an outline of how to identify, close and reopen workbooks.

Sub TS_WorkbookClosing()
Dim wbMaster As Workbook, wbi As Workbook
Dim SharePointFiles As New Collection, SharePointFile As Variant
Dim PartOfSharePointPath As String
PartOfSharePointPath = "somethin to here"               ' Put something here that is common to closed sharepoint file paths.
'Set wbMaster = Workbooks.Open("Full path and filename")' Open master workbook

For Each wbi In Workbooks                               ' Go through all open workbooks
    Debug.Print wbi.FullName                            ' print path&name
    
    If InStr(LCase(wbi.FullName), LCase(PartOfSharePointPath)) Then   ' close those workbooks which path or filename contains the string PartOfSharePointPath.
        SharePointFiles.Add wbi.FullName                ' Save Workbook path & name to collection
        wbi.Close SaveChanges:=False                    ' Close Workbook
    End If
    
Next

Application.Wait (Now + TimeValue("0:00:10"))           ' Waiting 10 seconds

For Each SharePointFile In SharePointFiles
    Workbooks.Open SharePointFile                       ' Opening previously closed workbooks
Next SharePointFile

End Sub

My apologies for any quirks, English is not my native language. "So I blame Google translate for all my goofs."

 

[Takes2ToTango]

Many thanks, Ill give this a try and let you know if it works!