Search and Pick the File from Folder based on Cell value the Validate the Both Reports with Format

Gunasekaran · Sep 2, 2022

There is a master excel file I have. The values in column F include MFG_JAGUAR_870, DSN_LABS_335 and so on.
I have two folders, FDSS and ETB, which contain around 340+ excel files.

1. ETB Folder File format - File names will be ETB _870_MFG_JAGUAR_870.xlsx, ETB_335_DSN_LABS_335.xlsx.

2. FDSS Folder File format - File names will be 870_MFG_JAGUAR_870_30.4 FDSS Map Local_Global Account By MEP_CODE.xlsx,
335_DSN_LABS_335_30.4 FDSS Map Local_Global Account By MEP_CODE.xlsx.

Go to Folder if cell F2 value is MFG_JAGUAR_870, the macro should memorize this value.
Pick the file, copy the data, and paste it into the appropriate sheet. In the Second Folder, it is the same way

Currently, I am copying and pasting the file data into a file, then validating it. For me, it will take more than a day to complete...total 350 files.

Searching for exact cell values with file names on Google returns no results.
I am unable to find and pick the file by loop condition...

All steps and processes are attached in the macro file. I wrote almost more code. However, this is not supported.

Can anyone help me on this please. Sir............................

vletm · Sep 3, 2022

Gunasekaran
What should happen after cells B3 text (in Process Steps-sheet)?

Gunasekaran · Sep 3, 2022

vletm said:
Gunasekaran
What should happen after cells B3 text (in Process Steps-sheet)?

Really sorry for that if any miscommunication Sir. Now again i attached Steps details in attached File.

My Process like, I need to validate 30.4 FDSS vs ETB by each MEP Code wise., with the standard format. Then save the file as MEP Order. That's all Sir...

vletm · Sep 3, 2022

Gunasekaran
Do You really do Your task as You've written?

Gunasekaran · Sep 3, 2022

Sir yes relly believe me. Now am working this task in my org. God promise. Recently I was moved to automation team from finance team. Due to job loss issue. Kindly support to me sir. So that,. they atleats trust my work. Otherwise my job....

Gunasekaran · Sep 3, 2022

I know little bit vba macro and good in recording macro to editing...if not believe I will connect with zoom meeting.. my number ...

vletm · Sep 3, 2022

Gunasekaran
Your given steps are like below:
#1 get MFG_JAGUAR_870
#2 open 1st file
#3 save it
#4 open 2nd file
#5 save it
... okay, You can do as above ...

Gunasekaran · Sep 3, 2022

vletm said:
Gunasekaran
Your given steps are like below:
#1 get MFG_JAGUAR_870
#2 open 1st file
#3 save it
#4 open 2nd file
#5 save it
... okay, You can do as above ...

15 Minutes once. I will refresh this page URL. And waiting for your answer sir...

Correct Sir, by Loop Condition same below steps will repeat till to MEP code last.
Open First File copy and paste in 30.4 FDSS Sheet.
then Open Second File, copy and paste in ETB Sheet

Then do the Validation both files is an Output Sheet method.

Then this two sheet move to same ME_Financial_MFG_JAGUAR_870.xlsx. method in the same Location.

Like that i have to do more than 89+ Files By Loop Condition...

Your given steps are like below:
#1 get MFG_JAGUAR_870
#2 open 1st file
#3 save it
#4 open 2nd file
#5 save it
... okay, You can do as above ...

Gunasekaran · Sep 3, 2022

Kindly share the code sir. Pz

vletm · Sep 3, 2022

Gunasekaran
Based Your given writings, there is not possible to write code.

Gunasekaran · Sep 4, 2022

Why sir is it no possible In vba..

Am looking loop condition with based on partial cell value. Open the file and paste in main file. Then validate save as new name.

Is this not possible in vba..

Atleast can u do favour. Open the file based on partial cell value ????

vletm · Sep 4, 2022

Gunasekaran
As I wrote Based Your given writings, there is not possible to write code.
Excel won't have of course-option.

Gunasekaran · Sep 4, 2022

OK sir. We don't have option to.open the files from folder based on partial cell value.. ???

Am un expecting this answer from experts..like u

Marc L · Sep 6, 2022

Try the Dir VBA function with wildcards as explained in VBA help …

Gunasekaran · Sep 6, 2022

Thank you very much for your positive response, Sir. Last 5 days I did many searches and completed this easy and big loop task.

However, I have a few questions and problems regarding my work.

What I have to do
1. I have validated both Unique Reports and saved them in a new book. (Around 360+ reports) why it is not possible to release this memory. My opened workbook also displayed a left-side Project Window after I closed it ( currently i tested only 4 MEP_Code Report )
2. Before starting this process. In both folders, there is a MEP_Code Report available for validation. I would like to know how to validate and update the status in Excel.? - Not yet to find the Code...

Project Windows Module Name - NewCodeME_First_Step_Financial

-

Code:

Sub First_Step_FileOpen_By_Loop()

  Dim wb As Workbook
    Dim Path, KeyWord As String
    Dim nFile, myFile As String
    Dim fExt As String
 
Set ws1 = ThisWorkbook.Sheets("30 FDSS VALIDATION")
Set ws3 = ThisWorkbook.Sheets("Baan ETB")

ws1.Unprotect password:="1234"
ws3.Unprotect password:="1234"

ws1.AutoFilterMode = False
ws3.AutoFilterMode = False

ws1.Rows("1:" & Rows.Count).Clear
ws3.Rows("1:" & Rows.Count).Clear
 
    Set wb = ThisWorkbook
    Set Inf = ThisWorkbook.Sheets("Info")
    fExt = "*.xlsx*"
 
    With wb.Sheets("Info")
    Inf.Select
        Path = .Range("E2") & Application.PathSeparator
        KeyWord = .Range("F2").Value
        xx = Range("G2").Value
    End With
 
        Dim filename As Workbook
     
ThisWorkbook.Activate
last_row = Range("F1048576").End(xlUp).Row
For s = 2 To last_row
File_Name = Range("F" & s).Value
     
Sfile = Dir("C:\ME Financial Report\FDSS_Report\*" & File_Name & "*.xl*")
Dfile = Dir("C:\ETB\TB_Report_by_Meps\*" & File_Name & "*.xl*")

If Sfile <> "" Then
   
Set fd = Workbooks.Open("C:\ME Financial Report\FDSS_Report\" & Sfile)
Set et = Workbooks.Open("C:\ETB\TB_Report_by_Meps\" & Dfile)

 
ThisWorkbook.Activate
ws1.Unprotect password:="1234"
ws3.Unprotect password:="1234"
fd.Worksheets(1).Cells.Copy ws1.Range("A1")
et.Worksheets(1).Cells.Copy ws3.Range("A1")

fd.Close SaveChanges:=False
et.Close SaveChanges:=False
 
Set wb = Nothing
Set et = Nothing
Call Validation_30_FDSS

Set wb = Nothing
Set et = Nothing
End If

Next
File_Name = ""
MsgBox "Done"

End Sub

Marc L · Sep 6, 2022

Which 'memory' ?
You must well elaborate what means 'validate & update the status in Excel' …

Gunasekaran · Sep 8, 2022

Hi Sir, I have completed my loop condition task based on the conversation above. My macro code seems to have skipped something while running the macro. Therefore, the "Interim Acc Validation Sheet" returns a blank result, even though the data is available in the "Interim Master Data Sheet".

Based on ETB Sheet Range("P02") Value Based, I need to update data in "Interim Acc Validation" Sheet Range("B4") from data of Interim Master Data Sheet.

In short, I'm trying to gather data from "Interim Master Data Sheet" based on "ETB Sheet" Range-"P02" Value Based On Update In "Interim Accounting Validation Sheet" in Range.B4"...

In case there is no data in the Master Data Sheet. Then it will output in this format. Kindly help................

If Data available in Interim account Master data, then Output like below

Kindly help me to recheck and give proper code this task Sir..

Code:

Sub Interim_Acc1()
Dim wss, wss1, wss2, wss3, wss4 As Worksheet
Set wss1 = ThisWorkbook.Sheets("Interim Acc Validation")
Set wss2 = ThisWorkbook.Sheets("Baan ETB")
Set wss4 = ThisWorkbook.Sheets("Info")
Set trg = ThisWorkbook.Sheets("Interim Master Data")

trg.AutoFilterMode = False

'Call Interim_Acc_Pull_From_Sql

Dim mep As String
wss2.Select
mep = wss2.Application.Trim(Range("P2").Value)

Set wss = ThisWorkbook.Sheets("Interim Master Data")
wss1.Activate
ActiveSheet.Unprotect password:="1234"
[A1].Select
Sheets("Interim Acc Validation").Range("A4:J100000").Clear
Rows(2).Clear

trg.Activate
trg.AutoFilterMode = False
lr = trg.Range("A" & Rows.Count).End(xlUp).Row

Dim mep_Name As String
On Error Resume Next
 
  With trg.Range("A1").CurrentRegion
        .AutoFilter Field:=1, Criteria1:=mep
        lr = trg.Range("A" & Rows.Count).End(xlUp).Row
    If lr > 2 Then
        
        trg.Range("B2:E" & lr).SpecialCells(xlCellTypeVisible).Copy wss1.Range("B4")
        .AutoFilter
    End If
    End With

On Error GoTo 0

wss.Activate
lr = wss1.Range("B" & Rows.Count).End(xlUp).Row
If lr <= 3 Then
wss.AutoFilterMode = False
'wss.Protect "1234", True, True
'wss.Visible = xlSheetVeryHidden
wss1.Activate
wss1.AutoFilterMode = False
wss1.Rows(1).ClearContents
wss1.Cells(2, 1).Activate
wss1.Range("D6") = "No INTERIM ACCOUNT"
wss1.Protect "1234", True, True

Application.CutCopyMode = False
Sheets("Info").Select
Exit Sub
Else
End If

Application.CutCopyMode = False

wss1.Activate

Sheets("Interim Acc Validation").Range("A1").Value = UCase(mep)

Cells(4, 6).Select
colrow = ThisWorkbook.Sheets("Baan ETB").Cells(1, Columns.Count).End(xlToLeft).Column

Dim i As Long
On Error Resume Next
For i = 4 To Range("B100000").End(xlUp).Row

If colrow = 11 Then

Range("F" & i).FormulaR1C1 = "=IFERROR(SUMIF('Baan ETB'!C[-4],'Interim Acc Validation'!RC[-4],'Baan ETB'!C[6]),0)"

Else
Range("F" & i).FormulaR1C1 = "=IFERROR(SUMIF('Baan ETB'!C[-4],'Interim Acc Validation'!RC[-4],'Baan ETB'!C[6]),0)"

End If

Next i
On Error GoTo 0
    Sheets("Interim Acc Validation").Select
With Range("A4:A" & Range("B" & Rows.Count).End(xlUp).Row)
.Cells(1, 1).Value = 1
.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Trend:=False
End With
Range("G4:H" & Rows.Count).Clear
Columns("I:AA").Clear

Application.ScreenUpdating = False
Application.DisplayAlerts = False
'wss1.Activate

Range("A3").CurrentRegion.Borders.Weight = XlBorderWeight.xlThin
Sheets("Interim Acc Validation").Cells.Font.Name = "Calibri"
Sheets("Interim Acc Validation").Cells.Font.Size = 9
Range("F4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Style = "Comma"

    lr = Range("F" & Rows.Count).End(xlUp).Row
    Range("F2").Formula = "=Subtotal(9,F4:F" & lr & ")"

Dim MR1 As Range
Set MR1 = Range("F2")
If MR1.Value <> 0 Then
MR1.Interior.ColorIndex = 3
Else
MR1.Interior.ColorIndex = 10
End If

Range("F2").Style = "Comma"

Application.CutCopyMode = False

wss1.Activate
ActiveSheet.AutoFilterMode = False
Rows(3).AutoFilter
Columns("G:XFD").Locked = False
ActiveSheet.Protect password:="1234", DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowSorting:=True, AllowFiltering:=True

Call Check_Sum_Total

Sheets("Info").Select
Range("A1").Select

End Sub

Gunasekaran · Sep 9, 2022

Marc L & vletm -

Kindly help me on this.

In short, I'm trying to copy and paste data from "Interim Master Data Sheet" based on "ETB Sheet" Range-"P02"Value.

Those copy data Update In "Interim Accounting Validation Sheet" in Range.B4" staring row... In case if No data available in "Master data sheet" then Just Update NO Interim account.

Both Output screen shot updated in above. Also Unable to attach. xlsm file here Sir.. Please help me this

vletm · Sep 9, 2022

Gunasekaran
If You could give Your expected details as well as answer to questions,
then there could be possible to give some even ideas.
Should Your #18 reply has connection with Your original thread?

Gunasekaran · Sep 9, 2022

vletm said:
Gunasekaran
If You could give Your expected details as well as answer to questions,
then there could be possible to give some even ideas.
Should Your #18 reply has connection with Your original thread?

this is part of this original thread.. if u need i will raise new thread sir. or if u ok i will give zoom link to check my process in few minutes. plz support me sir.

Gunasekaran · Sep 9, 2022

This above written my code, sometimes not copy and paste the data even if data available in "Master data Sheet". Thats why I raised this question here..

Gunasekaran · Sep 9, 2022

vletm said:
Gunasekaran
If You could give Your expected details as well as answer to questions,
then there could be possible to give some even ideas.
Should Your #18 reply has connection with Your original thread?

my expected answer,...

Copy and paste the data in "Active sheet" from "Master Account Sheet" if cell value is matched.

In case if No data available in "Master data sheet" then Just Update NO Interim account.

Gunasekaran · Sep 9, 2022

Any one can help me this.

Marc L · Sep 10, 2022

If you remove the On Error Resume Next codeline, which codeline raises an error ?

Marc L · Sep 10, 2022

According to the post #17 attachment an Excel basics VBA demonstration (v4) for starters

in order to copy the Interim Master Data worksheet matching rows to the Interim Acc Validation worksheet according to its A1 cell :

Code:

Sub Demo1()
        Const F = "F2", H = 3, P = "1234"
    With Sheet4.UsedRange.Rows
       .Parent.Unprotect P
       .Range(F).ClearContents
        If .Count > H Then .Item(H + 1 & ":" & .Count).EntireRow.Delete
        V = .Cells(1).Value
    End With
    With Sheet8.[A1].CurrentRegion.Columns
        If IsNumeric(Application.Match(V, .Item(1), 0)) Then
            .Item(1).AutoFilter 1, V
            .Item("B:E").Rows("2:" & .Rows.Count).Copy Sheet4.Cells(H + 1, 2)
            .AutoFilter
        End If
    End With
    With Sheet4.UsedRange.Rows
        If .Count > H Then
           .Item(H + 1 & ":" & .Count).Borders.Weight = 2
           .Range("A" & H + 1 & ":A" & .Count) = Evaluate("ROW(1:" & .Count & ")")
           .Range(F).Formula = "=SUM(F" & H + 1 & ":F" & .Count & ")"
        Else
           .Cells(H + 1, 4) = "No matching data …"
        End If
           .Parent.Protect P
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !

Search and Pick the File from Folder based on Cell value the Validate the Both Reports with Format

Member

Attachments

Excel Ninja

Member

Attachments

Excel Ninja

Member

Member

Excel Ninja

Member

Member

Excel Ninja

Member

Excel Ninja

Member

Excel Ninja

Member

Attachments

Excel Ninja

Member

Attachments

Member

Excel Ninja

Member

Member

Member

Member

Excel Ninja

Excel Ninja