Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Loop through excel files in a directory and copy onto master sheet

    Hi All,

    I have around 10-15 excel files in a folder which are refreshed each week. I need to loop through these files and copy the data form them into one master sheet.

    I plan to create the macro in a mast file called Pipeline Consolidated and run it form this workbook so don’t want this file to open.

    I have the code once the additional workbook is open to select the cells and then copy them back to my master sheet, my problem is how to loop through all the excel file in the directory.

    So far I have to put the file name in and repeart this step each time.

    Path = "S:\Reporting\Pipeline\Submitted by departments\Pipeline template imp.XLSX"
    Workbooks.Open Filename:=Path
    '''''End of Open Workbook

    ‘’’’’’’’’’’’’’’copies form the additioanal workbook’’’’’’’’’’’’’’’’’’
    Sheets("Pipeline").Select
    If ActiveSheet.AutoFilterMode = True Then
    ActiveSheet.AutoFilterMode = False
    End If
    LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
    Range("A4:J" & LastRow).Select
    Selection.Copy

    Windows("Pipeline Consolidated.xlsm").Activate


    '''selects the sheet name on the Consolildated workbook and copies the data
    Sheets("Pipeline Consolidated").Select
    Range("A4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False


    I have been searching google for ages but as i am new to VBA and have learnt from you guys and myself it is very difficult as there are alot of complex answers when i was hoping it would be quite simple.

    REgards,

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    From your code it looks like you are using Excel2007 or later?
    Can you attach an empty sample datafile, with just the headings?
    Can you give us an idea of the filenames you are using for the 10-15 excel files?

    I can give you a merge tool that will do what you want.

    zeddy

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Stimp,

    Try wrapping the following code around your macro. It will open each Excel file in a directory, run your macro, then close the workbook and cycle to the next file. After the last file is closed, the macro will end. Replace the path with your correct path.

    I had this code stuffed in my bag of tricks. Don't know who to give the credit to.

    HTH,
    Maud

    Code:
    Public Sub test()
    'DECLARE AND SET VARIABLES
    Dim wbk As Workbook
    Dim Filename As String
    Dim Path As String
    Path = "C:\Users\Maudibe\Desktop\ExcelFiles\"
    Filename = Dir(Path & "*.xlsm")
    '--------------------------------------------
    'OPEN EXCEL FILES
     Do While Len(Filename) > 0  'IF NEXT FILE EXISTS THEN
        Set wbk = Workbooks.Open(Path & Filename)
        '
        ' CODE GOES HERE
        '
         MsgBox Filename & " has opened"
        wbk.Close True
        Filename = Dir
    Loop
    End Sub

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    Although the code from Maudibe will loop through all files in a specified folder, I would advise against this method for merging data into a consolidated file.
    The reason is it is very easy for Users to place multiple copies of data files into such a folder, and you wouldn't want to 'double-count' records etc. by processing all files in a folder.
    Your code would need to test for such cases.
    Also, your code would need to test each file is of the 'expected' type i.e. contains the 'expected data' etc.
    (You could use a 'file-naming' convention to deal with this)
    It would be better to have a specified list of pre-defined 'source' files.
    I have a merge tool that will process such data, but just require a small sample datafile which has the data headings in.

    zeddy

  5. #5
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Hi Maud, thanks for the attached.

    Hi Zeddy,

    Please find the attached file as requested.

    The headings will be the same in each file, I can have the file names saved so we can always check for them files, although sometimes the files may not be there or may be blank so I guess an if file exists may work.

    Once I have all the data together I can then continue to put my code on there to convert all values into a single currency etc.

    Kind regards,
    Attached Files Attached Files

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    Does each datafile include multi-currency records, or is it one currency per datafile?
    Just asking, because the merge process can also provide certain 'summary' info, for each datafile merged.

    I'll post the merge file tool later today, now that I have your datafile template.

    zeddy

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    I have attached two files:
    [rzDeptMergeTool-v1.00.xlsm]
    [BlankPipelineConsolidated.zip]

    Save the .xlsm file to a folder of your choice.
    Unzip the [BlankPipelineConsolidated.zip] file, and place the binary .xlsb file into the same folder as the .xlsm file.
    (binary .xlsb file cannot be uploaded to this forum, unfortunately)

    1. Open the [rzDeptMergeTool-v1.00.xlsm] file.
    2. Enable Content (i.e. enable macros)
    3. In cell [F3], enter the folder location of your datafiles.
    4. Enter the Department names in the Dept. List (in range [E16:E115] )
    (provision has been made for up to 100 entries - this can be amended easily)
    5. The corresponding datafile names in adjacent column [G] use a formula to determine the name.
    (adjust the formulas in [G16:G115] to suit your naming convention, or just enter the actual filename.

    6. make sure you have some sample datafiles in your folder (as specified in cell [F3] )
    7. Click the button labelled [click here to Merge data files..]

    Watch as the status block is updated as each file is merged.

    Switch Windows to view the consolidation file.

    The process can be amended to import directly into a sheet in this processing tool etc etc.

    Please let me know how you get on with it.

    zeddy
    Attached Files Attached Files

  8. The Following User Says Thank You to zeddy For This Useful Post:

    MITCHETY (2014-09-08)

  9. #8
    New Lounger
    Join Date
    Apr 2014
    Posts
    3
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I have a question related to this thread. I too have a large number of files that have to be batch processed every week. I am fairly new to VBA.

    The data comes in a csv file and is always in the same format. I have written a macro to clean up the data I need and place it in a section of the file so that I can copy it and paste it to another spreadsheet where I collate all the data from all the files. But I don't know how to write the following:

    1.Take the data I have just extracted, copy it, paste it to the end of a specific worksheet of a specific workbook
    2.Save that workbook
    3.Go back to the csv file I copied the data from. Close it (do not save the csv) and move onto the next file and repeat until all files in that folder are processed.

    I have been doing some research on the net and come up with the following code:

    Sub Cleanmyclaims()
    Dim file
    Dim path As String

    path = "c:\users\paul\skydrive\documents\bam\hawken\claim s\"

    file = Dir (path & "*.csv")
    Do While file<> ""
    Workbooks.Open Filename:=path & file'

    'Here is where I am not sure if I have written the correct code. I want to run the macro "claims" that is in my personal.xlsb

    Call Claims

    'This is where I do not know how to copy the data to the end of Sheet1 of the following file ("C:\users\paul\skydrive\documents\bam\hawken\clai ms\Hawken - claims register.xlsx"). Go back to the csv file close it and then move onto the next file in the directory.

    file = Dir()
    Loop End Sub

    Any help would be greatly appreciated!

    Regards,

    Paul

  10. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Looks very similar to the code in post #3.

    Can you post a sample of your CSV file with the "cleaned up data" located in the section of the file you speak of? Will also need a sample copy of your second spreadsheet containing your macro and indicating where you want the data copied to.

    Maud
    Last edited by Maudibe; 2014-04-16 at 20:07.

  11. The Following User Says Thank You to Maudibe For This Useful Post:

    pwanis (2014-04-18)

  12. #10
    New Lounger
    Join Date
    Apr 2014
    Posts
    3
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Claims Macro.zip

    Thanks Maud for your offer of help. Overnight, I managed to find the code to copy the data into the new spreadsheet, go back to the previous window and close the csv file!

    Now I just need the code to open a file, execute a macro, close the file and move onto the next in the folder. Could I use the code that you put in Post 3 to do this?

    As requested, I have attached:

    • A couple of raw csv files
    • A processed csv
    • The target spreadsheet for the data and
    • A file containing the macro that I have written.


    Any assistance you can offer is greatly appreciated!

  13. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Pwanis,

    The answer is yes. You will need some method of calling the macro "test". It can be a button, short cut keys, selection from macro list, or an event. You will also need to change the path to the folder where your files are located (line 6 in the code). In the section of the code, "CODE GOES HERE", replace with the call to your macro. Although there are multiple ways to call a macro from within code, the 2 easiest ways are:
    1. Using the Call statement: Call MACROname (parameter1, parameter 2)
    2. Without the Call statement: MACROname parameter1, parameter2

    Code:
    Public Sub test()
    'DECLARE AND SET VARIABLES
    Dim wbk As Workbook
    Dim Filename As String
    Dim Path As String
    Path = "C:\Users\Maudibe\Desktop\ExcelFiles\"  'CHANGE PATH
    Filename = Dir(Path & "*.csv")
    '--------------------------------------------
    'OPEN EXCEL FILES
     Do While Len(Filename) > 0  'IF NEXT FILE EXISTS THEN
        Set wbk = Workbooks.Open(Path & Filename)
        '
        ' CODE GOES HERE
        '
         MsgBox Filename & " has opened"  'OPTIONAL- CAN COMMENT OUT
        wbk.Close True
        Filename = Dir
    Loop
    End Sub
    If you can define which sections (cell addresses) of data you wish to move in the .csv files and indicate where you want it placed in spreadsheet (.xlsx), I could help you further.

    Maud

  14. The Following 2 Users Say Thank You to Maudibe For This Useful Post:

    pwanis (2014-04-18),Sbv Hoang (2015-12-05)

  15. #12
    New Lounger
    Join Date
    Apr 2014
    Posts
    3
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Maud,

    This worked perfectly!!! You are a legend and I really appreciate your help. If you are ever in Melbourne, Australia, contact me and I'll shout you a beer!

    Thanks again.

    Regards,

    Paul

  16. #13
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    You're on bro!

  17. #14
    New Lounger
    Join Date
    Oct 2015
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Maudibe,

    Sorry to bumps this thread, but thought it was relevant to the code you posted..

    I have just come across your code that does the job, but I was wondering if there's any way to pause the macro so that then user can check the spreadsheet that opens up with the macro, before the macros is resumed and the maintenance work is carried out, in my case I am just deleting empty rows, but not until I checked them first.

    IF the user can take control of the spreadsheet in between the macros then I can see endless possibilities..

    Many thanks for sharing this code..


    Gangsta



    Quote Originally Posted by Maudibe View Post
    Stimp,

    Try wrapping the following code around your macro. It will open each Excel file in a directory, run your macro, then close the workbook and cycle to the next file. After the last file is closed, the macro will end. Replace the path with your correct path.

    I had this code stuffed in my bag of tricks. Don't know who to give the credit to.

    HTH,
    Maud

    Code:
    Public Sub test()
    'DECLARE AND SET VARIABLES
    Dim wbk As Workbook
    Dim Filename As String
    Dim Path As String
    Path = "C:\Users\Maudibe\Desktop\ExcelFiles\"
    Filename = Dir(Path & "*.xlsm")
    '--------------------------------------------
    'OPEN EXCEL FILES
     Do While Len(Filename) > 0  'IF NEXT FILE EXISTS THEN
        Set wbk = Workbooks.Open(Path & Filename)
        '
        ' CODE GOES HERE
        '
         MsgBox Filename & " has opened"
        wbk.Close True
        Filename = Dir
    Loop
    End Sub

  18. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Gangsta

    ..perhaps you just need to re-locate Maud's message box, like this..

    Code:
    Public Sub test()
    'DECLARE AND SET VARIABLES
    Dim wbk As Workbook
    Dim Filename As String
    Dim Path As String
    Path = "C:\Users\Maudibe\Desktop\ExcelFiles\"
    Filename = Dir(Path & "*.xlsm")
    '--------------------------------------------
    'OPEN EXCEL FILES
     Do While Len(Filename) > 0  'IF NEXT FILE EXISTS THEN
        Set wbk = Workbooks.Open(Path & Filename)
        '
        MsgBox Filename & " has opened. Check it now, and then click here to continue processing.."
        ' CODE GOES HERE
        '
         
        wbk.Close True
        Filename = Dir
    Loop
    End Sub
    zeddy

Page 1 of 3 123 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •