Forum Discussion
Solved
Excel VBA closed file remaining in VBA Project Editor
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.
- 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
- For me this was happening only when VB Editor was opened, so it only really affects some of my debugging. Strange and frustrating, but nothing more. Of course if someone usually runs their scripts from VBE then this issue is much worse.
I had the same issue with Windows 10 and Excel 2016. This came up recently although I have been designing and using complex add-ins for many years. Every time I opened and closed an .xlsm file (without closing Excel), the code remained displayed in the VBE editor. Even worse, If I do that n times with the same .xlsm file, the VBE editor n times the same code. The end result is an "out of memory" error and a crash of Excel.
The solution was to uninstall Google Drive although I do not really understand how Google Drive interacts with the VBE editor. The problem is gone, and so is Google Drive unfortunately.
Uninstalling Google Drive was not straightforward because Uninstall attempts fail with a message telling to quit Google Drive first... and there is no 'Quit Google Drive' command. I had to end all Goggle Drive tasks with Task Manager to successfully uninstall it.
- 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?e=hBBVNe
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.
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
