Excel Macro problems when working on sharepoint folders

%3CLINGO-SUB%20id%3D%22lingo-sub-1946591%22%20slang%3D%22en-US%22%3EExcel%20Macro%20problems%20when%20working%20on%20sharepoint%20folders%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1946591%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20using%20Excel%20VBA%20to%20automate%20some%20processes%20with%20a%20synced%20local%20copy%20of%20sharepoint%20folders.%26nbsp%3B%20Here%20are%20two%20annoying%20things%20I%20am%20having.%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20The%20macro%20will%20open%20excel%20files%20and%20make%20some%20changes.%20Then%20save%20and%20close%20the%20file.%26nbsp%3B%20%26nbsp%3BBut%20the%20macro%20will%20stop%20randomly%20showing%20%22Document%20not%20saved%22%20error.%26nbsp%3B%20After%20I%20click%20debug%20and%20continue%2C%20the%20macro%20can%20resume%20working.%26nbsp%3B%20%26nbsp%3BThe%20internet%20is%20working%20great%20with%20no%20interruptions.%26nbsp%3B%20I%20am%20not%20sure%20why%20this%20happens.%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20code%20where%20the%20error%20happens.%26nbsp%3B%3C%2FP%3E%3CP%3EWorkbooks(CurFileName).Close%20True%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2.%20After%20I%20processed%20all%20documents%2C%20I%20will%20have%20to%20move%20folders%20to%20the%20processed%20folder.%26nbsp%3B%20Even%20I%20closed%20all%20documents%20and%20windows%20and%20wait%20over%2015%20minutes.%26nbsp%3B%20I%20still%20get%20errors%20like%20%22Permission%20denied%22.%26nbsp%3B%20I%20have%20to%20restart%20my%20computer%20to%20get%20the%20folder%20lock%20removed.%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20code%20where%20the%20error%20happens.%26nbsp%3B%3C%2FP%3E%3CP%3ESet%20objFSO%20%3D%20CreateObject(%22Scripting.FileSystemObject%22)%3C%2FP%3E%3CP%3EobjFSO.MoveFolder%20MyOldPath%20%26amp%3B%20CurFolder%2C%20MyPath%20%26amp%3B%20CurFolder%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help%20me%20with%20these%20annoying%20mistakes!%26nbsp%3B%20Thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1946591%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharPoint%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

I am using Excel VBA to automate some processes with a synced local copy of sharepoint folders.  Here are two annoying things I am having. 

1. The macro will open excel files and make some changes. Then save and close the file.   But the macro will stop randomly showing "Document not saved" error.  After I click debug and continue, the macro can resume working.   The internet is working great with no interruptions.  I am not sure why this happens. 

Here is the code where the error happens. 

Workbooks(CurFileName).Close True 

 

2. After I processed all documents, I will have to move folders to the processed folder.  Even I closed all documents and windows and wait over 15 minutes.  I still get errors like "Permission denied".  I have to restart my computer to get the folder lock removed.   

Here is the code where the error happens. 

Set objFSO = CreateObject("Scripting.FileSystemObject")

objFSO.MoveFolder MyOldPath & CurFolder, MyPath & CurFolder

 

Please help me with these annoying mistakes!  Thanks

0 Replies