Collate data from different Excel files

bajesh

New Member
Joined
Jun 29, 2007
Messages
10
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 ??????
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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....
 
Upvote 0
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
 
Upvote 0
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.......
 
Upvote 0
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
 
Upvote 0
Hi Ravi,
Thank you very much for the code. Will tweak it according to my need. Once again thanks.
Regards,
Bajesh
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top