Excel Automation Problem

[goonsquad]

I'm writing an Excel Automation app and I'm running to a problem early
on with what should be a basic step.

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim oRng As Excel.Range
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add
oSheet = oBook.Worksheets(1)
....Populate Sheet...
oRng(Selection, ActiveCell.SpecialCells(xlLastCell)).Select()

My application will not compile due to the last line because
Selection, ActiveCell and xlLastCell are not declared. I have added
Excel 10.0 (2002) as a reference. What am I doing wrong?

 

[Ken Tucker [MVP]]

Hi,

I added a reference to the excel interop assembly. Here is an
example on creating as worksheet. Try to avoid using latebinding.

Dim oExcel As Microsoft.Office.Interop.Excel.Application

Dim oBook, oBook1 As Microsoft.Office.Interop.Excel.Workbook

Dim oSheet As Microsoft.Office.Interop.Excel.Worksheet

'Start a new workbook in Excel.

oExcel = New Microsoft.Office.Interop.Excel.Application

oBook = oExcel.Workbooks.Add

oBook1 = oExcel.Workbooks.Add

'Add data to cells of the first worksheet in the new workbook.

oSheet = CType(oBook.Worksheets(1),
Microsoft.Office.Interop.Excel.Worksheet)

oSheet.Range("A1").Value = "Last Name"

oSheet.Range("B1").Value = "First Name"

oSheet.Range("C1").Value = "Price"

oSheet.Range("A1:B1").Font.Bold = True

oSheet.Range("A2").Value = "Doe"

oSheet.Range("B2").Value = "John"

oSheet.Range("C2").Value = 12345.456

oSheet.Range("C2").Cells.NumberFormat = "$0.00"

oSheet = CType(oBook.Worksheets(2),
Microsoft.Office.Interop.Excel.Worksheet)

oSheet.Range("A1").Value = "Last Name"

oSheet.Range("B1").Value = "First Name"

oSheet.Range("C1").Value = "Price"

oSheet.Range("A1:B1").Font.Bold = True

oSheet.Range("A2").Value = "Doe"

oSheet.Range("B2").Value = "John"

oSheet.Range("C2").Value = 12345.456

oSheet.Range("C2").Cells.NumberFormat = "$0.00"

'Save the Workbook and quit Excel.

oExcel.DisplayAlerts = False

oBook.SaveAs("c:\Book1.xls")

oSheet = Nothing

oBook = Nothing

oExcel.Quit()

oExcel = Nothing

GC.Collect()



Ken
---------------------
I'm writing an Excel Automation app and I'm running to a problem early
on with what should be a basic step.

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim oRng As Excel.Range
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add
oSheet = oBook.Worksheets(1)
....Populate Sheet...
oRng(Selection, ActiveCell.SpecialCells(xlLastCell)).Select()

My application will not compile due to the last line because
Selection, ActiveCell and xlLastCell are not declared. I have added
Excel 10.0 (2002) as a reference. What am I doing wrong?

 

[Jorge]

Hi
Try
oSheet.oRng(Selection, ActiveCell.SpecialCells
(xlLastCell)).Select()

Kind Regards
Jorge

 

[Herfried K. Wagner [MVP]]

* (e-mail address removed) (goonsquad) scripsit:

My application will not compile due to the last line because
Selection, ActiveCell and xlLastCell are not declared. I have added

Make sure the enum containing 'xlLastCell' is imported.

 

[Jeff Burgoon]

Thanks guys!

Using the Excel Interop Assembly I was able to finally get it right.

oRng = oSheet.Range(oExcel.Selection,
oExcel.Selection.End(Microsoft.Office.Interop.Excel.XlDirection.xlToRight))