Appending an Excel file using Object Library & VBA

[buffalo]

Hi,

I have just started to use the Excel Object Library. Here is a code that opens two Excel files, TEST and BLOTTER. Let us say both have some data in sheets called SheetFile1(of TEST) and SheetFile2(of BLOTTER).

How would I copy all the rows from SheetFile2 and append them to SheetFile1 which already has a few rows in it? (No need to check if total > 65000, etc.)

Here is the code I use to open both files. Please feel free to remove any redundancy.


Sub OpenFINAL()

Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet

Dim o2Book As Excel.Workbook
Dim o2Sheet As Excel.Worksheet

sfilename = "Q:\ZOOM\TEST.xls"
s2filename = "Q:\ZOOM\BLOTTER.xls"
' Create a new instance of Excel
Set oExcel = New Excel.Application

Set oBook = oExcel.Workbooks.Open(sfilename)
Set o2Book = oExcel.Workbooks.Open(s2filename)

' Get a reference to the default/active sheet in the workbook (typically "Sheet1")
Set oSheet = oBook.ActiveSheet
Set o2Sheet = o2Book.ActiveSheet


' Assign a value to the first column of the first row
oSheet.Cells(2, 2).Value = o2Sheet.Cells(2, 2).Value
'----> So this part needs to change, all rows from BLOTTER have to be appended to TEST with formatting.

oBook.Save

' Close the workbook
oBook.Close
o2Book.Close
' Close Excel
oExcel.Quit

' Release the object references
Set oSheet = Nothing
Set oBook = Nothing


Set o2Sheet = Nothing
Set o2Book = Nothing

Set oExcel = Nothing

End Sub

 

[BrianB]

There is more code than you need here, but it shows the principle.

http://www.mrexcel.com/board2/viewtopic.php?t=145246&highlight=