Insert records into SQL table from Excel 2007 VBA

Hi all.

I have an Excel template I've been working with and I want it to be able to INSERT 2 fields into the tblXref_AddOEMID table: OEM_ID and Insert_Date whenever the value in column B = N/A

The worksheet has 2 columns: OEM_ID (column A) and myItem_ID (column B).

The range should be established by column A, as I don't know how many rows of data there will be, it varies everytime.

I was testing some code out and I was able to insert one row of data but how do I get it to insert everytime the myItem_ID has N/A?

Also below I provided some code on how I've been able to insert data from a SQL table into the worksheet.

Any ideas? Thank you in advance!
'Test code to insert one value    
Dim cnt As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim stCon As String, stSQL As String
    Set cnt = New ADODB.Connection
    Set rst = New ADODB.Recordset
     
    stCon = "DRIVER=SQL Server;SERVER=myServer;UID=myID;APP=Microsoft Office 2003;WSID=WS18;DATABASE=myDatabase;Trusted_Connection=Yes"
    cnt.ConnectionString = stCon
 
     cnt.Open
    
   
  
    stSQL = "Insert INTO tblXref_AddOEMID (OEM_ID, Insert_Date)"
    stSQL = stSQL & " Select 'ABC', GetDate()"
    rst.Open stSQL, cnt, adOpenStatic, adLockReadOnly, adCmdText

    If CBool(rst.State And adStateOpen) = True Then rst.Close
    Set rst = Nothing
    If CBool(cnt.State And adStateOpen) = True Then cnt.Close
    Set cnt = Nothing

'======================================
'Code used to insert data into spreadsheet from SQL table, it knows when to stop

' Create a connection object.
Dim cnPubs3 As ADODB.Connection
Set cnPubs3 = New ADODB.Connection

' Provide the connection string.
Dim strConn3 As String

'Connect to the database on server
strConn3 = "DRIVER=SQL Server;SERVER=myserver;UID=myID;APP=Microsoft Office 2003;WSID=WS10;DATABASE=myDatabase;Trusted_Connection=Yes"

'Now open the connection.
cnPubs3.Open strConn3
' Create a recordset object.
Dim rsPubs3 As ADODB.Recordset
Set rsPubs3 = New ADODB.Recordset

With rsPubs3
    ' Assign the Connection object.
    .ActiveConnection = cnPubs3

    Dim I3
    Dim rng3 As Range
    Set rng3 = Range("A3")
    Dim strSQL3 As String
    
    While rng3.Value <> ""

    strSQL3 = "SELECT ItemNumber FROM dbo.myTable WHERE OEM_ID = '" & rng3.Value & "'"

       .Open strSQL3
      
      ' check there's a record and if there is put the data in the same row as the item number
      ' across columns starting in column B
      If Not rsPubs3.EOF Then
        
         rsPubs3.MoveFirst ' change rst to the name of your recordset
                
            For I3 = 0 To rsPubs3.Fields.Count - 1
          
                rng3.Offset(, I3 + 1).Value = rsPubs3.Fields(I3).Value
                                      
            Next I3
            
      End If

      rsPubs3.Close
    
      Set rng3 = rng3.Offset(1)
            
    Wend
   
End With

cnPubs3.Close

Set rsPubs3 = Nothing
Set cnPubs3 = Nothing

Open in new window

printmediaAsked:
Who is Participating?
 
printmediaAuthor Commented:
I got it to work. Below is the code I used.
Dim cnt As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim stCon As String, stSQL As String
    Set cnt = New ADODB.Connection
    Set rst = New ADODB.Recordset
     
    stCon = "DRIVER=SQL Server;SERVER=myServer;UID=myID;APP=Microsoft Office 2003;WSID=WS8;DATABASE=myDatabase;Trusted_Connection=Yes"
    cnt.ConnectionString = stCon
 
    cnt.Open

    Dim rng11 As Long

    With Worksheets("Sheet1")

        rng11 = .Range("A" & Rows.Count).End(xlUp).Row
    
        Set MR11 = Range("B3:B" & rng11)
    
        For Each cell In MR11

            If cell.Value = "N/A" Then
    
             stSQL = "Insert INTO myTableA (ID, Insert_Date)"
             stSQL = stSQL & " Select '" & cell.Offset(, -1).Value & "' , GetDate()"
             rst.Open stSQL, cnt, adOpenStatic, adLockReadOnly, adCmdText
           
            Else
           
            End If
        Next
    
        If CBool(rst.State And adStateOpen) = True Then rst.Close
    
             Set rst = Nothing
             
        If CBool(cnt.State And adStateOpen) = True Then cnt.Close
    
             Set cnt = Nothing

End With

Open in new window

0
 
regevhaCommented:
To help us provide you with an accurate solution that matches your data processing requirements, please provide sample input data and the expected result.
0
 
printmediaAuthor Commented:
Attached is a sample Excel file with data
Sample-Excel.xlsx
0
 
printmediaAuthor Commented:
I figured it out.
0
 
regevhaCommented:
Thanks for posting the code. I am glad you found a solution by yourself.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.