locked
Can't delete file after renaming due to Word file handle still present. Error staes document is still in use but it's really not. Worked fine in 2007 but not in 2010. RRS feed

  • Question

  • I have some code associated with a Word Template. The template is opened, filled out and saved via a routing in vba. This all works fine in older 2007 version (which is version it was originally created in). Once this file is saved, the user can change the date in a text box and re-save document. The code saves using the new date for part of new file name and then deletes that older file from the server. It fails at the delete function because the newer version of word is not dropping the file handle from the first named file when user saves it to the new filename. In older version (2007) this handle was released as soon as file was saved to a different name. I can't seem to figure out how to release the old document filename so the old document file can be deleted.

    I hope I explained this well enough.

    Here's the code that woeked in version 2007 but fails in version 2010.

    Option Explicit

    Dim CAPEX01 As MSForms.ComboBox
    Dim CAPEX02 As MSForms.ComboBox
    Dim LocalPath As String
    Dim NetPath As String
    Dim OldPath As String
    Dim OldPathNet As String
    Dim DocName01 As String
    Dim DocName02 As String
    Dim DocName03 As String
    Dim DocName04 As String
    Dim DocName As String
    Dim DocNameold As String
    Dim TestDocName As String
    Dim filesys
    Dim newfolder

    Sub AutoOpen()
    ActiveDocument.ActiveWindow.View.Type = wdPrintView
    TestDocName = ActiveDocument.TextBox2
    OldPathNet = "\\yourPath\" & TestDocName & "\"
    End Sub


    Sub AutoNew()
    TestDocName = ActiveDocument.TextBox2
    OldPathNet = "\\yourPath\" & TestDocName & "\"
     ComboBox1.Locked = False
     ComboBox1.Enabled = True
     FillList1
     FillList2
     End Sub


    Sub DeleteOldDoc()
    OldPathNet = "\\yourPath\" & TestDocName ' & "\"
    DocNameold = OldPathNet & TestDocName & "-" & DocName02 & "-" & DocName03 & "-" & DocName04 & ".doc"

        If Not TestDocName = DocName01 Then
            Set filesys = CreateObject("Scripting.FileSystemObject")
        If filesys.FileExists(DocNameold) Then
            filesys.DeleteFile (DocNameold), True        'I get file permission error here
        End If
        End If
    If DocName01 <> "" Then
    If Not TestDocName = DocName01 Then
    If Not TestDocName = "" Then
        MsgBox "Project Proposal Has Been Moved From Year " & TestDocName & " To " & DocName01 & ""
    End If
    End If
    End If
    TestDocName = DocName01
    End Sub


    '''''''Document path functions''''''
    Sub chkpath()
    Set filesys = CreateObject("Scripting.FileSystemObject")
    If Not filesys.FolderExists("\\yourPath\") Then
       newfolder = filesys.CreateFolder("\\yourPath\")
    End If
    If Not filesys.FolderExists("\\yourPath\" & DocName01 & "\") Then
        newfolder = filesys.CreateFolder("\\yourPath\" & DocName01 & "\")
    End If
    End Sub

     

    ''''''Save Function''''''
    Private Sub CommandButton1_Click()
    DocName01 = ActiveDocument.TextBox2
    DocName02 = ActiveDocument.TextBox4
    DocName03 = ActiveDocument.TextBox1
    DocName04 = ActiveDocument.ComboBox1.Value
    chkpath
    NetPath = "\\yourPath\" & DocName01 & "\"
    DocName = NetPath & DocName01 & "-" & DocName02 & "-" & DocName03 & "-" & DocName04

    ActiveDocument.SaveAs2 FileName:=DocName, FileFormat:=wdFormatDocument
             
     ComboBox1.Locked = True
     ComboBox1.Enabled = False
     ComboBox2.Locked = True
     ComboBox2.Enabled = False
     TextBox1.Locked = True
     TextBox1.Enabled = False
     TextBox3.Locked = True
     TextBox3.Enabled = False
     TextBox4.Locked = True
     TextBox4.Enabled = False
     DeleteOldDoc
    End Sub

    Sub FillList1()
    Set CAPEX02 = ActiveDocument.ComboBox2
      With CAPEX02
          .AddItem "CASTING", 0
          .AddItem "HOT ROLLING", 1
          .AddItem "COLD ROLLING", 2
          .AddItem "FINISHING", 3
          .AddItem "PLANT GENERAL", 4
          .AddItem "MOBILE EQUIPMENT", 5
      End With
    End Sub
     
     Sub FillList2()
     Set CAPEX01 = ActiveDocument.ComboBox1
      With CAPEX01
          .AddItem "A Name", 0
          .AddItem "Another Name", 1

      End With
    End Sub

    Private Sub CommandButton2_Click()
        UserForm1.Show
    End Sub

    Wednesday, July 4, 2012 6:01 AM

All replies

  • Try to simply your code so that the part that is causing the error is left.

    Thursday, July 5, 2012 2:15 AM
  • Sub DeleteOldDoc()
    OldPathNet = "\\yourPath\" & TestDocName ' & "\"
    DocNameold = OldPathNet & TestDocName & "-" & DocName02 & "-" & DocName03 & "-" & DocName04 & ".doc"

        If Not TestDocName = DocName01 Then
            Set filesys = CreateObject("Scripting.FileSystemObject")
        If filesys.FileExists(DocNameold) Then
            filesys.DeleteFile (DocNameold), True        'I get file permission error here
        End If
        End If
    If DocName01 <> "" Then
    If Not TestDocName = DocName01 Then
    If Not TestDocName = "" Then
        MsgBox "Project Proposal Has Been Moved From Year " & TestDocName & " To " & DocName01 & ""
    End If
    End If
    End If
    TestDocName = DocName01
    End Sub

    This complete code above checks to see if the file name field on a form has changed and if so to save it as the new file (saves command) and then to keep the Folders clean, goes back and deletes the original filenmae from original folder 

    As highlighted in BOLD in snippet above, I get the file permission error stating file is in use. As my original thread mentioned this is due to the ~$filename.doc still being present in the working folder of the file I just "savedas" a new name. This file handle or lock is keeping my code from deleting the file that was renamed. Word still is holding on to it. THis code worked fine in 2007 and it appears to me that the file was released once the file was renamed.

    So to sum up, in 2007 Word, I was able to run this code without errors. In 2010 Word I get the file permission error due to file still being locked by Word even after it's been renamed. I need to know how to programatically release the file and then delete it even though Word still has a hold on it.

    Thanks,

    Thursday, July 5, 2012 4:08 AM
  • Anyone! Please!

    Has anyone been able to solve this? I really need to be able to delete the old file after renaming it to a new "saveas" filename.

    Thanks,

    Chevyman57

    Thursday, July 12, 2012 2:42 PM
  • You can't delete a file that is currently open.

    Thursday, July 12, 2012 3:51 PM
  • But it's really not open. It does not show up in any available window view in Word.

    In the previous verson of Word, the file handle was released once you did a File Save As command and renamed it to a different filename. The hadnle focus was then on the new filename, not the original filename. Once my code did this saveas function, I was able to delete the old file from the previous folder so as not to have two versions of the same file. This is what my code did and did it successfully.

    The only thing I can see during code execution is that the file handle on original file remains in original folder and thus does not allow my  "filesys.DeleteFile (DocNameold), True" code to execute. Something has changed between versions 2007 and 2010 with the way file handles are used and released.

    The odd thing is that if I use the pulldown menu for "file save as" and execute it, then run my code, it runs fine and completes the file delete command as well. It does not work programmatically but does work with menu selection?????? I can send anyone the Word DOT file with this code if need be. Just let me know who and where to send it.

    Need more help, please.

    Chevyman57

    Thursday, July 12, 2012 4:22 PM
  • Try testing your SaveAs command and let it save in the default format. My testing indicates that the format parameter in the SaveAs line is giving problems in Office 2010. If you want the correct format then record some code to SaveAs.

    Change the following line:

    ActiveDocument.SaveAs2 FileName:=DocName, FileFormat:=wdFormatDocument

    to the following:

    ActiveDocument.SaveAs2 FileName:=DocName


    Regards, OssieMac

    Friday, July 13, 2012 1:27 AM
  • OssieMac,

    I have tried several iterations of the file "SaveAs2" command incluidng recording it using that code in my saveas snippet. I also tried to revert to the old SaveAs command with no different results. Also tried to "SaveAs2" to newer file format for macro enabled documents without any success.

    Don't know where to go from here. I have initiated a dialog message box to alert the user that they have to delete the old file manually. Problem with that is most of my users won't really care how trashy the file system gets.

    Chevyman57

    Friday, July 13, 2012 4:13 AM
  • Hello Again Chevyman57,

    So far I have not been able to emulate the problem. I SavedAs in the same path and in a different path.

    I was attempting to follow your code to see if SaveAs is in the same path as the old file and I am afraid that I got lost and gave up.

    I am thinking that the problem might be due to different paths and the temp file for the original file name is remaining open in the original location even though that did not occur in my testing. However, I am not on a network and strange things sometimes occur in these conditions.

    Anyway you might like to try a workaround. Just Save the file in its original location (without name change) and then close and rename the file and move it.

    Not sure if you need it but the following is a link to Copy, Move and Delete files.

    http://www.rondebruin.nl/folder.htm


    Regards, OssieMac

    Friday, July 13, 2012 5:03 AM
  • I have a same problem of you, but long path tool help me in this situation. So you can download a latest version of “Long Path Tool" for solve this problem.

    Monday, October 15, 2012 2:34 AM
  • This is how MS handles doc files in 2010.

    Handle is released with docx files during a save as.

    Wednesday, March 25, 2015 2:38 PM
  • I've been away from this for some time but will change the code to use the newer docx extension and see if that works out. Thanks for the suggestion. I'll post my results but it will be some time before I can work on it.

    Glenn


    Glenn

    Thursday, March 26, 2015 2:53 AM
  • You could try a tool like LockHunter to see if the file is locked and who has it locked.  I do something similar in Word.  I create a new docx file based on a template (.dotx) and then fill out the docx file.  The template is only used for an instant and it is a shared template on shared drive.  I have never had it lock up.  I never delete the template.
    Thursday, March 26, 2015 1:10 PM
  • mogulman52 and Don,

    I went back and looked at my code and had already updated it to SaveAs in the new docx format. It still holds the lock handle in place until Word closes, unlike earlier versions which released the lock handle when you did a SaveAs.

    As a note, all my Word and Excel macro-enabled (dotm & xltm) templates are read only and are never filled in, prompting the user for a file name on any close event or if they run the code gets auto-named. I do the SaveAs and concatenate the file name from data on the document (or sheet) that has been filled in. During the SaveAs the docx gets saved to a network folder and also on a local folder. The lock gets renamed to the filename and remains until Word is closed.

    So my code still fails at the point noted below while trying to delete an old filename version after the file has been saved as a new filename in a new folder. So....

    The code is looking in the last folder where the docx file was saved for the older filename so it can be deleted. The newest docx version has already been saved in a different folder and has a new lock handle of its own. That lock is not my problem, it's the older file lock which really exists in the same folder as the first filename that the docx was saved to. It does not release that lock until I exit Word. My work around has been to instruct all users to manually delete the older version file.

    The other odd thing is this only happens when I run it from code, if you manually go through these steps with the SaveAs menu drop-downs in Word it will release the lock handle???

    Hope this isn't to confusing and thanks for all your suggestions and help.

    Sub DeleteOldDoc() OldPathNet = "\\yourPath\" & TestDocName ' & "\" DocNameold = OldPathNet & TestDocName & "-" & DocName02 & "-" & DocName03 & "-" & DocName04 & ".doc" If Not TestDocName = DocName01 Then Set filesys = CreateObject("Scripting.FileSystemObject") If filesys.FileExists(DocNameold) Then filesys.DeleteFile (DocNameold), True 'I get file permission

    error here- lock handle is still present from last SaveAs command in

    last folder where previous version of file was saved. End If End If If DocName01 <> "" Then If Not TestDocName = DocName01 Then If Not TestDocName = "" Then MsgBox "Project Proposal Has Been Moved From Year " & TestDocName & " To " & DocName01 & "" End If End If End If TestDocName = DocName01 End Sub




    Glenn

    Friday, March 27, 2015 2:24 PM
  • I guess I am not understanding.  You create a new doc based on a template.  It should look like this.

    Set myDoc = Documents.Add("C:\templates\myTemplate.dotx")
    myDoc.SaveAs fileName:="C:\docs\myNewDoc.docx", _
    FileFormat:=wdFormatDocumentDefault, AddToRecentFiles:=False

    Then you edit myDoc.docx.  Then you delete myOldDoc.docx. 

    If you use a tool like LockHunter who has the file open.


    • Edited by mogulman52 Friday, March 27, 2015 7:02 PM
    Friday, March 27, 2015 7:01 PM
  • Only a long shots but a couple of things you might look at.

    I am wondering if something in the code is preventing the external processes from finishing because you say the file handle is released after the application closes and also the problem does not occur in the interactive mode.

    Try inserting the command DoEvents. (Somewhere after the Save as and before the following line)

    Set filesys = CreateObject("Scripting.FileSystemObject")

    Next have a look at lines like the following. If the object variable is still set to the document then it is worth setting the variable to nothing before the Save As because it might be attempting to hold a link to the document until the application closes which automatically sets the variables to nothing.

    Set CAPEX02 = ActiveDocument.ComboBox2

     

    Regards, OssieMac

    Saturday, March 28, 2015 3:04 AM