Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 50
Default How to combine 200 spreadsheets

I have about 200 individual excel files. each has a single worksheet,
none is over a hundred rows, 8 coloumns.

Is there a simple way to collect all of those spreadsheets, (the
contents of a single directory) into one workbook, importing them into
individual sheets?

Is there a sigle way to just append the data in each of thos worksheets

into a single worksheet as well?

Working with Excell 2003 here.

Thanx

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,123
Default How to combine 200 spreadsheets

Hi Phil

Try this add-in or try the code on the other pages
http://www.rondebruin.nl/merge.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Phil Smith" wrote in message ...
I have about 200 individual excel files. each has a single worksheet,
none is over a hundred rows, 8 coloumns.

Is there a simple way to collect all of those spreadsheets, (the
contents of a single directory) into one workbook, importing them into
individual sheets?

Is there a sigle way to just append the data in each of thos worksheets

into a single worksheet as well?

Working with Excell 2003 here.

Thanx

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 50
Default How to combine 200 spreadsheets

Great, but it does not work. It only imports three or four of them. It
cycles through all of the files, but only about 70 lines of the first
four files come over. The rest of the files are shown in the log, but
without the "yes" beside them.

If the files are 2007 Excel format, and I am trying to import them using
2003, would that explain it? Unfortunately, the creator of these files
left on a two week trip to China, so it may take a bit to determine if
he is indeed using the later version...

Thanx






Ron de Bruin wrote:
Hi Phil
Try this add-in or try the code on the other pages
http://www.rondebruin.nl/merge.htm


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,123
Default How to combine 200 spreadsheets

Hi Phil

Can you send one or two of the files to me private.

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Phil Smith" wrote in message ...
Great, but it does not work. It only imports three or four of them. It
cycles through all of the files, but only about 70 lines of the first
four files come over. The rest of the files are shown in the log, but
without the "yes" beside them.

If the files are 2007 Excel format, and I am trying to import them using
2003, would that explain it? Unfortunately, the creator of these files
left on a two week trip to China, so it may take a bit to determine if
he is indeed using the later version...

Thanx






Ron de Bruin wrote:
Hi Phil
Try this add-in or try the code on the other pages
http://www.rondebruin.nl/merge.htm


  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,651
Default How to combine 200 spreadsheets

What are the file extensions? If they are .xls they ought to be Excel 2003
or earlier, but if they are .xlsx (or a number of other varieties) they are
probably 2007.
--
David Biddulph

"Phil Smith" wrote in message
...
Great, but it does not work. It only imports three or four of them. It
cycles through all of the files, but only about 70 lines of the first four
files come over. The rest of the files are shown in the log, but without
the "yes" beside them.

If the files are 2007 Excel format, and I am trying to import them using
2003, would that explain it? Unfortunately, the creator of these files
left on a two week trip to China, so it may take a bit to determine if he
is indeed using the later version...

Thanx






Ron de Bruin wrote:
Hi Phil
Try this add-in or try the code on the other pages
http://www.rondebruin.nl/merge.htm




  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 897
Default How to combine 200 spreadsheets

Phil,

This code will go through a folder called "merged" on your desktop
(assumed C:\Documents And Settings\username\Desktop\) and put all of
the workbooks it finds together on one large spreadsheet called
"merged.xls". It assumes that each workbook is in a tabular format.
This will only work on XL 2003, one of your posts suggested that the
files might be in 2007 format, in which case this code may not work.


Option Explicit
Public Declare Function GetUserName Lib "advapi32.dll" Alias
"GetUserNameA" ( _
ByVal lpBuffer As String, _
nSize As Long) As Long
Public Function Username() As String
Dim lpBuff As String * 1024
GetUserName lpBuff, Len(lpBuff)
Username = Left$(lpBuff, (InStr(1, lpBuff, vbNullChar)) - 1)
End Function
Sub MergeWorkbooks()
'
' click on the email and "save attachments" to a folder called
"merged" which you will
' create on your desktop
'
' this routine will go to that folder and merge all the workbooks in
the folder into one super workbook
' called "merged.xls" on your desktop
'
Dim NewWB As Excel.Workbook
Dim FName As String
Dim myLastCell As String, myLastRow As Long, myLastColumn As Long
Dim myRange As String
Dim directoryfiles()
Dim count As Integer
Dim FileN As String
Dim UserN As String, AddRange As Excel.range
Dim i As Long
UserN = Username
Application.ScreenUpdating = False
' basic error checking
If Dir("C:\Documents and Settings\" & UserN & "\Desktop\merged.xls")
< "" Then
MsgBox ("MERGED.XLS already exists, clear it out before running
this macro"), vbCritical
Exit Sub
End If

If Dir("C:\Documents and Settings\" & UserN & "\Desktop\merged\*.xls")
= "" Then
MsgBox ("No XLS files are in the directory." & vbCrLf & "Put some
workbooks there first."), vbCritical
Exit Sub
End If


' build an array of filenames for later processing
FileN = Dir("C:\Documents and Settings\" & UserN & "\Desktop\merged\")
Do
If FileN < "" Then
ReDim Preserve directoryfiles(count)
directoryfiles(count) = FileN
count = count + 1
End If
FileN = Dir
Loop While FileN < ""

Set NewWB = Workbooks.Add
ActiveWorkbook.SaveAs "C:\Documents And Settings\" & UserN & "\Desktop
\" & "merged.xls", FileFormat:=xlNormal

Set AddRange = Workbooks("merged.xls").Worksheets(1).range("A6553 6")

For i = 0 To UBound(directoryfiles())
Workbooks.Open ("C:\Documents And Settings\" & UserN & "\Desktop
\merged\" & directoryfiles(i))

Run "Del_Empty_Rows"
myLastRow = Cells.Find("*", [A1], , , xlByRows,
xlPrevious).Row
myLastColumn = Cells.Find("*", [A1], , , xlByColumns,
xlPrevious).Column
myLastCell = Cells(myLastRow, myLastColumn).Address
myRange = "a1:" & myLastCell
range(myRange).Copy Destination:=AddRange.End(xlUp).Offset(2,
0)
Workbooks(directoryfiles(i)).Close savechanges:=False
Next i

Workbooks("merged.xls").Close savechanges:=True

Set NewWB = Nothing
Set AddRange = Nothing
MsgBox ("Merge complete!" & vbCrLf & vbCrLf & UBound(directoryfiles())
+ 1 & " workbooks were merged."), vbInformation

If MsgBox("Would you like to delete the separate workbooks?", vbYesNo)
= vbYes Then
For i = 0 To UBound(directoryfiles())
Kill ("C:\Documents And Settings\" & UserN & "\Desktop\merged
\" & directoryfiles(i))
Next i
MsgBox ("Done!"), vbInformation
End If

Application.ScreenUpdating = True
End Sub


Check out http://www.rondebruin.nl/code.htm if you need help pasting
the code into the appropriate place.

HTH,
JP



On Jan 22, 11:43*am, Phil Smith wrote:
I have about 200 individual excel files. *each has a single worksheet,
none is over a hundred rows, 8 coloumns.

Is there a simple way to collect all of those spreadsheets, (the
contents of a single directory) into one workbook, importing them into
individual sheets?

Is there a sigle way to just append the data in each of thos worksheets

into a single worksheet as well?

Working with Excell 2003 here.

Thanx


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to combine workbooks or spreadsheets in to one???? musiclover Excel Worksheet Functions 1 April 3rd 07 07:56 PM
Lookup+combine two spreadsheets into third Benjamin Chait Excel Discussion (Misc queries) 1 March 23rd 07 08:45 PM
How do I combine spreadsheets? Jennifer Excel Discussion (Misc queries) 2 August 31st 06 09:33 AM
how do i combine two separate spreadsheets? Merging Spreadsheets Excel Discussion (Misc queries) 1 March 2nd 06 05:42 PM
Combine two spreadsheets using Macro jlogsdon Excel Worksheet Functions 0 May 23rd 05 11:14 PM


All times are GMT +1. The time now is 10:28 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"