Re: Update Data From Excel

[Bill <brobins3d@xxxxxxxxx>]

Bill wrote:
> Can anyone supply me with an example of VBA code that updates a table on 
> the 400 from data in the spreadsheet?  I can write downloads in my sleep 
> now, but I've never written an upload in Excel.  I'd prefer an example 
> written with Option Explicit.

Turns out it was a pretty simple setup.  Here's a code example for the 
archives.

Sub subCheckForUpdate()

    Dim intChanged As Integer, intRows As Integer, x As Integer
    Dim lngFromDate As Long, lngTodate As Long, lngExpireDate As Long, 
lngRetestDate As Long
    Dim rngData As Range, rngThisRow As Range
    Dim cLot As String, cProd As String, strMessage As String, strSQL 
As String
    Dim shtData As Worksheet

    Dim conn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rst As ADODB.Recordset

    Set shtData = Worksheets("Data")
    shtData.Activate
    shtData.Range("A2").Select
    Set rngData = ActiveCell.CurrentRegion
    intRows = rngData.Rows.Count
    intChanged = 0

    lngFromDate = Worksheets("Controls").Range("B10").Value
    lngTodate = Worksheets("Controls").Range("B11").Value

    Set conn = CreateObject("ADODB.Connection")
    conn.Provider = "IBMDA400"
    conn.Properties("Data Source") = "QS1031561"
    conn.Open
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = conn
    cmd.CommandType = adCmdText


    For x = 2 To intRows    ' Skip the headings row.
        Set rngThisRow = shtData.Range("A1").Offset((x - 1), 0).EntireRow
        lngExpireDate = shtData.Range("A1").Offset((x - 1), 5).Value

        If lngExpireDate >= lngFromDate And lngExpireDate <= lngTodate Then
            cLot = shtData.Range("A1").Offset((x - 1), 1).Value
            cProd = shtData.Range("A1").Offset((x - 1), 3).Value
            lngRetestDate = shtData.Range("A1").Offset((x - 1), 6).Value

            strSQL = getSQLString(cLot, cProd, lngExpireDate, 
lngRetestDate)
            cmd.CommandText = strSQL
            Set rst = cmd.Execute
            intChanged = intChanged + 1
        End If

    Next x

'''    rst.Close
    conn.Close

    strMessage = CStr(Format(intChanged)) & " Records were changed."
    MsgBox strMessage, 0, "Lot Expiration Reset System"

End Sub