Solved

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

Posted on 2009-04-14
19
414 Views
Last Modified: 2012-08-14
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

0
Comment
Question by:danlein
  • 11
  • 8
19 Comments
 
LVL 6

Expert Comment

by:DanielWillmott
ID: 24143535
If you issue a objWorkbook.Close after the SaveAs does that clear it up?
0
 
LVL 6

Expert Comment

by:DanielWillmott
ID: 24143562
Maybe also add:
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
after the objExcel.Quit
0
 
LVL 3

Author Comment

by:danlein
ID: 24143624
Nope.. same thing.  It always seems to take a minute or 2 to kick in, but same result
0
ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

 
LVL 6

Expert Comment

by:DanielWillmott
ID: 24143653
If you remove the SaveAs does it still happen?
0
 
LVL 3

Author Comment

by:danlein
ID: 24143752
Tried that earlier as well.   Same thing.
0
 
LVL 3

Author Comment

by:danlein
ID: 24143784
Just a heads up.  This also occurs on the local machine as well as the  network share.
0
 
LVL 3

Author Comment

by:danlein
ID: 24143790
Also.. don't know if it makes a difference.  I know sometimes it does.  This box is running Excel 2000.
0
 
LVL 6

Expert Comment

by:DanielWillmott
ID: 24143803
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
 
LVL 3

Author Comment

by:danlein
ID: 24144040
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
 
LVL 6

Expert Comment

by:DanielWillmott
ID: 24144103
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
 
LVL 6

Expert Comment

by:DanielWillmott
ID: 24144120
Yes. Change the objExcel.DisplayAlerts to False. That solves the problem.
0
 
LVL 3

Author Comment

by:danlein
ID: 24144131
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
 
LVL 3

Author Comment

by:danlein
ID: 24144136
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
 
LVL 3

Author Comment

by:danlein
ID: 24163010
Any ideas or updates??  Still need to get this resolved.  Thanks
0
 
LVL 6

Expert Comment

by:DanielWillmott
ID: 24184713
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
 
LVL 3

Author Comment

by:danlein
ID: 24184900
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
 
LVL 6

Expert Comment

by:DanielWillmott
ID: 24185115
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
 
LVL 3

Author Comment

by:danlein
ID: 24188140
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
 
LVL 3

Accepted Solution

by:
danlein earned 0 total points
ID: 24280737
Kindly close this question.  We have abandoned this for the time being.  I may reopen the question at another time.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question