?
Solved

Calling Excel from VB .net

Posted on 2003-11-13
8
Medium Priority
?
643 Views
Last Modified: 2012-05-04
Hi,

I'm simply calling excel from a VB .net program as same as i did in VB6.0 as follows-

Dim lobjExApp As New Excel.Application()
Dim lobjExWbk As New Excel.Workbook()
Dim lobjExWst As New Excel.Worksheet()

here when i execute second line it gives me error -
Additional information: COM object with CLSID {00020819-0000-0000-C000-000000000046} is either not valid or not registered.

I've added refernce of microsoft excel9.0 object library and I'm working on Windows 2000 machine.

Can anybody suggest me what to do ?

Thanx,
Jyoti.
0
Comment
Question by:jyotisinha
8 Comments
 

Expert Comment

by:krinpit
ID: 9738720
try

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheetAltNet As Excel.Worksheet

0
 
LVL 6

Author Comment

by:jyotisinha
ID: 9738795
krinpit..

It delays error to one more step i.e. when i'll do the
xlBook =new Excel.Workbook
it gives same error.
0
 

Expert Comment

by:krinpit
ID: 9739130
try adding references to 'msoffice' and 'stdole'
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 39

Expert Comment

by:appari
ID: 9739243
try latebinding.

Dim xlApp As object
Dim xlBook As object
Dim xlSheetAltNet As object

xlApp=createobject("Excel.Application")
...

0
 

Expert Comment

by:krinpit
ID: 9739718
appari, I don't think that should should be an issue, seeing as jyotisinha's process handles the first declaration correctly. I suspect that the program might stall on the latebinding statement with the same exception as before. It's definitely worth a shot, but if it works, I'd like to know why.
0
 
LVL 5

Accepted Solution

by:
tgannetts earned 480 total points
ID: 9741056
The following code seems to work

Dim xlapp As New Excel.Application()
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

xlBook = xlapp.Workbooks.Add()
xlSheet = xlBook.Worksheets.Add()

xlapp.Visible = True

Tom
0
 
LVL 6

Author Comment

by:jyotisinha
ID: 9761324
Tom ... yes it works but what's wrong with what I'd done ?
0
 

Assisted Solution

by:shrikantp
shrikantp earned 120 total points
ID: 9769373
Try The following Code in ur project it will run. Change connectionstring and give the proper table name.
And Add the following references in ur project

***************
Interop.Excel.dll
Interop.Excel_1_3.dll
Interop.Microsoft.Office.Core.dll
Interop.Office_2_1.dll
Interop.VBIDE.dll
Interop.VBIDE_5_3.dll
*****************
The following is the VB.NET Code just copy and paste it in ur project.

Imports System
Imports System.Drawing
Imports System.Collections
Imports System.ComponentModel
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.SqlClient



Namespace SQLServertoExcel
   ' <summary>
   ' Summary description for Form1.
   ' </summary>
   Public Class Form1
      Inherits System.Windows.Forms.Form
        Friend WithEvents button1 As System.Windows.Forms.Button
      ' <summary>
      ' Required designer variable.
      ' </summary>
      Private components As System.ComponentModel.Container = Nothing
     
     
      Public Sub New()
         '
         ' Required for Windows Form Designer support
         '
         InitializeComponent()
      End Sub 'New
       
      '
      ' TODO: Add any constructor code after InitializeComponent call
      '
     
      ' <summary>
      ' Clean up any resources being used.
      ' </summary>
        Protected Overloads Sub Dispose(ByVal disposing As Boolean)
            If disposing Then
                If Not (components Is Nothing) Then
                    components.Dispose()
                End If
            End If
            MyBase.Dispose(disposing)
        End Sub 'Dispose

        ' <summary>
        ' Required method for Designer support - do not modify
        ' the contents of this method with the code editor.
        ' </summary>
        Private Sub InitializeComponent()
            Me.button1 = New System.Windows.Forms.Button()
            Me.SuspendLayout()
            '
            ' button1
            '
            Me.button1.Location = New System.Drawing.Point(32, 72)
            Me.button1.Name = "button1"
            Me.button1.Size = New System.Drawing.Size(216, 112)
            Me.button1.TabIndex = 0
            Me.button1.Text = "Populate Excel With Customer Information From Microsoft SQL Server"
            '
            ' Form1
            '
            Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
            Me.ClientSize = New System.Drawing.Size(292, 273)
            Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.button1})
            Me.Name = "Form1"
            Me.Text = "Form1"
            Me.ResumeLayout(False)
        End Sub 'InitializeComponent

        ' <summary>
        ' The main entry point for the application.
        ' </summary>
        <STAThread()> _
        Shared Sub Main()
            Application.Run(New Form1())
        End Sub 'Main

        Private Function GetData() As System.Data.DataTable
            Dim conn As New SqlConnection("Data source=localhost;uid=sa;pwd=sa;database=Temp;")

            Dim adapter As New SqlDataAdapter("select * from EmpDetails", conn)
            Dim myDataSet As New DataSet()
            Try
                adapter.Fill(myDataSet, "Customer")
            Catch ex As Exception
                MessageBox.Show(ex.ToString())
            End Try

            Return myDataSet.Tables(0)
        End Function 'GetData

        Private Sub button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles button1.Click
            Dim excel As New Excel.ApplicationClass()
            Dim rowIndex As Integer = 1
            Dim colIndex As Integer = 0

            excel.Application.Workbooks.Add(True)

            Dim table As DataTable = GetData()

            Dim col As DataColumn
            For Each col In table.Columns
                colIndex += 1
                excel.Cells(1, colIndex) = col.ColumnName
            Next col

            Dim row As DataRow
            For Each row In table.Rows
                rowIndex += 1
                colIndex = 0
                Dim col1 As DataColumn
                For Each col1 In table.Columns
                    colIndex += 1
                    excel.Cells(rowIndex, colIndex) = row(col1.ColumnName).ToString()
                Next col1
            Next row
            excel.Visible = True
        End Sub 'button1_Click
    End Class 'Form1
End Namespace 'SQLServertoExcel
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question