Using VB to add fields in seperate Excel files

[GuyM]

HI,

I'am trying to add data from 2 seperate spreadsheets together with VB instead of having to do this to manually to 245 fields.

I went to the MS support site (Boo-Hiss) and they suggested this code...


Sub TotalData()
Dim File1 as Object, File2 as Object, CurCell as Object

' Where the first column of data is located in Book1 on Sheet1
' in range A1:A10.
set File1 = Workbooks("Book1").Sheets("Sheet1").Range("A1:A10")

' We are now dealing with the second column of data.
set File2 = Workbooks("Book2").Sheets("Sheet1").Range("A1:A10")

for Each CurCell In Range("A1:A10")

' to add instead of subtract, change the minus sign
' to a plus sign.
CurCell.Value = File1.Cells(CurCell.Row, 1).Value - _
File2.Cells(CurCell.Row, 1).Value

next
End Sub




...which I adapted to...


Sub TotalData()
Dim File1 as Object, File2 as Object, CurCell as Object

' Where the first column of data is located in Book1 on Sheet1
' in range A1:A10.
set File1 = Workbooks("SCMS1").Sheets("Sheet1").Range("B3:B38")

' We are now dealing with the second column of data.
set File2 = Workbooks("SCMS2").Sheets("Sheet1").Range("B3:B38")

for Each CurCell In Range("B3:B38")

' to add instead of subtract, change the minus sign
' to a plus sign.
CurCell.Value = File1.Cells(CurCell.Row, 1).Value + _
File2.Cells(CurCell.Row, 1).Value

next
End Sub




...for my purposes, but sadly it is not adding the column in workbook two to the column in workbook one together. New figures are created in the designated fields but data has neither been added or subtracted correctly, I have no idea where the calulations generated arise from.

Can anyone be of any assistance?

Kind Regards

GuyM

[Cimperiali]

private Sub Command1_Click()
'put a reference to Microsoft Excel 8.0 (or 9.0) object library
'this code can generate value in cells of sheets 1 of two xls files
'or reda those values and then
'add the values to the sheet2 of first .xls file
Dim xlApp as Excel.Application
Dim xlWbook1 as Excel.Workbook
Dim xlWbook2 as Excel.Workbook
Dim xlRange1 as Excel.range
Dim xlRange2 as Excel.range
Dim xlRange3 as Excel.range
Dim x as Variant
set xlApp = new Excel.Application
'---
'only for debug.purpouse
'xlApp.Visible = true
'-----
'if you have your .xls files
'you should unco,mmment these two lines:
'set xlWbook1 = xlApp.Workbooks.Open("c:\First.xls", , false)
'set xlWbook2 = xlApp.Workbooks.Open("c:\Second.xls", , false)
'-----
'and comment the following two lines:
set xlWbook1 = xlApp.Workbooks.Add
set xlWbook2 = xlApp.Workbooks.Add
'--------
With xlWbook1
'----
'this is italian
'set xlRange1 = .Sheets("Foglio1").range("B3:B38")
'set xlRange3 = .Sheets("Foglio2").range("B3:B38")
'-----
'your's is:
set xlRange1 = .Sheets("Sheet1").range("B3:B38")
set xlRange3 = .Sheets("Sheet2").range("B3:B38")
'----
End With
With xlWbook2
'----
'this is italian
'set xlRange2 = .Sheets("Foglio1").range("B3:B38")
'----
'your's is:
set xlRange2 = .Sheets("Sheet1").range("B3:B38")
End With
'------
'for debug purouse: writing values in runtime
'to sheets 1 of the two xls files
' comment out following line if not needed
'Dim i as Integer
for Each x In xlRange3
'----
'data added to debug purpouse
'if you have values there, comment out these lines
'i = i + 1

'xlRange1.Cells(x.Row, 1).Value = i
'xlRange2.Cells(x.Row, 1).Value = i * 2
'----
'this do the job of mmerging (summing) values
'taken form first sheet of two xls file and
'adding them in second sheet of first file
xlRange3.Cells(x.Row, 1).Value = xlRange1.Cells(x.Row, 1).Value + xlRange2.Cells(x.Row, 1).Value
next
'no confirmation
xlApp.DisplayAlerts = false
'save first xls file (in second sheet you have the sum column)
xlWbook1.SaveAs "C:\first.xls"
'save the second file
xlWbook2.SaveAs "c:\second.xls"
'release all reefernces, close and quit excel
set xlRange1 = nothing
set xlRange2 = nothing
set xlRange3 = nothing
xlWbook1.Close
xlWbook2.Close
set xlWbook1 = nothing
set xlWbook2 = nothing
xlApp.Quit
set xlApp = nothing
End Sub





Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, TCartwright, Bruno Paris, Dr_Micahel
and all the other wonderful people who made and make Codeguru a great place.
Come back soon, you Gurus.

The Rater