question

ranjanrkl avatar image
ranjanrkl asked

how to implement error handling in the VBA code

i have a code which picks up the database server name from "C10" cell of excel,after taking the value from the cell, it connects to the server and then execute the Stored procedure. My Server Name in "C10" cell : LE64\SQL2012 Option Explicit Sub Button1_Click() Call TS End Sub Sub TS() 'Variable Declaration Dim OutPut As Integer If MsgBox("Do you want to take the Snapshot?", vbQuestion + vbYesNo, "MS Excel") = vbYes Then Dim con As Connection Dim rst As Recordset Dim strConn As String Dim strDatabase As String Dim myRange As Range Dim objWorkbook As Excel.Workbook Dim objWorkSheet As Excel.Worksheet Set objWorkSheet = ThisWorkbook.Sheets("MS Excel") objWorkSheet.Activate Set con = New Connection Let strDatabase = objWorkSheet.Range("C10").Value strConn = "Provider=SQLOLEDB;" strConn = strConn & "Data Source= " & strDatabase & ";" strConn = strConn & "Initial Catalog=Warehouse;" strConn = strConn & "Integrated Security=SSPI;" con.Open strConn Set rst = con.Execute("Exec [dbo].[LoadSP]") MsgBox "Snapshot successfully taken", vbInformation, "MS Excel" con.Close End If End Sub but when i change the value of the cell "C10" slightly like to "LE64\SQL201" it fails to connect to the server and throws an error below: ![alt text][1] [1]: /storage/temp/1569-capture.png i want to handle and show this above error in a different Message box highlighting "Invalid Database name" if it fails to connect to right Database Server after picking up the value from C10 cell. I had a look at different error handling mechanism on the web but didn't able to implement it. Can any one help me in adding a try catch block to my code or any error handling mechanism to deal with the above issue.
stored-proceduresexcelvb
capture.png (5.6 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Dave_Green avatar image
Dave_Green answered
Failing because the SQL server doesn't exist probably means you want to switch to an alternative code block at that point (e.g. to present a pretty error message) rather than continuing to try to execute the code. I would look at [On Error][1] to accomplish this. I've linked to a [MS Support article][2] on how to use it. You do state in your question that you've looked at other mechanisms but been unable to get them to work. If you can state what you've ruled out it (and why) it may help others to assist you further. [1]: http://support.microsoft.com/kb/141571 [2]: http://support.microsoft.com/kb/141571
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for your suggestion.
0 Likes 0 ·
ranjanrkl avatar image
ranjanrkl answered
i made it work by adding "On Error" handling technique in my code: Option Explicit Sub Button1_Click() Call TS End Sub Sub TS() 'Variable Declaration Dim OutPut As Integer On Error GoTo ErrHandler If MsgBox("Do you want to take the Snapshot?", vbQuestion + vbYesNo, "MS Excel") = vbYes Then Dim con As Connection Dim rst As Recordset Dim strConn As String Dim strDatabase As String Dim myRange As Range Dim objWorkbook As Excel.Workbook Dim objWorkSheet As Excel.Worksheet Set objWorkSheet = ThisWorkbook.Sheets("MS Excel") objWorkSheet.Activate Set con = New Connection Let strDatabase = objWorkSheet.Range("C10").Value strConn = "Provider=SQLOLEDB;" strConn = strConn & "Data Source= " & strDatabase & ";" strConn = strConn & "Initial Catalog=Warehouse;" strConn = strConn & "Integrated Security=SSPI;" con.Open strConn On Error GoTo 0 Set rst = con.Execute("Exec [dbo].LoadSP") MsgBox "Snapshot successfully taken", vbInformation, "MS Excel" Exit Sub ErrHandler: MsgBox "Invalid Server\Instance Name", vbInformation, "MS Excel" Exit Sub con.Close End If End Sub
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Looks like you took @Dave_Green's suggestion. It would be good form to vote for and/or accept his answer since it led you to the solution.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.