craigwg
06-09-2009, 06:59 AM
This should be an interesting discussion. I have a few procedures written that work great, but there is a lot of
repetitive code amongst them. I want to consolidate them and put the repetitive code in one procedure and
then just call that procedure as needed. Pretty standard stuff, right?
The problem is that variables are created and called through out the procedures and if I start splitting up the
code it stops working. I turn to you guys (and gals) for help! How can I make my code less repetitve? I have
posted one block of code below. The code connects to an SQL server and then inserts data on the XLS
worksheet to the SQL database. I have another block that deletes, updates, and selects. I only posted the
insert block below.
Public Sub Insert()
'This is the connection code that I want stuffed in a seperate proc.
'Before running you must go to
'Tools--> References --> and add Micorsoft ActiveX Data Objects 2.8 Library In the VB editor
'define a DSN-less connections string:
Const stADO As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & "Persist Security Info=True;" & _
"Initial Catalog=books;" & "Data Source=ISTSLCD3" 'Initial Catalog is db, datasource is servername
'Define and set the worksheet
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Set wbBook = ActiveWorkbook
Set wsSheet = wbBook.Worksheets("Sheet1")
'Open a connection to the database. It's important that the connection CursorLocation
'property be adUseServer.
Dim con As ADODB.Connection
Set con = New ADODB.Connection
With con
.CursorLocation = adUseServer
.Open stADO
.CommandTimeout = 0
End With
'This is the end of the connection code. The connection is closed later. Below is the actual code that does
'the inserting.
'Notice that strings are quoted in '""' and numbers are quoted with only ""
Dim strSQL As String
Dim irow As Integer
For irow = Selection(1).row To (Selection(1).row + Selection.Rows.Count - 1)
If WorksheetFunction.CountA(Selection) <= 7 Then 'this verifies that minimum number of columns is selected
MsgBox "Please select the rows you want to insert into SQL.", vbOKOnly, "SQL Insert"
Exit Sub
End If
strSQL = "Insert into dbo.authors (au_id, au_fname, au_lname, phone, address, city, state, zip) values _
('" & wsSheet.Cells(irow, 1).Value & "','" & wsSheet.Cells(irow, 2).Value & "', '" & wsSheet.Cells(irow, 3).Value _
& "', '" & wsSheet.Cells(irow, 4).Value & "', '" & wsSheet.Cells(irow, 5).Value & "', '" & wsSheet.Cells(irow, 6).Value _
& "', '" & wsSheet.Cells(irow, 7).Value & "', " & wsSheet.Cells(irow, 8).Value & ")"
'execute the insert statement
con.Execute strSQL
Next irow
''''''''''''''''''''''''''This closes the connection and could be in a seperate procedure as well.
'Clean up the connection:
con.Close
Set con = Nothing
MsgBox "The selected row(s) have been added succesfully to the SQL database.", vbOKOnly, "SQL Success"
End Sub
So what do ya'll think?
repetitive code amongst them. I want to consolidate them and put the repetitive code in one procedure and
then just call that procedure as needed. Pretty standard stuff, right?
The problem is that variables are created and called through out the procedures and if I start splitting up the
code it stops working. I turn to you guys (and gals) for help! How can I make my code less repetitve? I have
posted one block of code below. The code connects to an SQL server and then inserts data on the XLS
worksheet to the SQL database. I have another block that deletes, updates, and selects. I only posted the
insert block below.
Public Sub Insert()
'This is the connection code that I want stuffed in a seperate proc.
'Before running you must go to
'Tools--> References --> and add Micorsoft ActiveX Data Objects 2.8 Library In the VB editor
'define a DSN-less connections string:
Const stADO As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & "Persist Security Info=True;" & _
"Initial Catalog=books;" & "Data Source=ISTSLCD3" 'Initial Catalog is db, datasource is servername
'Define and set the worksheet
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Set wbBook = ActiveWorkbook
Set wsSheet = wbBook.Worksheets("Sheet1")
'Open a connection to the database. It's important that the connection CursorLocation
'property be adUseServer.
Dim con As ADODB.Connection
Set con = New ADODB.Connection
With con
.CursorLocation = adUseServer
.Open stADO
.CommandTimeout = 0
End With
'This is the end of the connection code. The connection is closed later. Below is the actual code that does
'the inserting.
'Notice that strings are quoted in '""' and numbers are quoted with only ""
Dim strSQL As String
Dim irow As Integer
For irow = Selection(1).row To (Selection(1).row + Selection.Rows.Count - 1)
If WorksheetFunction.CountA(Selection) <= 7 Then 'this verifies that minimum number of columns is selected
MsgBox "Please select the rows you want to insert into SQL.", vbOKOnly, "SQL Insert"
Exit Sub
End If
strSQL = "Insert into dbo.authors (au_id, au_fname, au_lname, phone, address, city, state, zip) values _
('" & wsSheet.Cells(irow, 1).Value & "','" & wsSheet.Cells(irow, 2).Value & "', '" & wsSheet.Cells(irow, 3).Value _
& "', '" & wsSheet.Cells(irow, 4).Value & "', '" & wsSheet.Cells(irow, 5).Value & "', '" & wsSheet.Cells(irow, 6).Value _
& "', '" & wsSheet.Cells(irow, 7).Value & "', " & wsSheet.Cells(irow, 8).Value & ")"
'execute the insert statement
con.Execute strSQL
Next irow
''''''''''''''''''''''''''This closes the connection and could be in a seperate procedure as well.
'Clean up the connection:
con.Close
Set con = Nothing
MsgBox "The selected row(s) have been added succesfully to the SQL database.", vbOKOnly, "SQL Success"
End Sub
So what do ya'll think?