Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Combining files

Combining files

Combining files

I am looking for a way to take a number of excel files and combine them into one.

We currently create our BOM's in excel, creating a new file for every assembly.  My current machine has about 100 files, each representing an assembly of the machine.  The formats of each file are the same with the only difference being the number of rows used.  The larger the assembly, the more rows.

I would like to be able to combine all the files into one to be able to get information of the entire job such as cost and time of manufacture.

Does anyone know how this can be done.  My current method is to copy and paste each workbook into a master job workbook.  This is a nightmare that I would like to avoid.

I am running Excel 2000 under Windows NT 4.0

RE: Combining files

You can use a VB script to merge all the files into a single sheet in any arrangement you want. I am going to assume you have all the files in one directory, there are four columns of data, and a variable number of rows...try something like this for a start:

Sub Merge()

Dim oFSO, oFold, oFyls, f1
Dim aPostitionNo() As String
Dim aItemNo() As String
Dim aDescription() As String
Dim aQtyNo() As String
Dim y = As Long
Dim h As Long

h = 1

'This will open all files in the folder BOMS one at a time:
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFold = oFSO.GetFolder("C:\BOMS\")
Set oFyls = oFold.Files
For Each f1 In oFyls
    Workbooks.Open Filename:=f1.Name

'This checks the number of rows in the active file:
Set rng = Range(Cells(1, "D"), Cells(Cells.Rows.Count, "D").End(xlUp))

reDim aPostitionNo(rng) As String
reDim aItemNo(rng) As String
reDim aDescription(rng) As String
reDim aQtyNo(rng) As String

'This loads the data into array variables:
For y = 1 To rng
    aPositionNo(y) = ThisWorkbook.Sheets(1).Cells(y, 1).Value    
    aItemNo(y) = Thisworkbook.Sheets(1).Cells(y, 2).Value
    aDescription(y) = Thisworkbook.Sheets(1).Cells(y, 3).Value
    aQtyNo(y) = Thisworkbook.Sheets(1).Cells(y, 4).Value
Next y

'This is to space the data from each file into the next four columns, and write the data to a new file merge.xls:
h = h + 4
For y = 1 To rng
    Workbooks("merge.xls").Sheets(1).Cells(y, h). = aPositionNo(y)
    Workbooks("merge.xls").Sheets(1).Cells(y, h + 1) = aItemNo(y)
    Workbooks("merge.xls").Sheets(1).Cells(y, h + 2) = aDescription(y)     
    Workbooks("mrplist.xls").Sheets(2).Cells(y, h + 3) = aQty(y)
Next y

'This is to trigger to the next file:
Next f1

End Sub

Of course this is very rough, you will probably need to refine it some...


RE: Combining files

rather than writing vba code, you can accomplish the task by the following procedures:

1) open desired workbooks (source and destination).
2) right click the sheet tab.
3) select Move or Copy...
There is a file/book option and a before sheet option.
4) make your choices and continue onward.

Note that multiple sheets (singularly or continuously) from within a workbook can be moved/copied at the same time.

good luck!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close