?
Solved

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

Posted on 2009-04-14
19
Medium Priority
?
439 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
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…

764 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