Why can't I delete files using kill method?

nmc

New Member
Joined
Aug 25, 2022
Messages
38
Office Version
  1. 2021
Platform
  1. Windows
Hello, I have the following code and in the end I want to delete Mapping.xlsx, excel2.xlsx and wordTemplate.docx. But if I apply kill method they are deleted and I need to end the task in the manager to grant the deleting, can someone help?

VBA Code:
Option Explicit

Sub ReplaceWordsBetweenAngleBrackets()

   'Get the starting time
    Dim startTime As Double
    startTime = Timer
    

    Dim wordApp As Word.Application
    Dim wordDoc As Object
    Dim wordRange As Object
    Dim totalReplacements As Long
    Dim ws As Worksheet
    Dim searchValue As String
    Dim replaceValue As Variant
    Dim fld As Object
    Dim i As Long
    Dim excelApp As Excel.Application
    Dim excelWorkbook As Object
    Dim excelAApp As Excel.Application
    Dim excelWWorkbook As Object
    Dim ms As Worksheet
    Dim sourcePath As String
    Dim destPath As String
    Dim FSO As Object
    Dim filePath As String
    Dim fileName As String
    Set excelAApp = New Excel.Application
    Set excelApp = New Excel.Application
    Dim k As Integer, Artcnt As Integer
            Dim wdSec As Object
    Dim wdHF As Object
    Dim wdRng As Object

    
    
    'Open the Word file
    Set wordApp = CreateObject("Word.Application")
    Set wordDoc = wordApp.Documents.Open("C:\TempPrint\wordTemplate.docx")
    
    'Open the Excel file
    Set excelApp = CreateObject("Excel.Application")
    Set excelWorkbook = excelApp.Workbooks.Open("C:\TempPrint\excel2.xlsx")
    Set ws = excelWorkbook.Sheets(1)
    
    'Remove all mail merge fields from the Word document
    For Each fld In wordDoc.Fields
        fld.Unlink
    Next fld
    
'Merge pr colunas
    'Substitute words with values from the checklist
    'Loop through each search value in column A or each search value in row 1, depending on the option button clicked
    If Upload.OptionButton1.value = True Then 'OptionButton1 is selected
        For i = 1 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
            searchValue = Trim(ws.Cells(i, 1).value)
            replaceValue = CStr(ws.Cells(i, 2).value)
            
            'Search for matches of the search value and replace them with the replace value
            Set wordRange = wordDoc.Content
            With wordRange.Find
                .ClearFormatting
                .Text = searchValue
                .MatchWildcards = True
                If IsArray(replaceValue) Then
                    .Replacement.Text = Join(replaceValue, ", ")
                Else
                    .Replacement.Text = replaceValue
                End If
                .Execute Replace:=wdReplaceAll
            End With
        Next i
        
ElseIf Upload.OptionButton2.value = True Then 'OptionButton2 is selected
    For i = 1 To ws.Cells(1, Columns.Count).End(xlToLeft).Column
        searchValue = Trim(ws.Cells(1, i).value)
        replaceValue = CStr(ws.Cells(2, i).value)
            
            'Search for matches of the search value and replace them with the replace value
            Set wordRange = wordDoc.Content
            With wordRange.Find
                .ClearFormatting
                .Text = searchValue
                .MatchWildcards = True
                If IsArray(replaceValue) Then
                    .Replacement.Text = Join(replaceValue, ", ")
                Else
                    .Replacement.Text = replaceValue
                End If
                .Execute Replace:=wdReplaceAll
            End With
        Next i

    End If

    'Define words for gender
    ' Define the source and destination paths
    
    
Dim xlApp As Object
Dim xlWorkbook As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False
Set xlWorkbook = xlApp.Workbooks.Open("C:\TempPrint\Setup.xlsx")
    
    sourcePath = xlWorkbook.Sheets(1).range("C2").value
    destPath = "C:\TempPrint\Mapping.xlsx"
    
    ' Check if the source file exists
    If Not Dir(sourcePath) <> "" Then
        MsgBox "Source file does not exist!"
        Exit Sub
    End If
    
    ' Copy the file to the destination path
    FileCopy sourcePath, destPath
        
    'Open the Excel file
    Set excelApp = CreateObject("Excel.Application")
    Set excelWorkbook = excelApp.Workbooks.Open("C:\TempPrint\Mapping.xlsx")
    Set ms = excelWorkbook.Sheets(1)
    
    'Loop through each search value in column A or each search value in row 1, depending on the option button clicked
    If SelectTemplate.OptionButton1.value = True Then 'OptionButton1 is selected
        For i = 1 To ms.Cells(ms.Rows.Count, 1).End(xlUp).Row
            searchValue = ms.Cells(i, 1).value
            replaceValue = CStr(ms.Cells(i, 2).value)
            
            'Search for matches of the search value and replace them with the replace value
            Set wordRange = wordDoc.Content
            With wordRange.Find
                .ClearFormatting
                .Text = searchValue
                .MatchWildcards = True
                If IsArray(replaceValue) Then
                    .Replacement.Text = Join(replaceValue, ", ")
                Else
                    .Replacement.Text = replaceValue
                End If
                .Execute Replace:=wdReplaceAll
            End With
        Next i
    ElseIf SelectTemplate.OptionButton2.value = True Then 'OptionButton2 is selected
        For i = 1 To ms.Cells(ms.Rows.Count, 1).End(xlUp).Row
            searchValue = ms.Cells(i, 1).value
            replaceValue = CStr(ms.Cells(i, 3).value)
            
            'Search for matches of the search value and replace them with the replace value
            Set wordRange = wordDoc.Content
            With wordRange.Find
                .ClearFormatting
                .Text = searchValue
                .MatchWildcards = True
                If IsArray(replaceValue) Then
                    .Replacement.Text = Join(replaceValue, ", ")
                Else
                    .Replacement.Text = replaceValue
                End If
                .Execute Replace:=wdReplaceAll
            End With
        Next i
    End If
    
        'Replace the symbol with an empty string
    wordApp.Selection.Find.Execute FindText:="»", ReplaceWith:="", _
        Replace:=2, Forward:=True, Wrap:=wdFindContinue
        
            'Replace the symbol with an empty string
    wordApp.Selection.Find.Execute FindText:="«", ReplaceWith:="", _
        Replace:=2, Forward:=True, Wrap:=wdFindContinue
    
    
    'Loop through each section in the document
    For Each wdSec In wordDoc.Sections
        'Loop through each header in the section
        For Each wdHF In wdSec.Headers
            Set wdRng = wdHF.range
            With wdRng.Find
                .Text = "«"
                .Replacement.Text = ""
                .Wrap = 1 '1 = wdFindContinue
                .Execute Replace:=2 '2 = wdReplaceAll
            End With
        Next wdHF
        'Loop through each footer in the section
        For Each wdHF In wdSec.Footers
            Set wdRng = wdHF.range
            With wdRng.Find
                .Text = "«"
                .Replacement.Text = ""
                .Wrap = 1 '1 = wdFindContinue
                .Execute Replace:=2 '2 = wdReplaceAll
            End With
        Next wdHF
    Next wdSec
    
        'Loop through each section in the document
    For Each wdSec In wordDoc.Sections
        'Loop through each header in the section
        For Each wdHF In wdSec.Headers
            Set wdRng = wdHF.range
            With wdRng.Find
                .Text = "»"
                .Replacement.Text = ""
                .Wrap = 1 '1 = wdFindContinue
                .Execute Replace:=2 '2 = wdReplaceAll
            End With
        Next wdHF
        'Loop through each footer in the section
        For Each wdHF In wdSec.Footers
            Set wdRng = wdHF.range
            With wdRng.Find
                .Text = "»"
                .Replacement.Text = ""
                .Wrap = 1 '1 = wdFindContinue
                .Execute Replace:=2 '2 = wdReplaceAll
            End With
        Next wdHF
    Next wdSec
      
        wordDoc.Fields.Update
        
            'Remove all mail merge fields from the Word document
    For Each fld In wordDoc.Fields
        fld.Unlink
    Next fld
           
    Dim ObjRng As Object

Dim NumberingOption As String
Dim ArticleWord As String
Dim ColonWord As String
Dim OrderOption As String

'Read values from cells D2 and F2

NumberingOption = xlWorkbook.Sheets(1).range("D2").value
ArticleWord = xlWorkbook.Sheets(1).range("E2").value
ColonWord = xlWorkbook.Sheets(1).range("F2").value
OrderOption = xlWorkbook.Sheets(1).range("G2").value

    'Open the Word document

    'Get the range of the document
    Set ObjRng = wordDoc.Content
    
If NumberingOption = "Yes" And OrderOption = "After" Then
    
    For k = 1 To ObjRng.Words.Count
    
        If RTrim(ObjRng.Words(k)) = ArticleWord Then
          Artcnt = Artcnt + 1
            'Add the number to the word
            ObjRng.Words(k).Text = ArticleWord & " " & CStr(Artcnt) & " " & ColonWord
        End If
    Next k

ElseIf NumberingOption = "Yes" And OrderOption = "Before" Then

    For k = 1 To ObjRng.Words.Count
    
        If RTrim(ObjRng.Words(k)) = ArticleWord Then
          Artcnt = Artcnt + 1
            'Add the number to the word
            ObjRng.Words(k).Text = ArticleWord & " " & ColonWord & " " & CStr(Artcnt)
        End If
    Next k
End If
        
    'Save the Word document in the selected format and path based on the selected option button
    If Len(SelectTemplate.TextBox2.value) > 0 And Len(SelectTemplate.TextBox1.value) > 0 Then
        filePath = SelectTemplate.TextBox2.value
        fileName = SelectTemplate.TextBox1.value
        
        If SelectTemplate.OptionButton7.value = True Then 'OptionButton7 is selected (save as Word file)
            wordDoc.SaveAs2 fileName:=filePath & "\" & fileName & ".docx", FileFormat:=WdSaveFormat.wdFormatDocumentDefault
        ElseIf SelectTemplate.OptionButton8.value = True Then 'OptionButton8 is selected (save as PDF file)
            wordDoc.SaveAs2 fileName:=filePath & "\" & fileName & ".pdf", FileFormat:=wdFormatPDF
        ElseIf SelectTemplate.OptionButton6.value = True Then 'OptionButton6 is selected (save as both Word and PDF file)
            wordDoc.SaveAs2 fileName:=filePath & "\" & fileName & ".docx", FileFormat:=WdSaveFormat.wdFormatDocumentDefault
            wordDoc.SaveAs2 fileName:=filePath & "\" & fileName & ".pdf", FileFormat:=wdFormatPDF
        End If
        End If
        
        

        
'Get the ending time
Dim endTime As Double
endTime = Timer
    
'Calculate the total time taken in minutes
Dim totalTime As Double
totalTime = Round((endTime - startTime) / 60)


    
'Display the time taken in a message box
MsgBox "Code took " & totalTime & " minutes to run.", vbInformation, "Time Taken"



End Sub
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
When finished with a document or workbook, make sure that you first close the document or workbook, then quit the application, and then set the variables equal to Nothing, for example...

VBA Code:
Set wordApp = Nothing

'etc
'
'

Hope this helps!
 
Upvote 0
Solution

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top