Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 8th, 2007, 05:43 AM
Registered User
Join Date: Nov 2007
Location: Jaipur, rajasthan, India.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Merge many excel files into one excel files

1. I have a master file (master.xls), currently empty
2. I have 80 excels files (all look a like but their worksheet name are different)
3. I would like all the data from these worksheet to be appended to master.xls with a click of button.
4. I tried using some codes from this forum but they did not work.
5. All the source file starts with ums bla bla.xls
6. please if possible send me macro code to perform this operation.

Please suggest.

Reply With Quote
  #2 (permalink)  
Old November 9th, 2007, 11:36 AM
Friend of Wrox
Points: 513, Level: 8
Points: 513, Level: 8 Points: 513, Level: 8 Points: 513, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Feb 2007
Location: Davenport, IA, USA.
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts

Do you want to append all files without first checking for duplicates? If you need to check for duplicates you'll need to cycle through each source sheet 1 row at a time and will need to modify the code below for that.

Put all source files in the same folder. In this case, in C:\SourceXLS:
Private Sub PullFiles_Button_Click()

'Processes worksheets in sDir copying them to FullList Tab
  Dim oTarget As Worksheet, oSource As Worksheet, sOpenWB As String, sThisWB As String
  Dim sDir As String, iStartRow As Long, bNoError As Boolean
  sThisWB = ActiveWorkBook.FullName
  sThisWB = Right(sThisWB, Len(sThisWB) - Len(ActiveWorkbook.Path) - 1)
  sDir = "C:\SourceXLS"
  Set oTarget = Workbooks(sThisWB).Worksheets("FullList")
  sOpenWB = Dir(sDir & "\*.xls") 'Gets First file from directory of all *.xls files in sDir folder
  If sOpenWB = sThisWB Then sOpenWB = Dir 'Gets Next File if current is this master workbook
  if sOpenWB = "" Then
    MsgBox "No XLS files found in folder or bad path",,No Data Files Found"
    Exit Sub
  End If
  iStartRow = 2 'Pastes starting at row 2 assuming that titles are row 1
  Do While sOpenWB <> ""
    bNoError = OpenSource(sDir & "\" & sOpenWB)
    If Not bNoError Then
      MsgBox "Error Opening Workbook " & sDir & "\" & sOpenWB & ", check path / Filename and try again", _
        "File Open Error"
      Exit Sub 'Halts processing on first failure to read file that should exist
    End If
    Set oSource = Workbooks(sOpenWB).ActiveSheet 'Assumes active sheet on opening workbook is only 1 to copy
    oSource.Range("A2:G2").Select 'Assumes Data is in columns A to G and data starts in row 2 no empty rows
    oSource.Selection.End(xlDown).Select 'Selects all records going down until empty row
    oSource.Selection.Copy Destination:=oTarget.Cells(iStartRow, 1)
    iStartRow = oTarget.Range("A2").End(xlDown).Row
    Workbooks(sOpenWB).Close SaveChanges:=False
    sOpenWB = Dir 'Gets Next File
    If sOpenWB = sThisWB Then sOpenWB = Dir 'Gets Next File if current is this master workbook

End Sub

Private Function OpenSource(sToOpen As String) As Boolean

'Tries to open file and returns false if fails
  On Error GoTo FailedOpenSource
    Workbooks.Open FileName:=sToOpen
  On Error GoTo 0
  OpenSource = True

End Function

This is pretty simplistic but it should point you in the right direction.

Hope this helped.

Reply With Quote
  #3 (permalink)  
Old November 14th, 2007, 06:23 PM
Registered User
Join Date: Jan 2005
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to milindsaraswala Send a message via Yahoo to milindsaraswala

I need same solution but their is slightly change what i need it should update row value. I mean in source excel sheet it will some data in column which will not have in destination file. I want merge that as well as add new rows which is not available in sheet. And their will be 1 column in destination file which will work as unique value.

Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Generating Excel FIles from resource Files abinashpatra ASP.NET 1.0 and 1.1 Basics 0 July 28th, 2008 02:07 AM
export excel files collie ASP.NET 1.x and 2.0 Application Design 2 January 3rd, 2008 09:09 AM
Comparing two different excel files anup.bihani Excel VBA 3 December 4th, 2006 06:00 AM
Excel files Ghost ajindal General .NET 0 January 19th, 2005 08:40 AM
Opening Excel and Excel files dinosaur_uk VB.NET 2002/2003 Basics 3 September 17th, 2004 03:22 AM

All times are GMT -4. The time now is 05:48 PM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.