none
Append data from multiple excel files

    Question

  • Hi,

    I have 50 excel files in one folder and have one master sheet. I want to append data of all 50 excel files in to my master file each week as these 50 excel files are updated each week. I want to append data of all 50 excel files in to the next available row in my master file. So the code should do the following task.

    1. Ask for the path (browse) to select a folder which has those 50 excel files saved in it.

    2.Take data of all 50 excel files one by one and append it to my master file say "master.xls"

    All 50 excel files and my master file has same number of columns and titles. I hope I am clear to this. I am beginner to vba. Please help me to build a code for this. 

    Please help me ...

     

     

     

    • Moved by Mark Liu-lxfModerator Thursday, January 12, 2012 3:11 AM VBA issue (From:Visual Basic General)
    Tuesday, January 10, 2012 10:46 PM

Answers

  • The best code depends on your version, but this should work with all versions.  Put the code into a standard codemoudle in an otherwise blank workbook. When the dialog comes up, just navigate to your folder and select one file.

    Bernie

     

    Option Explicit
    Sub Consolidate()
    ' Will consolidate Mulitple Sheets
    ' from Multiple Files onto one sheet
    ' Assumes that all data starts in cell A1 and
    ' is contiguous, with no blanks in column A


    Dim i As Integer
    Dim boolHeaders As Boolean
    Dim FileDummy As String
    Dim myPath As String
    Dim WorkFile As String
    Dim Basebook As Workbook
    Dim myBook As Workbook
    Dim mySheet As Worksheet
    Dim rCount As Long

    FileDummy = Application.GetOpenFilename(, , _
    "Just Select a file in your desired folder")

    If FileDummy = "False" Then
    MsgBox "You cancelled."
    Exit Sub
    End If

    i = InStrRev(FileDummy, "\")
    myPath = Left(FileDummy, i)
    MsgBox "I will apply the procedure to all files in the folder " _
    & Chr(13) & myPath

    With Application
    .DisplayAlerts = False
    .EnableEvents = False
    .ScreenUpdating = False
    End With

    boolHeaders = False

    Set Basebook = ThisWorkbook

    WorkFile = Dir(myPath & "*.xls")
    Do While WorkFile <> ""
    Application.StatusBar = "Now working on " & WorkFile

    Set myBook = Workbooks.Open(myPath & WorkFile)
    For Each mySheet In myBook.Worksheets
    rCount = rCount + mySheet.Range("A1").CurrentRegion.Rows.Count _
    - IIf(boolHeaders, 0, 1)
    If rCount >= Rows.Count Then
    ThisWorkbook.Sheets.Add After:=Worksheets(ThisWorkbook.Worksheets.Count)
    rCount = 0
    boolHeaders = False
    End If
    mySheet.Range("A1").CurrentRegion.Offset(IIf(boolHeaders, 1, 0)).Copy _
    Basebook.Worksheets(ThisWorkbook.Worksheets.Count).Cells(Rows.Count, 1) _
    .End(xlUp)(IIf(boolHeaders, 2, 1))
    boolHeaders = True
    Next mySheet
    myBook.Close

    WorkFile = Dir()
    Loop

    With Application
    .DisplayAlerts = True
    .EnableEvents = True
    .ScreenUpdating = True
    End With

    Basebook.SaveAs Application.GetSaveAsFilename

    Application.StatusBar = False

    End Sub


    HTH, Bernie
    Wednesday, January 11, 2012 4:59 PM
  • hi Bunty,
     
    Dim Ligne As Long, oFile As Object, oFolder As String
    Dim wk1 As Workbook, wk2 As Workbook
     
    Sub SelectFolder()
    Set wk1 = ThisWorkbook
    Dim fd As FileDialog
       Set fd = Application.FileDialog(msoFileDialogFolderPicker)
         With fd
            .AllowMultiSelect = False
            .Show
            oFolder = .SelectedItems(1)
            List_Files
         End With
    End Sub
    Sub List_Files()
       Ligne = 1
       Set fso = CreateObject("Scripting.FileSystemObject")
       Set sfoFolder = fso.getfolder(oFolder)
       Read_File1 sfoFolder
    End Sub
    Sub Read_File1(ByRef Dossier)
        Ligne = Ligne + 1
        For Each oFile In Dossier.Files
         If Right(oFile, 4) = ".xls" Then
            x = oFile
             Workbooks.Open oFile
            Set wk2 = ActiveWorkbook
                Ligne = Ligne + 1
               wk1.Sheet1.Cells(Ligne, 1) = wk2.Sheet1.Cells(1, 1)
             wk2.Close SaveChanges:=False
            Set wk2 = Nothing
          End If
        Next oFile
      For Each d In Dossier.SubFolders
        Read_File1 d
      Next
    End Sub
     --
    isabelle
     Le 2012-01-11 09:47, bunty_1244 a écrit :
    >
    > Hi,
    >
    > I have 50 excel files in one folder and have one master sheet. I want to append data of all 50 excel files in to my master file each week as these 50 excel files are updated each week. I want to append data of all 50 excel files in to the next available row in my master file. So the code should do the following task.
    >
    > 1. Ask for the path (browse) to select a folder which has those 50 excel files saved in it.
    >
    > 2.Take data of all 50 excel files one by one and append it to my master file say "master.xls"
    >
    > All 50 excel files and my master file has same number of columns and titles. I hope I am clear to this. I am beginner to vba. Please help me to build a code for this.
    >
    > Please help me ...
    >
    > --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    > Bunty
     
    Wednesday, January 11, 2012 8:40 PM

All replies

  • Hello,

    This forum is for VB in Visual Studio Net, try for your VBA question the ISVVBA forum

    http://social.msdn.microsoft.com/Forums/en-US/isvvba


    Success
    Cor
    Wednesday, January 11, 2012 9:54 AM

  • Hi,

    I have 50 excel files in one folder and have one master sheet. I want to append data of all 50 excel files in to my master file each week as these 50 excel files are updated each week. I want to append data of all 50 excel files in to the next available row in my master file. So the code should do the following task.

    1. Ask for the path (browse) to select a folder which has those 50 excel files saved in it.

    2.Take data of all 50 excel files one by one and append it to my master file say "master.xls"

    All 50 excel files and my master file has same number of columns and titles. I hope I am clear to this. I am beginner to vba. Please help me to build a code for this.

    Please help me ...


    Bunty
    Wednesday, January 11, 2012 2:47 PM
  • The best code depends on your version, but this should work with all versions.  Put the code into a standard codemoudle in an otherwise blank workbook. When the dialog comes up, just navigate to your folder and select one file.

    Bernie

     

    Option Explicit
    Sub Consolidate()
    ' Will consolidate Mulitple Sheets
    ' from Multiple Files onto one sheet
    ' Assumes that all data starts in cell A1 and
    ' is contiguous, with no blanks in column A


    Dim i As Integer
    Dim boolHeaders As Boolean
    Dim FileDummy As String
    Dim myPath As String
    Dim WorkFile As String
    Dim Basebook As Workbook
    Dim myBook As Workbook
    Dim mySheet As Worksheet
    Dim rCount As Long

    FileDummy = Application.GetOpenFilename(, , _
    "Just Select a file in your desired folder")

    If FileDummy = "False" Then
    MsgBox "You cancelled."
    Exit Sub
    End If

    i = InStrRev(FileDummy, "\")
    myPath = Left(FileDummy, i)
    MsgBox "I will apply the procedure to all files in the folder " _
    & Chr(13) & myPath

    With Application
    .DisplayAlerts = False
    .EnableEvents = False
    .ScreenUpdating = False
    End With

    boolHeaders = False

    Set Basebook = ThisWorkbook

    WorkFile = Dir(myPath & "*.xls")
    Do While WorkFile <> ""
    Application.StatusBar = "Now working on " & WorkFile

    Set myBook = Workbooks.Open(myPath & WorkFile)
    For Each mySheet In myBook.Worksheets
    rCount = rCount + mySheet.Range("A1").CurrentRegion.Rows.Count _
    - IIf(boolHeaders, 0, 1)
    If rCount >= Rows.Count Then
    ThisWorkbook.Sheets.Add After:=Worksheets(ThisWorkbook.Worksheets.Count)
    rCount = 0
    boolHeaders = False
    End If
    mySheet.Range("A1").CurrentRegion.Offset(IIf(boolHeaders, 1, 0)).Copy _
    Basebook.Worksheets(ThisWorkbook.Worksheets.Count).Cells(Rows.Count, 1) _
    .End(xlUp)(IIf(boolHeaders, 2, 1))
    boolHeaders = True
    Next mySheet
    myBook.Close

    WorkFile = Dir()
    Loop

    With Application
    .DisplayAlerts = True
    .EnableEvents = True
    .ScreenUpdating = True
    End With

    Basebook.SaveAs Application.GetSaveAsFilename

    Application.StatusBar = False

    End Sub


    HTH, Bernie
    Wednesday, January 11, 2012 4:59 PM
  • hi Bunty,
     
    Dim Ligne As Long, oFile As Object, oFolder As String
    Dim wk1 As Workbook, wk2 As Workbook
     
    Sub SelectFolder()
    Set wk1 = ThisWorkbook
    Dim fd As FileDialog
       Set fd = Application.FileDialog(msoFileDialogFolderPicker)
         With fd
            .AllowMultiSelect = False
            .Show
            oFolder = .SelectedItems(1)
            List_Files
         End With
    End Sub
    Sub List_Files()
       Ligne = 1
       Set fso = CreateObject("Scripting.FileSystemObject")
       Set sfoFolder = fso.getfolder(oFolder)
       Read_File1 sfoFolder
    End Sub
    Sub Read_File1(ByRef Dossier)
        Ligne = Ligne + 1
        For Each oFile In Dossier.Files
         If Right(oFile, 4) = ".xls" Then
            x = oFile
             Workbooks.Open oFile
            Set wk2 = ActiveWorkbook
                Ligne = Ligne + 1
               wk1.Sheet1.Cells(Ligne, 1) = wk2.Sheet1.Cells(1, 1)
             wk2.Close SaveChanges:=False
            Set wk2 = Nothing
          End If
        Next oFile
      For Each d In Dossier.SubFolders
        Read_File1 d
      Next
    End Sub
     --
    isabelle
     Le 2012-01-11 09:47, bunty_1244 a écrit :
    >
    > Hi,
    >
    > I have 50 excel files in one folder and have one master sheet. I want to append data of all 50 excel files in to my master file each week as these 50 excel files are updated each week. I want to append data of all 50 excel files in to the next available row in my master file. So the code should do the following task.
    >
    > 1. Ask for the path (browse) to select a folder which has those 50 excel files saved in it.
    >
    > 2.Take data of all 50 excel files one by one and append it to my master file say "master.xls"
    >
    > All 50 excel files and my master file has same number of columns and titles. I hope I am clear to this. I am beginner to vba. Please help me to build a code for this.
    >
    > Please help me ...
    >
    > --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    > Bunty
     
    Wednesday, January 11, 2012 8:40 PM
  • Hi bunty_1244,

    I find you have created a same thread in Visual Basic forum. I am sorry that duplicate thread is not support here. For better support, I will merge this thread to another.

    Sorry for any inconvenience and have a nice day.


    Mark Liu-lxf [MSFT]
    MSDN Community Support | Feedback to us
    Thursday, January 12, 2012 3:09 AM
  • please check, here! we are in the ISVVBA forum, thank you for confirm.
    look at this picture to see what i means :  http://cjoint.com/?BAmfmkGNycY
     --
    isabelle
     
    Le 2012-01-11 04:54, Cor Ligthert [MVP] a écrit :
    > Hello,
    >
    > This forum is for VB in Visual Studio Net, try for your VBA question the ISVVBA forum
    >
    >
    > --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    > Success
    > Cor
     
    Thursday, January 12, 2012 4:14 AM
  • or this one to convince you : http://cjoint.com/?BAmfuk86Aed
     
    --
    isabelle
      Le 2012-01-11 23:14, isabelleV a écrit :
    > please check, here! we are in the ISVVBA forum, thank you for confirm.
    > look at this picture to see what i means : http://cjoint.com/?BAmfmkGNycY
    > --
    > isabelle
    > Le 2012-01-11 04:54, Cor Ligthert [MVP] a écrit :
    >  > Hello,
    >  >
    >  > This forum is for VB in Visual Studio Net, try for your VBA question the ISVVBA forum
    >  >
    >  >
    >  > --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    >  > Success
    >  > Cor
     
    Thursday, January 12, 2012 4:20 AM
  • Hi Isabelle,

    The thread is moved from the VB general forum to here (VBA Forum) by the Moderators, so we are now in the correct Forum.

     

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Thursday, January 12, 2012 4:31 PM
  • ok i understand better now, thank you Daniel for this return
     
    --
    isabelle
     Le 2012-01-12 11:31, danishani a écrit :
    > Hi Isabelle,
    >
    > The thread is moved from the VB general forum to here (VBA Forum) by the Moderators, so we are now in the correct Forum.
    >
    > Hope this helps,
    >
    > --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    > Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
     
    Thursday, January 12, 2012 8:17 PM