Solved

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

Posted on 2009-04-14
19
409 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
Comment Utility
If you issue a objWorkbook.Close after the SaveAs does that clear it up?
0
 
LVL 6

Expert Comment

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

Author Comment

by:danlein
Comment Utility
Nope.. same thing.  It always seems to take a minute or 2 to kick in, but same result
0
 
LVL 6

Expert Comment

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

Author Comment

by:danlein
Comment Utility
Tried that earlier as well.   Same thing.
0
 
LVL 3

Author Comment

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

Author Comment

by:danlein
Comment Utility
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
Comment Utility
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
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 6

Expert Comment

by:DanielWillmott
Comment Utility
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
Comment Utility
Yes. Change the objExcel.DisplayAlerts to False. That solves the problem.
0
 
LVL 3

Author Comment

by:danlein
Comment Utility
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
Comment Utility
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
Comment Utility
Any ideas or updates??  Still need to get this resolved.  Thanks
0
 
LVL 6

Expert Comment

by:DanielWillmott
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Kindly close this question.  We have abandoned this for the time being.  I may reopen the question at another time.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now