Solved

Calling Excel from VB .net

Posted on 2003-11-13
8
630 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now