Cannot close temporary file and delete in VBA for Excel coding

jolor69
NewLounger
Posts: 1
Joined: 12 Oct 2023, 09:56

Cannot close temporary file and delete in VBA for Excel coding

Post by 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

+++++++++++++++++++++++++++++++++++++++++++++++++++++

Code: Select all

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
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 79365
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Cannot close temporary file and delete in VBA for Excel coding

Post by HansV »

Welcome to Eileen's Lounge!
Add the following lines above the final MsgBox:

Code: Select all

    Dim strPath As String
    strPath = wbOutput.FullName
    wbOutput.Close SaveChanges:=False
    DoEvents
    Kill strPath
Best wishes,
Hans