Solved

Calling Excel from VB .net

Posted on 2003-11-13
8
639 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
IDE for Python 5 74
C# XML Get Values 4 37
ModalPopup  question 22 39
orderby list (from Json) 1 32
IP addresses can be stored in a database in any of several ways.  These ways may vary based on the volume of the data.  I was dealing with quite a large amount of data for user authentication purpose, and needed a way to minimize the storage.   …
The object model of .Net can be overwhelming at times – so overwhelming that quite trivial tasks often take hours of research. In this case, the task at hand was to populate the datagrid from SQL Server database in Visual Studio 2008 Windows applica…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

696 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