Cannot close temporary file and delete in VBA for Excel coding

[jolor69]

Hi All,

Good to be here and hope to learn more from you expert on VBA, especially for Excel and Power Point.
Issue : I have generated below VBA code for Excel and trying to tweak the code so that after processing is complete, the temporary file shown in attached pic : OutPutTemp1.xlsx would is required to be closed and it can then be deleted. I tried using "Kill" command but it never worked.
Any advice?

Below is the main code and I don't put all lines of code since Step 10 to 61 are just different combination of filters and total calculations.
Input to the files are
a. A CSV comma delimited file for example InputRawFile.csv
b. A template in Excel Workbook format for example Template.xlsx where the data will be populated.
Output of the files are
a. The temporary file OutPutTemp1
b. The updated template file Template.xlsx with data populated

Sub ProcessCSVFile()
    Dim filePath As Variant
    Dim wbCSV As Workbook
    Dim wbOutput As Workbook
    Dim wbTemplate As Workbook
    Dim lastRow As Long
       
    ' Step 1: Open dialog box to select a CSV file
    filePath = Application.GetOpenFilename("CSV Files (*.csv), *.csv")
   
    If filePath = "False" Then
        Exit Sub ' User canceled file selection
    End If
   
    ' Step 2: Save the file as an Excel workbook with the name "OutPutTemp1.xlsx"
    Set wbCSV = Workbooks.Open(filePath)
    wbCSV.SaveAs "OutPutTemp1.xlsx", FileFormat:=xlOpenXMLWorkbook
    wbCSV.Close
   
    ' Step 3: Open file "OutPutTemp1.xlsx"
    Set wbOutput = Workbooks.Open("OutPutTemp1.xlsx")
   
    ' Step 4: Adjust auto width for all columns
    wbOutput.Sheets(1).Cells.EntireColumn.AutoFit
   
    ' Step 5: Delete column AB and AC
    wbOutput.Sheets(1).Columns("AB:AC").Delete
   
    'FILTER FOR YAU AND YNZ
   
    ' Step 6: Filter column A to "YAU" and "YNZ" and column D to "3rd Party"
    wbOutput.Sheets(1).Range("A1").AutoFilter Field:=1, Criteria1:="YAU", Operator:=xlOr, Criteria2:="YNZ"
    wbOutput.Sheets(1).Range("D1").AutoFilter Field:=4, Criteria1:="3rd Party"
   
    ' Step 7: Total column AB for visible cells
    wbOutput.Sheets(1).Cells(wbOutput.Sheets(1).Rows.Count, "AB").End(xlUp).Offset(1, 0).Formula = "=SUBTOTAL(109,AB2:AB" & wbOutput.Sheets(1).Cells(wbOutput.Sheets(1).Rows.Count, "AB").End(xlUp).Row & ")"
   
    ' Step 8: Open file "FYxx Monthly Operation Report for YEA Group Template.xlsx"
    Set wbTemplate = Workbooks.Open("FYxx Monthly Operation Report for YEA Group Template.xlsx")
   
    ' Step 9: Value from item 7 to cell B6 divided by 1000
    wbTemplate.Sheets(1).Range("B6").Value = wbOutput.Sheets(1).Cells(wbOutput.Sheets(1).Rows.Count, "AB").End(xlUp).Offset(1, 0).Value / 1000
   
>>>>>>  Step 10 until 61 are basically similar codes similar as above Steps 6 to 9 with different filtering requirements.
   
    ' Step 62: Add the current date and time to cell C1 in the template file
    wbTemplate.Sheets(1).Range("C1").Value = Now
   
    ' Step 63: Add the date and time of the imported CSV file to cell C2 in the template file
    wbTemplate.Sheets(1).Range("C2").Value = FileDateTime(filePath)

    ' Step 64: Ask the user for the input month in the "mmm-yyyy" format
    Dim inputMonth As String
    Do
        inputMonth = InputBox("Input Month & Year for this Report for (mmm-yyyy)", "Input Month & Year")
        If Not IsDate(inputMonth & "-01") Then
            MsgBox "Input Month & Year not in correct format", vbExclamation
        End If
    Loop Until IsDate(inputMonth & "-01")
   
   ' Step 65: Paste the input month as text in the "mmm-yyyy" format to cell C3 in the template file
    wbTemplate.Sheets(1).Range("C3").NumberFormat = "@"
    wbTemplate.Sheets(1).Range("C3").Value = Format(DateValue(inputMonth & "-01"), "mmm-yyyy")
   
    ' Step 66: Add the file name of the imported CSV file to cell G1 in the template file
    wbTemplate.Sheets(1).Range("G1").Value = Right(filePath, Len(filePath) - InStrRev(filePath, "\"))
   
    ' Close and save the template workbook
    ' Close and save the output workbook
    wbTemplate.Close SaveChanges:=True
   
   
    MsgBox "Process completed successfully! See updated data on file : FYxx Monthly Operation Report for YEA Group Template.xlsx", vbInformation
End Sub

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Cannot close temporary file and delete in VBA for Excel coding - Eileen's Lounge

www.eileenslounge.com

Redirecting

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[jolor69]

Helo.
Note, for benefit to others, this query has been replied in following thread. It was amended and tested and worked as expected

Redirecting