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
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.
' Create a recordset object.
Dim rsPubs3 As ADODB.Recordset
Set rsPubs3 = New ADODB.Recordset
' Assign the Connection object.
.ActiveConnection = cnPubs3
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 & "'"
' 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
Set rng3 = rng3.Offset(1)
Set rsPubs3 = Nothing
Set cnPubs3 = Nothing