Create a Recursive VBA to call itself and create mass files

[seigen]

Hello, and thanks everyone for your replies

I am currently facing a small problem with some VBA code I wrote some time back. I need this VBA that executes a Macro, it copies some data and it generates a file, to generate the files automatically (ie in a recursive fashion)


The solution I came is that:

1) Given a set of source Workbooks (where the data is copied from) and,
2) Given a set of filenames that they will be Saved As

Then,

I execute a windows batch script (.bat) that it waits for me to input the file name, and then it waits for me to open Excel, go to the VBA Editor, and paste the generated VBA, which is changing in the background each time, with the help of the same batch script. This batch script just basically reads a text file with a "dummy" VBA, then changes the filename pattern with the one I input in the batch script, and then I need to go back to Excel, to paste the VBA, and then execute.
This process is being repeated N times. Given that N is the set of filenames each file will be Saved As.


So if I have a set of 99 filenames, I will have to loop and manually repeat (again, with the help of the windows batch script I created), to wait and go to Excel, paste the VBA, execute, and continue with the next filename.


I know that I can "avoid" the need to generate a different VBA in the background, copy it, and then paste it each time, and I can have just one VBA that will call itself and do all of this process in one shot, but haven't found it yet



I am posting the "dummy" code as reference:

Option Explicit

Public wb As Workbook
Public currYear As String
Public currMonth As String
Public KPIReportsHome As String
Public currentFile As String
Public savedAsWorkbook As String
Public KPIPreviousMonth As String
Public ZUSERRISKREP As String

'Variables to replace
    'Replace the path of the previous month KPI as helper
    'KPIPreviousMonth =

    'Replace this with the filename. Replace the month, and replace the system at the end.
    'currentFile =
    'savedAsWorkbook =

    'Change these to the current Year & Month
    'currYear =
    'currMonth =
        
    'Change this to the folder where you want to download the files. Leave a backslash at the end
    'KPIReportsHome =


    'Change the path to the correct one for ZUSERRISKREP file
    'ZUSERRISKREP =


    'YYMM with the next month (or last two digits of next year if applicable)


Private Sub WorkbookInitialize()
    'Replace this with the filename. Replace the month, and replace the system at the end.
    currentFile = "\REP_RU_2303_GRCKPI_Report_PBS.xlsx"
    savedAsWorkbook = "REP_RU_2303_GRCKPI_Report_E-FISS-XXXX"

    'Change these to the current Year & Month
    currYear = "2023"
    currMonth = "March"
        
    'Change this to the folder where you want to download the files. Leave a backslash at the end
    KPIReportsHome = "C:\Users\sesa684878\Desktop\KPI Reports\"

    'Change the path to the correct one for ZUSERRISKREP file
    ZUSERRISKREP = "C:\Users\sesa684878\Desktop\KPI Reports\2023\March\Helper Files\PBS\ZUSERRISKREP_PBS.xlsx"
    
    'Replace the path of the previous month KPI Report as helper
    KPIPreviousMonth = "C:\Users\sesa684878\Desktop\KPI Reports\2023\March\Helper Files\CoCo Reports\REP_RU_2302_GRCKPI_Report_E-FISS-XXXX.xlsx"

End Sub


Private Sub OpenCopyPaste()
    Dim Reportwb As Workbook
    Dim ws As Worksheet

    Call WorkbookInitialize

    'set report workbook to workbook object (works only if the file is already open)
    Set Reportwb = ThisWorkbook
    Set ws = Reportwb.Sheets("3_KPI_Overview")
    Application.ScreenUpdating = False

    'Find the last FF ID and FF ID assignments. Change the letter of the range for next month
    ws.Range("G20").FormulaArray = _
        "=IF('7_KPI_FFID_Usage'!R5C3="""",0,COUNTA(UNIQUE(FILTER('7_KPI_FFID_Usage'!R5C3:R900000C3, '7_KPI_FFID_Usage'!R5C3:R900000C3<>""""))))"
    
    ws.Range("G21").FormulaArray = _
        "=IF('7_KPI_FFID_Usage'!R5C2="""",0,COUNTA(UNIQUE(FILTER('7_KPI_FFID_Usage'!R5C2:R900000C2, '7_KPI_FFID_Usage'!R5C2:R900000C2<>""""))))"


    ws.Range("G20:G21").Copy
    ws.Range("G20:G21").PasteSpecial xlPasteValues


    'open the KPIPreviousMonth file and select the source sheet
    Set wb = Workbooks.Open(Filename:=KPIPreviousMonth)

    'copy the source range and paste in desired range in ThisWorkbook. Change the letter of the range at the right for next month
    wb.Sheets("3_KPI_Overview").Range("D5:F7").Copy
    ws.Range("D5:F7").PasteSpecial xlPasteValues

    wb.Sheets("3_KPI_Overview").Range("D12:F13").Copy
    ws.Range("D12:F13").PasteSpecial xlPasteValues

    wb.Sheets("3_KPI_Overview").Range("D20:F21").Copy
    ws.Range("D20:F21").PasteSpecial xlPasteValues

    wb.Close False


    'Open the ZUSERRISKREP file. Change the letter of the range for next month
    Set wb = Workbooks.Open(Filename:=ZUSERRISKREP)
    '1 Number of users in scope
    ws.Range("G5").Formula2R1C1 = _
        "=IF('4_KPI_Details '!R5C2="""",0,ROWS(UNIQUE(FILTER('4_KPI_Details '!R5C2:R900000C2, ('4_KPI_Details '!R5C2:R900000C2<>"""")*('4_KPI_Details '!R5C4:R900000C4=""E-FISS-XXXX"")))))"
    
    '2 Number of users with risks (before mitigation)
    ws.Range("G6").Formula2R1C1 = _
        "=IFERROR(ROWS(UNIQUE(FILTER([ZUSERRISKREP_PBS.xlsx]Sheet1!R2C3:R900000C3, ([ZUSERRISKREP_PBS.xlsx]Sheet1!R2C3:R900000C3<>"""")*([ZUSERRISKREP_PBS.xlsx]Sheet1!R2C5:R900000C5=""E-FISS-XXXX"")))),0)"
    
    '3 Number of users with risks (after mitigation)
    ws.Range("G7").Formula2R1C1 = _
        "=IFERROR(ROWS(UNIQUE(FILTER([ZUSERRISKREP_PBS.xlsx]Sheet1!R2C3:R900000C3, ([ZUSERRISKREP_PBS.xlsx]Sheet1!R2C5:R900000C5=""E-FISS-XXXX"")*([ZUSERRISKREP_PBS.xlsx]Sheet1!R2C7:R900000C7="""")))),0)"
    
    '7 Number of risks (before mitigation)
    ws.Range("G12").FormulaR1C1 = _
        "=IFERROR(COUNTIFS([ZUSERRISKREP_PBS.xlsx]Sheet1!R2C6:R900000C6, ""<>"",[ZUSERRISKREP_PBS.xlsx]Sheet1!R2C5:R900000C5, ""=E-FISS-XXXX""),0)"
    
    '8 Number of MCs attached
    ws.Range("G13").FormulaR1C1 = _
        "=IFERROR(COUNTIFS([ZUSERRISKREP_PBS.xlsx]Sheet1!R2C7:R900000C7, ""<>"",[ZUSERRISKREP_PBS.xlsx]Sheet1!R2C5:R900000C5, ""=E-FISS-XXXX""),0)"




    ws.Range("G5:G7").Copy
    ws.Range("G5:G7").PasteSpecial xlPasteValues
    ws.Range("G7").Font.Bold = True
    
    ws.Range("G12:G13").Copy
    ws.Range("G12:G13").PasteSpecial xlPasteValues


    wb.Close False
    Application.ScreenUpdating = True

End Sub

Private Sub KPIHelper()
    Dim SelectedCells As Range
    Dim ws As Worksheet
    Set wb = ThisWorkbook

    'Sheet 4_KPI_Details
    Set ws = wb.Sheets("4_KPI_Details ") ' <-- Change this to the Sheet Name
    ws.Select
    Set SelectedCells = Selection
    SelectedCells.AutoFilter Field:=3, Criteria1:="<>E-FISS-XXXX"
    SelectedCells.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    ActiveSheet.AutoFilterMode = False
    
    ws.Range("B4").Select


    'Sheet 5_KPI_Risks_per_Users
    Set ws = wb.Sheets("5_KPI_Risks_per_Users") ' <-- Change this to the Sheet Name
    ws.Select
    Set SelectedCells = Selection
    SelectedCells.AutoFilter Field:=3, Criteria1:="<>E-FISS-XXXX"
    SelectedCells.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    ActiveSheet.AutoFilterMode = False
    
    ws.Range("B4").Select

    'Sheet 6_KPI_Roles
    Set ws = wb.Sheets("6_KPI_Roles") ' <-- Change this to the Sheet Name
    ws.Select
    Set SelectedCells = Selection
    SelectedCells.AutoFilter Field:=3, Criteria1:="<>E-FISS-XXXX"
    SelectedCells.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    ActiveSheet.AutoFilterMode = False
    
    ws.Range("B4").Select

    'Sheet 7_KPI_FFID_Usage
    Set ws = wb.Sheets("7_KPI_FFID_Usage") ' <-- Change this to the Sheet Name
    ws.Select
    Set SelectedCells = Selection
    SelectedCells.AutoFilter Field:=6, Criteria1:="<>E-FISS-XXXX"
    SelectedCells.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    ActiveSheet.AutoFilterMode = False
    
    ws.Range("B4").Select


    'Sheet 8_KPI_Smart_MCs
    Set ws = wb.Sheets("8_KPI_Smart_MCs") ' <-- Change this to the Sheet Name
    ws.Select
    Set SelectedCells = Selection
    SelectedCells.AutoFilter Field:=3, Criteria1:="<>E-FISS-XXXX"
    SelectedCells.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    ActiveSheet.AutoFilterMode = False
    
    ws.Range("B4").Select


End Sub


Private Sub MasterMacro()
    Set wb = ThisWorkbook
    Dim homesheet As Worksheet
    Set homesheet = wb.Sheets("3_KPI_Overview")

    Application.DisplayAlerts = False
    
    'Call Macros Section
    Call WorkbookInitialize
    Call KPIHelper
    Call OpenCopyPaste


    homesheet.Select
    homesheet.Range("B4").Select

    ActiveWorkbook.SaveAs KPIReportsHome & currYear & "\" & currMonth & "\Reports CoCo\" & savedAsWorkbook & ".xlsx", FileFormat:=xlWorkbookDefault

    'Reopen the master KPI File
    Workbooks.Open KPIReportsHome & currYear & "\" & currMonth & currentFile
    

    'Close the current Workbook
    Workbooks(savedAsWorkbook).Close


    Application.DisplayAlerts = True

End Sub

Remember that:


1) The source Workbook changes each time. This is noted with the variable: KPIPreviousMonth
2) The Saved As Workbook changes each time. This is noted with the variable: savedAsWorkbook



Thank you very much

 

[seigen]

Is it possible to use recursion and have the VBA be reading one filename and once the file is copied and the filename is generated with the filename (which again changes each time) ?


Thanks for your replies