Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 483
  • Last Modified:

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

0
danlein
Asked:
danlein
  • 11
  • 8
1 Solution
 
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
 
danleinAuthor Commented:
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.

 
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
 
danleinAuthor Commented:
Kindly close this question.  We have abandoned this for the time being.  I may reopen the question at another time.
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.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

  • 11
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now