Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Calling Excel from VB .net

Posted on 2003-11-13
8
Medium Priority
?
642 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

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.   …
Flash (http://en.wikipedia.org/wiki/Adobe_Flash) has evolved over the years to where it has become a masterful tool for displaying content screen.  It has excellent layout placement, UI precision as well as rendering capabilities. This, along with t…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

618 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