SOLVED

Excel VBA closed file remaining in VBA Project Editor

%3CLINGO-SUB%20id%3D%22lingo-sub-2197050%22%20slang%3D%22en-US%22%3EExcel%20VBA%20closed%20file%20remaining%20in%20VBA%20Project%20Editor%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2197050%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20morning%20to%20all.%3C%2FP%3E%3CP%3EI%20am%20new%20to%20this%20community%20and%20turning%20to%20you%20for%20some%20help%20on%20a%20topic%20that%20I%20have%20seen%20posted%20in%20the%20past%20but%20have%20not%20found%20a%20proper%20solution%20for%20yet%3A%3C%2FP%3E%3CP%3EI%20developed%20code%20for%20my%20client%20who%20uses%20SharePoint%20to%20share%20files%20within%20their%20team.%20I%20have%20a%20problem%20with%20files%20%22hanging%22%20or%20%22ghosting%22%20in%20the%20VBA%20Project%20Explorer%20window%20after%20close%20event.%3C%2FP%3E%3CP%3EThe%20simple%20code%20('set%20wkbk%20%3D%20nothing')%20will%20eliminate%20the%20problem%20on%20my%20PC%3B%20however%2C%20this%20will%20not%20be%20the%20case%20for%20my%20client's%20VBA%20Project%20Editor%3B%20eventually%2C%20this%20creates%20multiple%20replicas%20of%20the%20same%20file%20names%20eventually%20leading%20to%20issues%20and%20even%20crashes.%20We%20are%20running%20on%20MS%20Office%20365%20(PC).%20I%20believe%20the%20issue%20may%20be%20related%20to%20SharePoint.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EOption%20Explicit%0A%0ASub%20TestOpenWbk()%0ADim%20WkBk%20As%20Workbook%0A%0ASet%20WkBk%20%3D%20Workbooks.Open(%22C%3A%5CUserName%5CFilePath%5CFileName.xlsx%22)%0AWkBk.Close%20savechanges%3A%3DFalse%0ASet%20WkBk%20%3D%20Nothing%20'removes%20the%20file%20from%20the%20project%20editor%20on%20my%20PC%20but%20not%20on%20client%0A%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20insights%20are%20truly%20welcome.%3C%2FP%3E%3CP%3EThank%20you%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2197050%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2199233%22%20slang%3D%22de-DE%22%3ESubject%3A%20Excel%20VBA%20closed%20file%20remaining%20in%20VBA%20Project%20Editor%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2199233%22%20slang%3D%22de-DE%22%3ETry%20this%20VBA%20code%2C%20so%20you%20could%20call%20up%20the%20file%20in%20the%20browser%20and%20edit%20it%20(so%20the%20theory%20-%20I%20haven't%20tried%20it).%3CBR%20%2F%3ESet%20wshshell%20%3D%20CreateObject(%22WScript.Shell%22)%20%3CBR%20%2F%3E%20wshshell.Run%20%3CA%20href%3D%22https%3A%2F%2Ffirma.sharepoint.com%2F%3Ax%3A%2Fs%2FCutRedTape-EES2019%2FEbmmNX5qGehEv5Z2ROk5my8BeZ4DlQ039JaYsEltZcUIqA%3Fe%3DhBBVNe%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Ffirma.sharepoint.com%2F%3Ax%3A%2Fs%2FCutRedTape-EES2019%2FEbmmNX5qGehEv5Z2ROk5my8BeZ4DlQ039JaYsEltZcUIqA%3Fe%3DhBBVNe%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3ESolution%20proposal%20without%20guarantee%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%20Nikolino%20I%20know%20I%20%3CBR%20%2F%3E%20don't%20know%20anything%20(Socrates)%3C%2FLINGO-BODY%3E
New Contributor

Good morning to all.

I am new to this community and turning to you for some help on a topic that I have seen posted in the past but have not found a proper solution for yet:

I developed code for my client who uses SharePoint to share files within their team. I have a problem with files "hanging" or "ghosting" in the VBA Project Explorer window after close event.

The simple code ('set wkbk = nothing') will eliminate the problem on my PC; however, this will not be the case for my client's VBA Project Editor; eventually, this creates multiple replicas of the same file names eventually leading to issues and even crashes. We are running on MS Office 365 (PC). I believe the issue may be related to SharePoint.

 

 

Option Explicit

Sub TestOpenWbk()
Dim WkBk As Workbook

Set WkBk = Workbooks.Open("C:\UserName\FilePath\FileName.xlsx")
WkBk.Close savechanges:=False
Set WkBk = Nothing 'removes the file from the project editor on my PC but not on client

End Sub

 

Any insights are truly welcome.

Thank you in advance.

 

5 Replies
Try this VBA code, so you could call up the file in the browser and edit it (so the theory - I haven't tried it).
Set wshshell = CreateObject("WScript.Shell")
wshshell.Run https://firma.sharepoint.com/:x:/s/CutRedTape-EES2019/EbmmNX5qGehEv5Z2ROk5my8BeZ4DlQ039JaYsEltZcUIqA...

Solution proposal without guarantee


Nikolino
I know I don't know anything (Socrates)
Thank you Nikolino. I will see if someone else has an answer. I am looking at solving this within Excel VBA. Your link is invalid.

@YvesAustin 

It's not a link, the editor probably converted it to a link automatically.

You have to enter the address of your SharePoint.

 

Set wshshell = CreateObject("WScript.Shell")
wshshell.Run https://firma.sharepoint.com/:x:/s/CutRedTape-EES2019/EbmmNX5qGehEv5Z2ROk5my8BeZ4DlQ039JaYsEltZcUIqA?e=hBBVNe

 

@Nikolino 

Here is another code fresh from the internet, untested.

 

 

Sub Analyse()
Dim arrFilenames As Variant
Dim wbkArr As Workbook
Dim wbkBasis As Workbook
Set wbkBasis = ActiveWorkbook

Selection:
    ' Zu öffnende Dateien erfragen
    arrFilenames = Application.GetOpenFilename( _
        "Excelfiles (*.xlsm), *.xlsm, All Files (*.*), *.*", 1, _
        "Select Excel files...", MultiSelect:=True)
        'Add selected files to an array field
    If VarType(arrFilenames) = vbBoolean Then
        If MsgBox("No files were selected. Do you want to exit the Makro?", vbYesNo, "Exit?") =  _
 _
vbNo Then
            GoTo Selection
        Else
            Set wbkBasis = Nothing
            Exit Sub
        End If
    End If
    
    Application.ScreenUpdating = False
    'Hide Makro activity to gain speed

    For i = 1 To UBound(arrFilenames) ' Durchläuft die Anzahl der Dateien
        'Wenn Datei noch nicht geöffnet
        'If FileOpenYet(Dir$(arrFilenames(i))) = False Then
        If FileOpenYet(Parse_Resource(arrFilenames(i))) = False Then
            'dann öffnen
            'Workbooks.Open FileName:=arrFilenames(i)
            Workbooks.Open FileName:=Parse_Resource(arrFilenames(i))
        Else
            'oder Aktivieren
            Workbooks(arrFilenames(i)).Activate
        End If
        Set wbkArr = ActiveWorkbook
        '-------------------------------------------------------
        'hier kommt dann der Code rein, der die ausgewählten Dateien
        'betrifft. Die Ursprungsdatei ist über wbkBasis ansprechbar.
        wkbbasis.Worksheets(1).Cells(i, 1) = wbkArr.Worksheets(1).Range("F32")
        wkbbasis.Worksheets(1).Cells(i, 2) = wbkArr.Name
        wbkArr.Close savechanges:=False    'Datei schließen
        Set wbkArr = Nothing
        '-------------------------------------------------------
    Next i
    Set wbkArr = Nothing
    wbkBasis.Activate
    Set wbkBasis = Nothing
    Application.ScreenUpdating = True
End Sub

Function FileOpenYet(FileName As String) As Boolean
'eine Funktion, die Prüft ob eine Datei schon geöffnet ist.
    Dim s As String
    On Error GoTo Nonexistent
    s = Workbooks(FileName).Name
    FileOpenYet = True
    Exit Function
Nonexistent:
    FileOpenYet = False
End Function


Public Function Parse_Resource(URL As String)
'Uncomment the below line to test locally without calling the function & remove argument above
'Dim URL As String
Dim SplitURL() As String
Dim i As Integer
Dim WebDAVURI As String

'Check for a double forward slash in the resource path. This will indicate a URL
If Not InStr(1, URL, "//", vbBinaryCompare) = 0 Then

    'Split the URL into an array so it can be analyzed & reused
    SplitURL = Split(URL, "/", , vbBinaryCompare)

    'URL has been found so prep the WebDAVURI string
    WebDAVURI = "\\"

    'Check if the URL is secure
    If SplitURL(0) = "https:" Then
        'The code iterates through the array excluding unneeded components of the URL
        For i = 0 To UBound(SplitURL)
            If Not SplitURL(i) = "" Then
                Select Case i
                    Case 0
                    'Do nothing because we do not need the HTTPS element
                    Case 1
                    'Do nothing because this array slot is empty
                    Case 2
                    'This should be the root URL of the site. Add @ssl to the WebDAVURI
                        WebDAVURI = WebDAVURI & SplitURL(i) & "@ssl"
                    Case Else
                        'Append URI components and build string
                        WebDAVURI = WebDAVURI & "\" & SplitURL(i)
                End Select
            End If
        Next i
    Else
    'URL is not secure
        For i = 0 To UBound(SplitURL)
            'The code iterates through the array excluding unneeded components of the URL
            If Not SplitURL(i) = "" Then
                Select Case i
                    Case 0
                        'Do nothing because we do not need the HTTPS element
                    Case 1
                        'Do nothing because this array slot is empty
                    Case 2
                    'This should be the root URL of the site. Does not require an additional  _
slash
                        WebDAVURI = WebDAVURI & SplitURL(i)
                    Case Else
                        'Append URI components and build string
                    WebDAVURI = WebDAVURI & "\" & SplitURL(i)
                End Select
            End If
        Next i
    End If
    'Set the Parse_Resource value to WebDAVURI
    Parse_Resource = WebDAVURI
Else
'There was no double forward slash so return system path as is
    Parse_Resource = URL
End If

End Function

 

If the code gets stuck, put the parameters in brackets so that you force the conversion from the variant to a string.

 

Thank you for your understanding and patience

 

 

Nikolino

I know I don't know anything (Socrates)

 

best response confirmed by YvesAustin (New Contributor)
Solution
Nikolino, I truly appreciate the time you spent on finding this solution for me. Thank you. I am not sure I understand the proposed solution and where/how/why it would work. I do not have SharePoint myself and am trying to see if it is possible to create a code (or a way to write code/ declare variables) that would remove these "hanging" files (again these files are closed, but remain accessible in the VBA Project Editor). Best. Yves