How to create and access Excel in VB.NET?

[James Wong]

Dear all,

I have an old VB6 application which can create and access Excel object. The
basic definition statements are as follows:

Dim appExcel As Object
Dim wkb1 As Excel.Workbook
Dim wks1 As Excel.Worksheet

Set appExcel = New [_ExcelApplication]
Set wkb1 = appExcel.Workbooks.Open(strFileName)
Set wks1 = wkbSalesSumy.Worksheets("One")

However, in VB.NET 2003, I got problem to do the same thing:

Dim appExcel As Excel.Application
Dim wkb1 As Excel.Workbook
Dim wks1 As Excel.Worksheet

appExcel = New Excel.Application ' since Excel.Application is
interface, this statement will fail due to "New" is not applicable to
interface

Besides, how can I create new worksheet using VB code?

Thanks for your attention and kindly help!

Regards,
James Wong

 

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

Dear all,

I have an old VB6 application which can create and access Excel object. The
basic definition statements are as follows:

Dim appExcel As Object
Dim wkb1 As Excel.Workbook
Dim wks1 As Excel.Worksheet

Set appExcel = New [_ExcelApplication]
Set wkb1 = appExcel.Workbooks.Open(strFileName)
Set wks1 = wkbSalesSumy.Worksheets("One")

However, in VB.NET 2003, I got problem to do the same thing:

Dim appExcel As Excel.Application
Dim wkb1 As Excel.Workbook
Dim wks1 As Excel.Worksheet

appExcel = New Excel.Application ' since Excel.Application is
interface, this statement will fail due to "New" is not applicable to
interface

Besides, how can I create new worksheet using VB code?

Thanks for your attention and kindly help!

Regards,
James Wong

 

[Herfried K. Wagner [MVP]]

[Working with Excel]

General:

INFO: Develop Microsoft Office Solutions with Visual Studio .NET
<URL:http://support.microsoft.com/?kbid=311452>

HOWTO: Automate Microsoft Excel from Visual Basic .NET
<URL:http://support.microsoft.com/?scid=kb;EN-US;301982>

How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With
Visual Basic .NET
<URL:http://support.microsoft.com/?scid=kb;EN-US;316934>

Samples:

How to handle events for Excel 2003 by using Visual Basic .NET
<URL:http://support.microsoft.com/?scid=kb;EN-US;822750>

HOW TO: Handle Events for Excel by Using Visual Basic .NET
<URL:http://support.microsoft.com/default.aspx?scid=kb;en-us;302814>

HOWTO: Automate Microsoft Excel from Visual Basic .NET
<URL:http://support.microsoft.com/?scid=kb;EN-US;301982>

HOW TO: Transfer Data to an Excel Workbook by Using Visual Basic .NET
<URL:http://support.microsoft.com/?scid=kb;EN-US;306022>

HOW TO: Handle Events for Excel by Using Visual C# .NET
<URL:http://support.microsoft.com/?scid=kb;EN-US;823981>

Related:

BUG: "Old Format or Invalid Type Library" Error When Automating Excel 2002
<URL:http://support.microsoft.com/?scid=kb;EN-US;320369>

PRB: Office Application Does Not Quit After Automation from Visual Studio
..NET Client
<URL:http://support.microsoft.com/?scid=kb;EN-US;317109>

HOWTO: Use the WebBrowser Control to Open an Office Document in Visual Basic
..NET
<URL:http://support.microsoft.com/?scid=kb;EN-US;304643>

INFO: Considerations for Server-Side Automation of Office
<URL:http://support.microsoft.com/?scid=kb;EN-US;257757>

More information:

<URL:http://support.microsoft.com/search/?query=Excel+.NET&catalog=LCID=1033>

There are loads of information about how to use Office applications together
with .NET in the Microsoft Office Developer Center:

Microsoft Office Developer Center
<URL:http://msdn.microsoft.com/office/>

Office Development
<URL:http://msdn.microsoft.com/office/understanding/>

Using the Microsoft Visual Studio Tools for the Microsoft Office System
<URL:http://www.devx.com/codemag/Article/18233/>

 

[James Wong]

Hi Herfried and Ken,

Thanks for your information and it works fine!

One more question, there is some information composed by numeric digits but
I want to show it as text (e.g. tel. no. or barcode). In Excel, I will set
the cell format as text. How can I do that using VB.NET with Excel class?

Thanks again your help!

Regards,
James Wong

[Working with Excel]

General:

INFO: Develop Microsoft Office Solutions with Visual Studio .NET
<URL:http://support.microsoft.com/?kbid=311452>

HOWTO: Automate Microsoft Excel from Visual Basic .NET
<URL:http://support.microsoft.com/?scid=kb;EN-US;301982>

How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With
Visual Basic .NET
<URL:http://support.microsoft.com/?scid=kb;EN-US;316934>

Samples:

How to handle events for Excel 2003 by using Visual Basic .NET
<URL:http://support.microsoft.com/?scid=kb;EN-US;822750>

HOW TO: Handle Events for Excel by Using Visual Basic .NET
<URL:http://support.microsoft.com/default.aspx?scid=kb;en-us;302814>

HOWTO: Automate Microsoft Excel from Visual Basic .NET
<URL:http://support.microsoft.com/?scid=kb;EN-US;301982>

HOW TO: Transfer Data to an Excel Workbook by Using Visual Basic .NET
<URL:http://support.microsoft.com/?scid=kb;EN-US;306022>

HOW TO: Handle Events for Excel by Using Visual C# .NET
<URL:http://support.microsoft.com/?scid=kb;EN-US;823981>

Related:

BUG: "Old Format or Invalid Type Library" Error When Automating Excel 2002
<URL:http://support.microsoft.com/?scid=kb;EN-US;320369>

PRB: Office Application Does Not Quit After Automation from Visual Studio
.NET Client
<URL:http://support.microsoft.com/?scid=kb;EN-US;317109>

HOWTO: Use the WebBrowser Control to Open an Office Document in Visual Basic
.NET
<URL:http://support.microsoft.com/?scid=kb;EN-US;304643>

INFO: Considerations for Server-Side Automation of Office
<URL:http://support.microsoft.com/?scid=kb;EN-US;257757>

More information:

<URL:http://support.microsoft.com/search/?query=Excel+.NET&catalog=LCID=10
33>

There are loads of information about how to use Office applications together
with .NET in the Microsoft Office Developer Center:

Microsoft Office Developer Center
<URL:http://msdn.microsoft.com/office/>

Office Development
<URL:http://msdn.microsoft.com/office/understanding/>

Using the Microsoft Visual Studio Tools for the Microsoft Office System
<URL:http://www.devx.com/codemag/Article/18233/>