Vba extract data from multiple files into multiple worksheets

[Rodrigues]

Hi all
I'm wondering if someone could help me please with vba code.
Have multiple.csv files (24 per day) on the same folder and would like to extract data into a Master file.
The data from each ,csv file needs to be pasted into the Master file on each worksheet.
Each day we'll have 24.csv files and a master file I.e.: 21062017 ; 22062017...

Folder: C:\PA
.csv files :
2017-06-21 Batch Yield Report 0600-0659.csv
2017-06-21 Batch Yield Report 0700-0759.csv
Master file: Master.xlsm

So, on 21/06/17 the data from file name:
2017-06-21 Batch Yield Report 0600-0659.csv, needs to be pasted into Master 21062017 Sheet1 ;
2017-06-21 Batch Yield Report 0700-0759.csv, needs to be pasted into Master Sheet2 ;

on 22/06/17 the data from file name:
2017-06-22 Batch Yield Report 0600-0659.csv needs to be asted into Master 22062017 Sheet1 and so on
I'm using a code, I use on another project, however cpoies all files into sheet1 regardless name or date, tried to manipulate the code to achive this and add "& T1&" (for date as part of the file name) without success.

Any help, sugestion will be appreciated.
Thanks in advance.
R

 

[SirJB7]

Hi, Rodrigues!

Could you please clarify to which master file worksheet should be copied any of the 24 daily files, and what should be done with the existing information in target worksheets, if any?

In the meanwhile check if this is what you're looking for:

Sub LoopThroughDirectory()

    Application.AskToUpdateLinks = False
    Application.DisplayAlerts = False
   
    Dim MyFile As String, rng As Range, wb As Workbook
    Dim lrow As Integer
    MyFile = Dir("c:\PA\*.csv*")
        Do While Len(MyFile) > 0
            If MyFile = "Master 21062017.xlsm" Then
                Exit Sub
            End If
            Set wb = Workbooks.Open("C:\PA\" & MyFile)
                   
                With wb.Sheets(1)
                    lrow = .Cells(Rows.Count, 1).End(xlUp).Row
                    Set rng = .Range("A1:Q" & lrow)
                    rng.Copy Workbooks("Master 21062017.xlsm").Sheets(Val(Left(MyFile, 2))).Cells(Rows.Count, 1).End(xlUp)(1)
                End With
            wb.Close
            MyFile = Dir
        Loop
       
    Application.AskToUpdateLinks = True
    Application.DisplayAlerts = True
       
End Sub

Regards!

 

[AlanSidman]

You will need to set up either if - then statements to determine what the file name is that you wish to copy or
You will need to set up Case Select statements to determine the same.
Once the file name is determined using either of those two means you will then need to direct the paste statement to the correct sheet.

To understand Case Select, look at this link.

https://www.techonthenet.com/excel/formulas/case.php

It appears that you already understand IF-Then as you have applied it already to determine the Master File.

 

[SirJB7]

Hi, Rodrigues!

Regarding what AlanSidman wrote, replace the last Dim statement by:

    Dim lrow As Integer, iDay As Integer
  
    iDay = InputBox("Day to be processed:", "Day")
    MyFile = Dir("c:\PA\Master " & Format(iDay, "00") & Format(Now(), "mm") & Year(Now()) & ".xslm")
    If MyFile = "" Then Exit Sub

And remove the first If...EndIf within the Do...Loop.

Adjust the reference to the master file as required in the below sentences.

Regards!

 

[Rodrigues]

SirJB7 and Alan Sidman, firstly thank you so much for help me on this.
Have been working/testing this and probably I have complicated to much.
The reason behind to copy all the info from csv files is that, on sheet2 (new file attached named NewMaster.xlsm), I have some formulas to extract data from sheet1, which then generate more organized/detailed data.
I'm struggled with 3 points here:
-after add on sheet 2 the formulas the .xlsm file takes ages to read & past the data onto sheet1; if I remove formulas it's prety quick (tested with 744 csv).
-formulas (examples on sheet2 rows 17&45 highlighted in yellow) won't work after add

(Sheet1!$B$4:$B$1000000=C3)

. , highlighted in green are the expected results, if data is imported to NewMAster on each individual sheet by day, I don't need the code above to look trhough the date.
-then everytime before import should clear the data to avoid duplications?
So, I'm not sure how to archive this, if you could please advise or have a different better approach I will aprecite.
Thanks
R