代码之家 › 专栏 › 技术社区 › Haminteu

插入值不为null的位置

excel vba sql

Haminteu · 技术社区 · 6 年前

我的Excel VBA上有以下代码:

Sub Button3_Click()
Dim conn As New ADODB.Connection

With Sheets("Sheet1")
    conn.Open "Provider=SQLNCLI11;Server=myServer;Database=myDatabase;Trusted_Connection=yes;"

    iRowNo = 6

    Do Until .Cells(iRowNo, 1) = ""
        dtDateTime = .Cells(iRowNo, 1)
        sTag = .Cells(iRowNo, 10)
        conn.Execute "insert into RawData(oDateTime,oTag) values ('" & dtDateTime & "', '" & sTag & "')"

        iRowNo = iRowNo + 1
        Application.StatusBar = "Processing..."
    Loop
    MsgBox "Upload success!", vbInformation, "AOA"

    Sheet1.Range("A6", "L100000").ClearContents
    Sheet1.Cells(6, 1).Select

    conn.Close
    Set conn = Nothing
End With
End Sub

如果检查上面的代码,则有两个值要添加到表中。在以下情况下是否可以跳过记录 sTag 单元格为空?
如果 雄鹿 单元格为空,然后插入下一条记录。像这样的。

请帮忙。
非常感谢。

2 回复 | 直到 6 年前

MichaÅ Turczyn 6 年前

您可以有条件地执行插入,即在 If 声明,您检查的地方 sTag 为了空虚。

If sTag <> "" Then
    conn.Execute "insert into RawData(oDateTime,oTag) values ('" & dtDateTime & "', '" & sTag & "')"
End If

PaichengWu 6 年前

尝试:

Sub Button3_Click()
    Dim conn As New ADODB.Connection

    With Sheets("Sheet1")
        conn.Open "Provider=SQLNCLI11;Server=myServer;Database=myDatabase;Trusted_Connection=yes;"

        iRowNo = 6

        Do Until .Cells(iRowNo, 1) = ""
            dtDateTime = .Cells(iRowNo, 1)
            sTag = .Cells(iRowNo, 10)
            If sTag <> "" Then
                conn.Execute "insert into RawData(oDateTime,oTag) values ('" & dtDateTime & "', '" & sTag &     "')"
            End If

            iRowNo = iRowNo + 1
            Application.StatusBar = "Processing..."
        Loop
        MsgBox "Upload success!", vbInformation, "AOA"

        Sheet1.Range("A6", "L100000").ClearContents
        Sheet1.Cells(6, 1).Select

        conn.Close
        Set conn = Nothing
    End With
End Sub

推荐文章