Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Collate data from different Excel files

This is a discussion on Collate data from different Excel files within the Excel Questions forums, part of the Question Forums category; Hi, I'm new to this board. Here's my situation:- I have multiple excel files with a form popping up whenever ...

  1. #1
    New Member
    Join Date
    Jun 2007
    Posts
    10

    Default Collate data from different Excel files

    Hi, I'm new to this board. Here's my situation:- I have multiple excel files with a form popping up whenever these files are opened (form has been called in Workbook open function). I need to collate data from these multiple excel files without opening these files. Can somebody help with a sample code ??????

  2. #2
    Board Regular
    Join Date
    Feb 2006
    Posts
    3,437

    Default Re: Collate data from different Excel files

    Hi
    Need more details. see this link for a starter
    Appending/Importing data from multiple files.
    Ravi

  3. #3
    New Member
    Join Date
    Jun 2007
    Posts
    10

    Default Re: Collate data from different Excel files

    Hi Ravi,
    Thanks for the sample code. Problem here with my situation is that I will not know the number of rows in the individual excel files.
    If it was standard no. of rows, then I can manage with your given code.
    Plz advice....

  4. #4
    Board Regular
    Join Date
    Feb 2006
    Posts
    3,437

    Default Re: Collate data from different Excel files

    Hi
    To modify the codes, you should give me an idea of how many columns and how many rows on an average / max each file has. Is it 25-50 or 400-450 or 3500-3800 etc.Any other details about layout of your data would help.
    Ravi

  5. #5
    New Member
    Join Date
    Jun 2007
    Posts
    10

    Default Re: Collate data from different Excel files

    Ravi,
    Each files have data from column A to N. We will not be able to know the no. of rows. It may vary from file to file. Plz advice.......

  6. #6
    Board Regular
    Join Date
    Feb 2006
    Posts
    3,437

    Default Re: Collate data from different Excel files

    HI
    try the following macro. change the file path from D:\My Documents\ to your file path. I have set max no of rows as 100. change it to suit your needs.
    Code:
     
    Sub collate()
    Dim F As String, c As String
    Dim d As Integer, e As Integer, a As Integer, b As Integer, x As Integer
    Cells(3, 1).Select
    F = Dir("D:\My Documents\" & "*.xls")
    Do While Len(F) > 0
    ActiveCell.Formula = F
    ActiveCell.Offset(1, 0).Select 'By row
    F = Dir()
    Loop
    d = 1
    x = Cells(Rows.Count, 1).End(xlUp).Row
    For e = 3 To x
    c = Cells(e, 1)
    cells(1,2) = c
    d = d + 1
    Cells(d, 2) = Cells(e, 1)
    For a = 2 To 100 ' enter max no of rows instead of 100
    Cells(1, 1) = "='D:\My Documents\[" & c & "]sheet1'!A" & a
    If Cells(1, 1) = "" Or Cells(1, 1) = 0 Then
    Exit For
    Else
    For b = 1 To 14
    Cells(1, 1) = "='D:\My Documents\[" & c & "]sheet1'!" & Chr(b + 64) & a
    Cells(d, b + 3) = Cells(1, 1)
    Next b
    d = d + 1
    End If
    Next a
    d = d + 1
    Next e
    End Sub
    run the macro. pulling data from closed workbook is a slow process depending on the no of files and no of rows. Cell B1 shows the file name from which data is currently extracted.
    Ravi

  7. #7
    New Member
    Join Date
    Jun 2007
    Posts
    10

    Default Re: Collate data from different Excel files

    Hi Ravi,
    Thank you very much for the code. Will tweak it according to my need. Once again thanks.
    Regards,
    Bajesh

  8. #8
    New Member
    Join Date
    Aug 2009
    Posts
    4

    Question Re: Collate data from different Excel files

    Hi Ravi,

    I'm trying to do pretty much the same thing as Bajesh with collating data from multiple workbooks so have used the code you posted above.

    I have edited the code for my needs and it works fine and does exactly what I want it to do, so thanks for that.

    The problem now is that it only seems to work when the folder I get the data from has less than 10 workbooks in it.

    My folder has up to 500 files in it, is there anything that can be or needs to be changed in the above code that will enable it to work with large amounts of files?

    Any help you can give will be much appreciated

    Thanks in advance
    Gavin

  9. #9
    Board Regular
    Join Date
    Feb 2006
    Posts
    3,437

    Default Re: Collate data from different Excel files

    Hi gavin
    there is no limit to the number of files. Probably your 11th file does not have sheet1 and the macro might have halted. What is the error message you are getting?
    ravi

  10. #10
    New Member
    Join Date
    Aug 2009
    Posts
    4

    Default Re: Collate data from different Excel files

    Hi Ravi,

    All of the workbooks are in exactly the same format with sheet 1 appearing in all of them, also I have tried different sets of sample data so the 11th file isn't always the same and on rare occaisions it will work with more than 10 files.

    the error I get is "Run-time error '1004': Application-defined or object-defined error"

    which highlights either this line:

    Code:
     
    Cells(1, 1) = "='C:\RSC(C)\2009\Runfiles 2009 Working Folders\Scot\[" & c & "]sheet1'!A" & a
    or this line:

    Code:
    Cells(1, 1) = "='C:\RSC(C)\2009\Runfiles 2009 Working Folders\Scot\[" & c & "]sheet1'!" & Chr(b + 64) & a
    as the offending code. Which 1 it highlights seems to be random as it picks a different 1 each time I get the error.

    Gavin

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com