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