Running macro on All Files based on vbYesNo

[babar2019]

Hi All,

I would like the macro below to run for all excel files in the destination if the answer is chosen as 'Yes'. Currently the macro matches value in A2 Colummn of the active workbook and runs only for the ones found.

Can you please assist?

Sub InProcessRecon()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook, srcWS As Worksheet, desWS As Worksheet, LastRow As Long, key As Variant, totals As Long, totals1 As Long, totals2 As Long, fVisRow As Long
    Dim RngList As Object, rng As Range, arr As Variant, i As Long, fNames As String, code As Variant, sDate As String, Day1 As String, prevWS As Worksheet
    Dim answer As Integer
    
    'Source File Sheet name
    Set srcWS = Sheets("QRYLIBA380.CSIPHIST>Sheet1")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    'Opening In Process files based on the value coming from source file
    fNames = "SIGNAPAY LTD IN PROCESS ACCOUN,In Process DDA Recon - SignaPay," & _
             "EPT 6001 IN PROCESS ACCOUNT,In Process DDA Recon - EPS," & _
             "APS IN PROCESS ACCOUNT,In Process DDA Recon - APS," & _
             "PAYMENT WORLD IN PROCESS ACCT,In Process DDA Recon - Payment World," & _
             "TRISOURCE IN PROCESS ACCOUNT,In Process DDA Recon - TriSource," & _
             "BANCTEK SOLUTIONS IN PROCESS,In Process DDA Recon - BancTek," & _
             "MERCHANT BANCARD IN PROCESS,In Process DDA Recon - MBN," & _
             "ADVANCE MERCHANT IN PROCESS AC,In Process DDA Recon - DAS," & _
             "2C PROCESSOR IN PROCESS,In Process DDA Recon - 2CP," & _
             "FRONTLINE IN PROCESS ACCOUNT,In Process DDA Recon - FrontLine," & _
             "TITANIUM PROCESSING IN PROCESS,In Process DDA Recon - Titanium Processing," & _
             "ARGUS MERCHANT IN PROCESS ACCT,In Process DDA Recon - Argus," & _
             "INFINITY CAPTIAL LLC IN PROCES,In Process DDA Recon - Choice," & _
             "TITANIUM PAYMENTS IN PROCESS,In Process DDA Recon - Titanium Payments," & _
             "MERCHANT INDUSTRY2 IN PROCESS,In Process DDA Recon - Merchant Industry," & _
             "UNIFIED PAYMENTS IN PROCESS,In Process DDA Recon - Unified," & _
             "ELECTRONIC MERCHANT SYS IN PRO,In Process DDA Recon - EMS Conversion," & _
             "MAVERICK IN PROCESS ACCOUNT,In Process DDA Recon - Maverick," & _
             "PIVOTAL PAYMENTS IN PROCESS,In Process DDA Recon - Nuvei," & _
             "C&H FINANCIAL SERVICES IN PROC,In Process DDA Recon - C&H," & _
             "MERCHANT LYNX SERVICES IN PROC,In Process DDA Recon - Merchant Lynx," & _
             "EVANCE IN PROCESS,In Process DDA Recon - eVance," & _
             "TSYS IN PROCESS ACCOUNT,In Process DDA Recon - TSYS," & _
         "BANKCARD IN PROCESS ACCOUNT,In Process DDA Recon - BankCard,GRANITE PAYMENT ALLIANCE IN PR,In Process DDA Recon - Granite,AUTOSCRIBE CORP IN PROCESS,In Process DDA Recon - AUTOSCRIBE,TRX SERVICES IN PROCESS ACCT,In Process DDA Recon - TRX Merchant Services"
        
        answer = MsgBox("Do you wish to roll over the Month End Data?", vbQuestion + vbYesNo + vbDefaultButton2, "Month End Roll Over")
        
        arr = Split(Application.Trim(fNames), ",")
    Set RngList = CreateObject("Scripting.Dictionary")
    For Each rng In srcWS.Range("A2", srcWS.Range("A" & srcWS.Rows.Count).End(xlUp))
        If Not RngList.Exists(rng.Value) Then
            RngList.Add rng.Value, Nothing
        End If
    Next rng
    For Each key In RngList
        For i = 0 To UBound(arr)
            If arr(i) = key Then
                Set wkbDest = Workbooks.Open(ActiveWorkbook.Path & "\" & arr(i + 1) & ".xlsx")
                With srcWS.Cells(1).CurrentRegion
                    .AutoFilter 1, key
                    fVisRow = srcWS.Range("A1", srcWS.Range("A" & srcWS.Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
                    sDate = srcWS.Cells(fVisRow, 4)
                    Day1 = Left(Right(sDate, 4), 2)

If answer = vbYes Then
'Run this for all files in the active workbook path. Currently it's matching A2 Colummn of the active workbook and running only for the ones found.
Else
'I already have this working for 'No'.

End sub

 

[JLGWhiz]

How would you run it against a workbook that is not fouind? Did I miss something?

 

[babar2019]

Hi @JLGWhiz

The 'fNames' string actually has all the workbooks mentioned by name. However the macro runs based on if the source workbook has the file mentioned and updates only those which are found from 'fNames'..

What I'm requesting is whenever the answer is chosen 'Yes', Then every file specified under 'fNames' should be updated.

Hope I was clear.

Thank you

 

[JLGWhiz]

Sorry, I am still lost. The question remains,. How can VBA know where to find the workbooks you want to update, if the fNames listing does not find a match. There is no other reference to any workbook that I can see.

 

[babar2019]

HI @JLGWhiz

When the answer is chosen 'Yes', Can we not code VBA to use 'ActiveWorkbook.Path' and open each file specified under 'fNames' with extension .xlsx and run the macro ?

For example, if the workbook that I'm running macro is under C:\Users\Test which is the ActiveWorkbook.Path, I want VBA to open a workbook called 'In Process DDA Recon - SignaPay.xlsx' which is under the same location and specified under fNames, is this not possible?

 

[JLGWhiz]

Using best guess method, here is what I came up with. Insert where you have the comment to run all files.

If answer = vbYes Then
'Run this for all files in the active workbook path. Currently it's matching A2 Colummn of the active workbook and running only for the ones found.
    For i = 0 To UBound(arr)
        Set wkbDest = Workbooks.Open(ThisWorkbook.Path & "\" & arr(i) & ".xlsx")
        For Each rng In srcWS.Range("A2", srcWS.Cells(Rows.Count, 1).End(xlUp))
            With srcWS.Cells(1).CurrentRegion
                    .AutoFilter 1, rng.Value
                            fVisRow = srcWS.Range("A1", srcWS.Range("A" & srcWS.Rows.Count).End(xlUp)). _
                            SpecialCells(xlCellTypeVisible).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
                            sDate = srcWS.Cells(fVisRow, 4)
                            Day1 = Left(Right(sDate, 4), 2)
                    End With
        Next
        wb.Close 'Not sure you want this here but included just in case
    Next
Else

II assume there is more code to do something with the variables that are initialized, but I did not see it in the posted code. Good luck.

P.S. The answer to the below question is, "No".

When the answer is chosen 'Yes', Can we not code VBA to use 'ActiveWorkbook.Path' and open each file specified under 'fNames' with extension .xlsx and run the macro ?