Solved

Calling Excel from VB .net

Posted on 2003-11-13
8
637 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

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…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used.

789 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