Push Excel Range to SQL Table via VBA

Question

I am in need of pushing a range in Excel to a new row in an SQL table each time an associate executes a VBA macro. So far, I have segregated the data into a single row and multiple columns (110 cells of data in total) in Excel. My problem right now is stemming from how to insert each one of these individual cells from the Excel sheet into the corresponding column and the first empty row in an SQL table. I've done some pretty extensive searches of the internet and have found nothing remotely close to what I am trying to do.

Is there a correct procedure that would allow me to dump a 110-column row into the first empty row in an SQL table?

I have the tables written and I have the range set:

Set DataBaseData = ActiveSheet.Range("DO1:HT1")

Beyond this I have no idea in which manner to open a connection with the Server, Database and Table. This is what I've winged so far:

Sub Connection()
    Dim Conn As ADODB.Connection
    Dim Command As ADODB.Command

    Set Conn = New ADODB.Connection
    Set Command = New ADODB.Command

    Dim i As Integer
    Dim columnnumber As Integer

    i = 0

    Conn.ConnectionString = "Provider=SQLOLEDB; Data Source=[Nope];Initial Catalog=[NopeNope];User ID=[NopeNopeNope];Password=[AbsolutelyNot]; Trusted_Connection=no;"

    Conn.Open
    Command.ActiveConnection = Conn
End Sub

Any help would be greatly appreciated.

If you have the curiosity as to what I'm trying to do: I'm pushing a series of data from a CMM to the Database so I can store the data for the needed amount of time, and call that data back to PowerBI and Minitab.

There are no "empty rows" in a database table - only records which have been inserted. You will need to construct a SQL insert statement to add a record, or open an editable recordset, call AddNew on that recordset, add your field values then call Update see vb-helper.com/howto_ado_insert.html or learn.microsoft.com/en-us/sql/ado/reference/ado-api/… for example. — Tim Williams
– Tim Williams, Commented Nov 14, 2019 at 4:54
Example of both approaches here: stackoverflow.com/questions/10708077/… — Tim Williams
– Tim Williams, Commented Nov 14, 2019 at 4:57
I assume you have considered querying excels from power query, negating the requirement to involve sql...? — LJ01
– LJ01, Commented Nov 14, 2019 at 8:42
@LJ01 I've considered it but ISO/IATF requires record retention for 10 years so I want to have somewhere to permanently put it and not have thousands of excel sheets floating around. — Noah Thomas
– Noah Thomas, Commented Nov 14, 2019 at 14:03

Noah Thomas · Accepted Answer · 2019-11-14 19:21:05Z

I was able to successfully write an entire row from Excel to an SQL Database using the following:

Sub Connection()

  Const Tbl As String = "NEIN"

        Dim InsertQuery As String, xlRow As Long, xlCol As Integer

        Dim DBconnection As Object

        Set DBconnection = CreateObject("ADODB.Connection")

        DBconnection.Open "Provider=SQLOLEDB.1;Password=NEIN" & _
            ";Persist Security Info=false;User ID=NEIN" & _
            ";Initial Catalog=NEIN;Data Source=NEIN"

        InsertQuery = ""
        xlRow = 1   'only one row being used *as of now*, and that is the top row in the excel sheet
        xlCol = 119 'First column of data
        While Cells(xlRow, xlCol) <> ""

            InsertQuery = InsertQuery & "INSERT INTO " & Tbl & " VALUES('"

            For xlCol = 119 To 229 'columns DO1 to HT1
                InsertQuery = InsertQuery & Replace(Cells(xlRow, xlCol), "'", "''") & "', '"  'Includes mitigation for apostrophes in the data
            Next xlCol
            InsertQuery = InsertQuery & Format(Now(), "M/D/YYYY") & "')" & vbCrLf 'The last column is a date stamp, either way, don't forget to close that parenthesis

        Wend

        DBconnection.Execute InsertQuery
        DBconnection.Close
        Set DBconnection = Nothing

End Sub

Collectives™ on Stack Overflow

Push Excel Range to SQL Table via VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related