Solved

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

Posted on 2009-04-14
19
415 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

821 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