Announcement

Collapse
No announcement yet.

VBA to extract & combine data from multiple excel file from a specific folder

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA to extract & combine data from multiple excel file from a specific folder



    conditions:-
    a long list of excel files in FIXED folder (eg. C Drive: Data Folder)
    no fix number of files
    file comes in different file names
    file come in different worksheet name.

    how to have a VBA to automatically:-
    1) pull the data from this files with a FIXED column range but row will depends on each file
    2) combine it to one excel & auto save it

  • #2
    Re: VBA to extract & combine data from multiple excel file from a specific folder

    Hi Luvnet

    This is a very commonly asked question on this and other forums. THere is a lot of information not supplied. Like where is the data stored in the opened files, where is it going to, as single column or multiple columns. Asking the right questions leads to a more specific answer. Here is some generic code which should do what you wish. If you can manipulate it from your end all the better.

    Take care

    Smallman

    Code:
    Option Explicit
    Sub OpenFile()
        Dim sPath As String
        Dim sFil As String
        Dim strName As String
        Dim twbk As Workbook
        Dim owbk As Workbook
        Dim ws As Worksheet
        
        Set twbk = ActiveWorkbook
        sPath = "C:\Data Folder\" 'Change to suit
        sFil = Dir(sPath & "*.xls")
    
        Do While sFil <> ""
            strName = sPath & sFil
            Set owbk = Workbooks.Open(strName)
            Set ws = owbk.Sheets(1)
            ws.Range("A1", Range("A" & Row.Count).End(xlUp)).Copy
            twbk.Sheets(1).Range("A65536").End(xlUp)(2).PasteSpecial xlPasteValues
            owbk.Close False 'Close no save
            sFil = Dir
        Loop
    
    twbk.Save
    End Sub
    sigpicthesmallman.com .......... Excel Dashboards ............ Excel Infographicsg..........k

    Comment


    • #3
      Re: VBA to extract &amp; combine data from multiple excel file from a specific folder

      Folder Path - "C:\Users\Chau_Yee_Wong\Desktop\SI Orders"
      File type might be .xls or .xlsx
      Data to copy from each file is From A19 to J19
      Rows will be different for each file. Normally i use Relative Reference+End+Down after selecting the Col to range the visible row
      Combining data from all files available on that folder into 1 new file. Do the data should have 10col

      one new request.
      the excel file that been saved onto the folder will surely have a Date Modified details (Date & Time). can this details be populated onto excel file.
      meaning when i open the 1stFile, will copy the above range (eg A19:J21) & paste onto a new file. then add in the Date & Time details of 1stFile onto Col L.
      Then open 2ndFile from the folder, again go to the same col & relative ref down for the number of rows available (A19:J28), copy & paste it onto 1 row after 1stFile (A22:J31) & add the date & time details.
      continue this steps until all the file been open & copied the data onto the specific new file.

      Comment


      • #4
        Re: VBA to extract &amp; combine data from multiple excel file from a specific folder

        note - all the files might have different worksheet name ^_^

        Comment


        • #5
          Re: VBA to extract &amp; combine data from multiple excel file from a specific folder

          Hi

          All of the items you requested on Post 3 are possible. This however

          note - all the files might have different worksheet name ^_^
          does concern me somewhat. Is the sheet object name the same in each workbook, for example is the data to collect on Sheet1? If not perhaps there is only 1 sheet in each file? Failing that is there some characteristic about the names on the workbooks which is unique? If the answer is no to all of those questions then there is nothing I can do to help. There has to be a unique item about the sheet you want to import.

          Take care

          Smallman
          Last edited by Smallman; August 31st, 2012, 10:44. Reason: tpyo
          sigpicthesmallman.com .......... Excel Dashboards ............ Excel Infographicsg..........k

          Comment


          • #6
            Re: VBA to extract &amp; combine data from multiple excel file from a specific folder

            can do a rename of the active worksheet whenever the file is been open, save it onto original file & then only pull the the data out.
            no concern if to rename the worksheet. but original worksheet name will be different when 1st open.
            thanks in advance for helping.

            Comment


            • #7
              Re: VBA to extract &amp; combine data from multiple excel file from a specific folder

              In that case then the sample code I provided should work for you. However you have not answered the questions I posed on Post 2. Answer these or better, much better provide a workbook. That gives me all the information I need and you don't need to say anything.

              Take care

              Smallman
              sigpicthesmallman.com .......... Excel Dashboards ............ Excel Infographicsg..........k

              Comment


              • #8
                Re: VBA to extract &amp; combine data from multiple excel file from a specific folder

                how do i attach file to you?

                Comment


                • #9
                  Re: VBA to extract &amp; combine data from multiple excel file from a specific folder

                  Hi

                  Click on Go Advanced - use the paperclip and follow directions. Note your file needs to be less than 100kb. Just take some data out if it is.

                  I am about to fly down the East Coast of Oz so will probably be off line for a day or two so, appologies if i am unable to respond quickly.

                  Take care

                  Smallman
                  sigpicthesmallman.com .......... Excel Dashboards ............ Excel Infographicsg..........k

                  Comment


                  • #10
                    Re: VBA to extract &amp; combine data from multiple excel file from a specific folder

                    file attached.
                    Attached Files

                    Comment


                    • #11
                      Re: VBA to extract &amp; combine data from multiple excel file from a specific folder

                      attached
                      Attached Files

                      Comment


                      • #12
                        Re: VBA to extract &amp; combine data from multiple excel file from a specific folder

                        i cant attache another 2 files but basically the rules is the same.

                        Comment


                        • #13
                          Re: VBA to extract &amp; combine data from multiple excel file from a specific folder

                          Hi Luvnet

                          I have had a look at your files and there is something else you need to do. The Template sheets which are being consolidated have a range merged from B2 to about B16. I recommend you unmerge these cells. If you do that then you can use the following code. If not you can not. Tested on my machine with unmerged cells.

                          Oh there is the issue of the date and time. Where pray am I ment to get this data from it is not visible in any cell.

                          Take care

                          Smallman

                          Code:
                          Option Explicit
                          Option Base 1
                          Sub OpenFile()
                              Dim ar As Variant
                              Dim ar2 As Variant
                              Dim arr(1 To 3) As String
                              Dim sPath As String
                              Dim sFil As String
                              Dim strName As String
                              Dim twbk As Workbook
                              Dim owbk As Workbook
                              Dim i As Integer
                              Dim lw As Long
                          
                              ar2 = Array("B2", "B4", "B13")
                          
                              Set twbk = ActiveWorkbook
                              sPath = "C:\Users\Peter\Documents\Test\" 'Change to suit
                              sFil = Dir(sPath & "*.xls")
                               
                              Do While sFil <> ""
                                  strName = sPath & sFil
                                   Set owbk = Workbooks.Open(strName)
                                      ar = Range("A19", Range("A" & Rows.Count).End(xlUp).Resize(, 10))
                                          For i = 1 To 3
                                              arr(i) = Range(ar2(i))
                                          Next i
                                      owbk.Close False  'Close no save
                                     lw = Range("A65536").End(xlUp).Row + 1
                                    Range(Cells(lw, 1), Cells(lw + UBound(ar) - 1, 10)) = ar 'A to J
                                   Range(Cells(lw, 11), Cells(lw + UBound(ar) - 1, 13)) = arr 'K to to M
                                  Range(Cells(lw, 16), Cells(lw + UBound(ar) - 1, 16)) = sFil 'P File name
                                 sFil = Dir
                              Loop
                          End Sub
                          sigpicthesmallman.com .......... Excel Dashboards ............ Excel Infographicsg..........k

                          Comment


                          • #14
                            Re: VBA to extract &amp; combine data from multiple excel file from a specific folder

                            hi Smallman,
                            thanks for the code. it work well on merged cells.

                            But i still have some concern on the below:-
                            1) I do not need the file name (Col P) but i need the Excel File modified date & time
                            2) if i want to reduce the Col (now A to J), how can i adjust it to only Col A and Col I? How do i amend the code?

                            Comment


                            • #15


                              Re: VBA to extract &amp; combine data from multiple excel file from a specific folder

                              urgent help pls..... Smallman
                              can add in the excel file created details (date & time) on your code?

                              Comment

                              Working...
                              X