Excel exception Vb.Net

[Nate TK]

Hi,

I am currently attempting to get results from a MySQL database and write them into an excel spreadsheet, I have been doing this through Visual Studio Community 2015 using vb.net. 

The initial plan was to be that when the function was entered into excel the results would appear within the same excel workbook that was already open, however this doesn't seem to be the case at time of entering the function into Excel. 

The current problems that have been occurring are; exception from HRESULT: 0x800AC472, exception from HRESULT: 0x800A03EC, Exception thrown: 'System.Runtime.InteropServices.COMException' in mscorlib.dll. These occur once it has connected and is writing the results to Excel, both cause  Excel to throw an exception and for the Excel workbook that the function is entered in to become completely unresponsive.

It has never once managed to reach the full 30000 rows of results data, and it hasn't been consistent in where the HRESULT message boxes have popped up (anywhere between 3000 to 29000 rows so far). 

Any help and/or advice would be greatly appreciated with this please.

My source code is as so below:

Imports ExcelDna.Integration.ExcelDnaUtil

Imports MySql.Data.MySqlClient

Imports Excel = Microsoft.Office.Interop.Excel

Public Module MyFunctions

    Public Function ConnectNate()

        Dim conn As New MySqlConnection

        'Dim ws As Object ' not As Worksheet

        'ws = Application.activesheet

        'MsgBox(ws.name)

        Dim DatabaseName As String = "DBname"

        Dim server As String = "****"

        Dim userName As String = "****"

        Dim password As String = "****"

        If Not conn Is Nothing Then conn.Close()

        conn.ConnectionString = String.Format("server={0}; user id={1}; password={2}; database={3}; pooling=false", server, userName, password, DatabaseName)

        Try

            conn.Open()

            MsgBox("Connected")

            Dim item1 As Integer

            Dim item2 As Integer

            Dim item3 As Integer

            Dim item4 As Integer

            Dim item5 As Integer

            Dim item6 As Integer

            Dim item7 As Integer

            Dim stm As String = "SELECT * FROM DBname.nates_table ORDER BY Field1 DESC"

            Dim cmd As MySqlCommand = New MySqlCommand(stm, conn)

            Dim reader As MySqlDataReader

            reader = cmd.ExecuteReader()

            item1 = 0

            item2 = 1

            item3 = 2

            item4 = 3

            item5 = 4

            item6 = 5

            item7 = 6

            Dim xlApp As New Excel.Application

            Dim xlWorkBook As Excel.Workbook

            Dim xlWorkSheet As Excel.Worksheet

            xlWorkBook = xlApp.Workbooks.Add

            '~~> Display Excel

            xlApp.Visible = True

            '~~> Set the relevant sheet that we want to work with

            xlWorkSheet = xlApp.Sheets.Add

            xlWorkSheet.Name = "NateSheet1"

            With xlWorkSheet

                '~~> Directly type the values that we want

                .Range("A1").Value = "Field1"

                .Range("B1").Value = "Field2"

                .Range("C1").Value = "Field3"

                .Range("D1").Value = "Field4"

                .Range("E1").Value = "Field5"

                .Range("F1").Value = "Field6"

                .Range("G1").Value = "Field7"

                Dim counter As ULong

                counter = 2

                For Each Read In reader

                    .Range("A" & counter).Value = reader.Item(item1)

                    .Range("B" & counter).Value = reader.Item(item2)

                    .Range("C" & counter).Value = reader.Item(item3)

                    .Range("D" & counter).Value = reader.Item(item4)

                    .Range("E" & counter).Value = reader.Item(item5)

                    .Range("F" & counter).Value = reader.Item(item6)

                    .Range("G" & counter).Value = reader.Item(item7)

                    counter = counter + 1

                    If counter = 30000 Then

                        Exit For

                    End If

                Next

            End With

            reader.Close()

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

        conn.Close()

    End Function

End Module

[Govert van Drimmelen]

Hi Nate,

 

In an Excel-DNA add-in, you should always get the root Application COM object by calling ExcelDnaUtil.Application. Your approach of calling New Excel.Application(…) can cause unexpected problems.

Excel does not allow you to write to the sheet or interact with the COM object model in the context of a UDF function called from the worksheet. If your code were in a macro that your trigger from a button or a ribbon item, it would probably work.

The easiest fix is probably to change your “Function” to a “Sub” (then Excel-DNA will register it as a macro rather than a worksheet function), then put a button on a sheet and assign the macro “ConnectNate” to the button.

There are some ways to start running this macro from a function, but you should get it to work as a macro first.

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Nathaniel Thane-Kitson]

Hi Govert,

Thank you for your help I've managed to get it working now, using some responses from other questions following the idea from your response too.

New version of code below:

Imports ExcelDna.Integration ' for ExcelDnaUtil, XlCall, etc

Imports ExcelDna.Integration.ExcelDnaUtil

Imports MySql.Data.MySqlClient

Imports Excel = Microsoft.Office.Interop.Excel

Public Module MyFunctions

    Dim xlApp As Excel.Application

    <ExcelCommand(MenuName:="Database Macros", MenuText:="App Run")>

    Public Sub ConnectNate()

        Dim conn As New MySqlConnection

        Dim ws As Object ' not As Worksheet

        ws = Application.activesheet

        MsgBox(ws.name)

        Dim DatabaseName As String = "DBname"

        Dim server As String = "****"

        Dim userName As String = "****"

        Dim password As String = "****"

        If Not conn Is Nothing Then conn.Close()

        conn.ConnectionString = String.Format("server={0}; user id={1}; password={2}; database={3}; pooling=false", server, userName, password, DatabaseName)

        Try

            conn.Open()

            MsgBox("Connected")

            Dim item1 As Integer

            Dim item2 As Integer

            Dim item3 As Integer

            Dim item4 As Integer

            Dim item5 As Integer

            Dim item6 As Integer

            Dim item7 As Integer

            Dim stm As String = "SELECT * FROM DBname.nates_table ORDER BY Field1 DESC"

            Dim cmd As MySqlCommand = New MySqlCommand(stm, conn)

            Dim reader As MySqlDataReader = cmd.ExecuteReader()

            item1 = 0

            item2 = 1

            item3 = 2

            item4 = 3

            item5 = 4

            item6 = 5

            item7 = 6

            Dim xlSheet As Excel.Worksheet

            Dim xlWorkBook As Excel.Workbook

            Dim xlCell As Excel.Range

            xlApp = CType(ExcelDnaUtil.Application, Excel.Application)

            xlSheet = CType(xlApp.ActiveSheet(), Excel.Worksheet)

            xlCell = xlApp.ActiveCell()

            xlWorkBook = xlApp.Workbooks.Add

            xlApp.Visible = True

            '~~> Set the relevant sheet that we want to work with

            With xlWorkBook.ActiveSheet

    End Sub

End Module

=======

Nate