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!