0

I'm transitioning from Excel VBA to VB.NET, so if this is a dumb question, please go easy on me. I get a NullReferenceException was unhandled by user code on this line of the following sub:

Dim objSheet As Excel.Worksheet = objBook.Sheets("SQL Creator")

VS says that the Object reference is not set to an instance of the object. I'm not sure why it's asking for that, because I've already declared a new instance of Excel in the objApp variable. Why would I need to declare a new instance of each object under that class? It's very possible I'm not thinking about that correctly, but I just wanted to mention my thoughts. Overall, I'm just trying to test the sub below to see if it will open and close a connection to a PostgreSQL database.

Imports Microsoft.Office.Interop

    Public Sub QueryData(ByVal ribbonUI As Office.IRibbonControl)

            Dim objApp As New Excel.Application
            Dim objBook As Excel.Workbook = objApp.ActiveWorkbook
            Dim objSheet As Excel.Worksheet = objBook.Sheets("SQL Creator")
            Dim pgconn As String

            pgconn = "Driver={PostgreSQL};" &
            "Server = localhost;" &
            "Port = 5432;" &
            "Database = CFABudget;" &
            "Uid = postgres;" &
            "Pwd = budgeto;"

            Dim SQL As String = objSheet.Range("BudgetSQL").Text
            Dim conn As New Data.Odbc.OdbcConnection(pgconn)
            Dim cmd As Data.Odbc.OdbcCommand = New Data.Odbc.OdbcCommand(SQL)

            conn.Open()

            MsgBox("Success!", vbOKOnly)

            conn.Close()


        End Sub

Thank you all for your help!

jones-chris
  • 629
  • 2
  • 13
  • 29
  • Possible duplicate of [What is a NullReferenceException, and how do I fix it?](http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) – Allan S. Hansen May 10 '16 at 16:36

1 Answers1

0

Replace

Dim objApp As New Excel.Application

With:

Dim objApp As Excel.Application = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application")

You were trying to get active workbook from new empty instance of excel.

Claudius
  • 1,883
  • 2
  • 21
  • 34