Merging multiple excel files into 1 excel spreadsheet

U

uberblah

I have thousands of spreadsheets, all with thier data in the exact sam
format; however, all with different file names, that I want to combin
onto one spreadsheet. Rather than manually copying and pasting eac
row of information onto a spreadsheet from every file , is there an
macro/function/trick/omnipotant being... ;) that I can use t
automatically place each line of information from the seperate file
onto a single spreadsheet
 
K

Ken Wright

Give us details of what data is in there (ie ranges) and how you want it
consolidated, where do you want the data to go.

Example, looking for the values in range A3:L3 on sheet 1 in every workbook
to be listed in order underneath each other on a single consolidation
worksheet:-

Ron De Bruin has lots of examples for this kind of thing on his website

http://www.rondebruin.nl/tips.htm

but if you give us some more detail then maybe we can give you more tailored
advice

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :)
------------------------------­------------------------------­----------------
 
U

uberblah

Ken,

I need values in range A4:AK4 in every workbook to be listed in order
underneath each other on a single consolidation worksheet. There are
no sheet1/2/3 in these files, so they be be considered forms?
 
U

uberblah

Ron,

Your code is very useful and it does do what I needed it to do;
however, I need to modify it to do 2 extra tasks and I'm new to VB
programming so I wondered if you could help.

1. I need the macro to crawl through directories. Maybe some sort of
recursion needs to be used? been so long since I've programmed :( My
files are contained in varius sub directories, and how deep they go may
vary. I need the macro to crawl through the main directory I point it
to and capture all the files in all the sub directories.

2. These spreadsheets I'm usings have 25 rows to them and not all rows
contain data; however, I need to get all those rows with data onto my
consolidated sheet. The rows are from rows A4 to A28. and the row
length is A4:AK.

I'm playing with your code as I write this but I don't know how
successfull I'm going to be considering I'm so rusty with programming
in general, let alone I've never touched VB. :eek:

The code from your website that I'm using is:

Copy a Range from each workbook

This example will copy Range("A1:J1") from the first sheet in each
workbook.
Change the folder "C:\Data" 0r "\\ComputerName\YourFolder" to your
folder.

Sub Example1()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim rnum As Long
Dim SourceRcount As Long
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "C:\Data"
ChDrive MyPath
ChDir MyPath

FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Application.ScreenUpdating = False
Set basebook = ThisWorkbook
basebook.Worksheets(1).Cells.Clear
'clear all cells on the first sheet
rnum = 1

Do While FNames <> ""
Set mybook = Workbooks.Open(FNames)
Set sourceRange = mybook.Worksheets(1).Range("A4:AK4")
SourceRcount = sourceRange.Rows.Count
Set destrange = basebook.Worksheets(1).Cells(rnum, "A")

basebook.Worksheets(1).Cells(rnum, "AL").Value = mybook.Name
' This will add the workbook name in column D if you want

sourceRange.Copy destrange
' Instead of this line you can use the code below to copy only
the values

' With sourceRange
' Set destrange = basebook.Worksheets(1).Cells(rnum,
"A"). _
' Resize(.Rows.Count,
..Columns.Count)
' End With
' destrange.Value = sourceRange.Value

mybook.Close False
rnum = rnum + SourceRcount
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub
 
U

uberblah

Ron,

Agian your code is informative and helpful. I modified it so I could
get every row in the spreadsheet because your original code only
returns the first row you point it to.

A feature I have been uccessfull with so far is to add the directory
name where the file is located into into the spreadsheet.

Below is the code I'm using. I have edited it down to 1 Case statement
so it's easyer to read. The case statements are what allowed me to
capture each row of the spreadsheets. I highlighted the psuedo code in
red which I want to use to capture the current directory that File is
in. Any ideas on how I can get this to work? I don't think there is
an option under Application.Filesearch to do this?


Sub File_crawl_consolodate_values()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim rnum As Long
Dim i As Long
Dim j As Long
Dim a As Long
Application.ScreenUpdating = False

With Application.FileSearch
..NewSearch
..LookIn = "C:\Data"
..SearchSubFolders = True
..FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
Set basebook = ThisWorkbook
rnum = 1
For i = 1 To .FoundFiles.Count
For j = 1 To 1
Select Case j

Case 1
Set mybook = Workbooks.Open(.FoundFiles(i))
Set sourceRange =
mybook.Worksheets(1).Range("A4:AK4")
a = sourceRange.Rows.Count
With sourceRange
Set destrange =
basebook.Worksheets(1).Cells(rnum, 1). _
Resize(.Rows.Count,
..Columns.Count)
End With
destrange.Value = sourceRange.Value
basebook.Worksheets(1).Cells(rnum, "A").Value =
Dir(CurrentFileDirectory, vbDirectory)
mybook.Close
rnum = rnum + a
End Select
Next j
Next i
End If
End With
End Sub
 
U

uberblah

Nevermind!!!! I just figured it out. Thanks again for the codebase to
work from Ron, Without It I couldn't have accomplished the cool little
macro I've got now. :)

Incidently if anyone cares, all you need to do is:

basebook.Worksheets(1).Cells(rnum, "A").Value = Dir(mybook.path,
vbDirectory)
 
R

rehoboth123

Hi Uberblah

I tried using your code, but it's only returning the 1st line in the
files. My range is from a1 to k1.

Help!
 
Top