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