Export with VB to Excel and update file

Question

Hello.

This is the code that i have to export data to Excel.

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add

oSheet = oBook.Worksheets(1)
oSheet.Range("A1").Value = "ID"
oSheet.Range("B1").Value = " Nome"
oSheet.Range("A1:B1").Font.Bold = True
oSheet.Range("A2").Value = CStr(Request("ID"))
oSheet.Range("B2").Value = "John"

    oBook.SaveAs("C:\Book1.xlsx")
    oExcel.Quit()

I can create and save the excel file, but i can't update the contents. How can i do it?

Thanks.

Answer 1

You're trying to set a Range to a value, I think either you need to set a Range to an array that can contain the value(s) or you need to set a single Cell to a single value.

This link shows how to do either:

http://support.microsoft.com/kb/301982

Answer 2

I think you want:

 oBook = oExcel.Workbooks.Open ("C:\Book1.xlsx")

When you choose Add, you are creating a new workbook.

If you are confident there are no gaps, something like this may suit:

''Last cell in column A, or first gap
oSheet.Range("a1").End(xlDown).Select
''A+1 row
oSheet.ActiveCell.Offset(1) = "a"
''A + 1 row + 1 col
oSheet.ActiveCell.Offset(1, 1) = "b"
''A + 1 row + 2 col
oSheet.ActiveCell.Offset(1, 2) = "c"

Otherwise, you may need http://support.microsoft.com/kb/142526 to determine the last cell.