After VBS script run on workbook a couple times, file becomes "locked for editing"

Hey.
I'm beating my head on this and wanted to take a step back.  I've written a script that opens a workbook, protects the sheet with a password, and saves it to another folder on a network share.  Fine and dandy the first time.  
When the script runs a second or third time, excel gives the fun error "locked for editing."  On the file server, the file isn't seen as open.
Any ideas???  Thanks in advance!!
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("\\Server\share on server\test.xls")
Set objWorksheet = objWorkbook.Worksheets(1)
 
objExcel.Visible = True
objExcel.DisplayAlerts = True
 
objWorksheet.Protect"password"
 
objWorkbook.SaveAs "\\Server\share on server\test-123.xls"
objExcel.Quit

Open in new window

LVL 3
danleinAsked:
Who is Participating?
 
danleinAuthor Commented:
Kindly close this question.  We have abandoned this for the time being.  I may reopen the question at another time.
0
 
DanielWillmottCommented:
If you issue a objWorkbook.Close after the SaveAs does that clear it up?
0
 
DanielWillmottCommented:
Maybe also add:
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
after the objExcel.Quit
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
danleinAuthor Commented:
Nope.. same thing.  It always seems to take a minute or 2 to kick in, but same result
0
 
DanielWillmottCommented:
If you remove the SaveAs does it still happen?
0
 
danleinAuthor Commented:
Tried that earlier as well.   Same thing.
0
 
danleinAuthor Commented:
Just a heads up.  This also occurs on the local machine as well as the  network share.
0
 
danleinAuthor Commented:
Also.. don't know if it makes a difference.  I know sometimes it does.  This box is running Excel 2000.
0
 
DanielWillmottCommented:
When you run it the 2nd, 3rd time, do you make sure the SaveAs target file does not exist? Excel will pause to ask you if it should over write the file. I think this is the problem.
0
 
danleinAuthor Commented:
Correct me if I'm wrong, but if the line objExcel.DisplayAlerts = False is set to "False" the default should be to overwrite it anyway.  
Also, I can't open the file either.  On opening the file, gives the same error.
0
 
DanielWillmottCommented:
I believe it is supposed to. But when I tried the code and left the target file in place, the code hung. When I removed the file it worked every time.
0
 
DanielWillmottCommented:
Yes. Change the objExcel.DisplayAlerts to False. That solves the problem.
0
 
danleinAuthor Commented:
OK.  I don't think that is the issue.  
I've started the code fresh on another machine with Office 2007, and the code works perfectly every time, and automatically overwrites the file.  I have done this about 10 times now.  The other machine would break after 2.

I have a suspicion that it is related to Office 2000.  I know some code needs to be changed to work on 2000.
0
 
danleinAuthor Commented:
I had changed it to false prior to my opening this question.  Tried all variables i could think of.  Local vs Share, true, false, waiting, looking at processes, etc.  
0
 
danleinAuthor Commented:
Any ideas or updates??  Still need to get this resolved.  Thanks
0
 
DanielWillmottCommented:
danlein,
I repeated this and still do not have the locking issue. The file is created everytime. The second I allow Excel to prompt (for over write, etc) I get the hang - of course. Were you able to figure this out?
I was thinking that maybe during your testing, your results were getting affected by previous failures, etc.
0
 
danleinAuthor Commented:
I have not gotten any resolution... still trying to tackle the issue.
I've made new files, changed paths, and still the issue occurs.  Are you running office 2000?  
Somehow, I believe the office version is the issue.  I believe this because I've had to rewrite some macros we use in 2007 because they don't work in 2000.  Wondering if the same issue occurs with vbscripts.  
Let me know your thoughts.  If it doesn't get resolved, I may have to close reopen this question in another area.
0
 
DanielWillmottCommented:
True - I'm using Office 2007. It's quite possible the script has a command Office 2000 doesn't like and it's waiting to show you the error, which it can't.
Can you run it as a .vbs script on the machine having the problem? (with the Visible property enabled?)
Something simple like this...

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.DisplayAlerts = True
Set objWorkbook = objExcel.Workbooks.Open("C:\test.xls")
objWorkbook.SaveAs "C:\test2.xls"
objWorkbook.Close
objExcel.Quit
Set objWorkbook = Nothing
Set objExcel = Nothing

Open in new window

0
 
danleinAuthor Commented:
nope.. Same result.  
Script works a few times.  I have to click yes to overwrite.  Then a few minutes later, says the file is locked.
Im stumped
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.