Running macro on All Files based on vbYesNo

babar2019

Board Regular
Joined
Jun 21, 2019
Messages
89
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?

VBA Code:
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
How would you run it against a workbook that is not fouind? Did I miss something?
 

babar2019

Board Regular
Joined
Jun 21, 2019
Messages
89
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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

Board Regular
Joined
Jun 21, 2019
Messages
89
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Using best guess method, here is what I came up with. Insert where you have the comment to run all files.
Code:
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 ?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,577
Messages
5,637,201
Members
416,960
Latest member
Carbon1198

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top