Combining files 1

[JamesBarlow]

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

 

[alexit]

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&quot
Set oFold = oFSO.GetFolder("C:\BOMS\&quot
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&quot, Cells(Cells.Rows.Count, "D&quot.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 = ThisWorkbook.Sheets(1).Cells(y, 1).Value
aItemNo = Thisworkbook.Sheets(1).Cells(y, 2).Value
aDescription = Thisworkbook.Sheets(1).Cells(y, 3).Value
aQtyNo = 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&quot.Sheets(1).Cells(y, h). = aPositionNo
Workbooks("merge.xls&quot.Sheets(1).Cells(y, h + 1) = aItemNo
Workbooks("merge.xls&quot.Sheets(1).Cells(y, h + 2) = aDescription
Workbooks("mrplist.xls&quot.Sheets(2).Cells(y, h + 3) = aQty
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...

Alex

 

[pmover]

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!
-pmover