?
Solved

Insert records into SQL table from Excel 2007 VBA

Posted on 2011-10-13
5
Medium Priority
?
577 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:printmedia
  • 3
  • 2
5 Comments
 
LVL 6

Expert Comment

by:regevha
ID: 36963179
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
 

Author Comment

by:printmedia
ID: 36963195
Attached is a sample Excel file with data
Sample-Excel.xlsx
0
 

Accepted Solution

by:
printmedia earned 0 total points
ID: 36963846
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
 

Author Closing Comment

by:printmedia
ID: 36984612
I figured it out.
0
 
LVL 6

Expert Comment

by:regevha
ID: 36964067
Thanks for posting the code. I am glad you found a solution by yourself.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question