mysql - EXCEL VBA 为多个变量赋值

Question

嗨，我正在尝试将 Excel 中的值插入到 mysql 数据库中。所以我的值(value)观是排成一行的。每行大约 20+ 个单元格。每个单元格必须输入到数据库中的特定列。

我需要的是我所拥有的简化代码。

SQLStr = "INSERT INTO submitteddrawings(Team,Name,MgtNo,JobNo,DrawingNo,Status,Version,SubMo,DwgSheet,ReusedDwg,PCChecked, N1A,N1B,N1C,N1D,N2A,N2B,N2C,N2D,N3A,N3B,N3C,N3D,N3E,N4A,N4B,N4C,N4D,N4E,N5A,N5B,N5C,N5D,N6,J1A,J1B,J1C,J2A,J2B,J2C,J2D,J2E,J3A,J3B,J3C,J3D,J3E,J3F,J3G) VALUES ('" & e & "', '" & f & "','" & g & "','" & h & "','" & i & "','" & j & "','" & k & "','" & l & "','" & m & "','" & n & "','" & o & "','" & p & "','" & q & "','" & r & "','" & s & "','" & t & "','" & u & "','" & v & "','" & w & "','" & x & "','" & y & "','" & z & "','" & aa & "','" & ab & "','" & ac & "','" & ad & "','" & ae & "','" & af & "','" & ag & "','" & ah & "','" & ai & "','" & aj & "', '" & ak & "','" & al & "','" & am & "','" & an & "','" & ao & "','" & ap & "','" & aq & "','" & ar & "','" & ass & "','" & at & "','" & au & "','" & av & "','" & aw & "','" & ax & "','" & ay & "','" & az & "','" & ba & "','" & bb & "','" & bc & "','" & bd & "')"

正如所见，存在大量令人眼花缭乱的变量。TIA

Answer 1

尝试理解下面的作用 - 为行中的特定范围创建一个字符串。这只是在 Excel 中为特定范围生成 SQL 语句的多种方法之一。

Option Explicit

Private Function GetInsertStatementForRowRange(InputRange As Range) As String
    Dim SQLStr As String, sValues As String, oRng As Range
    Const INSERT_BASE As String = "INSERT INTO submitteddrawings(Team,Name,MgtNo,JobNo,DrawingNo,Status,Version,SubMo,DwgSheet,ReusedDwg,PCChecked,N1A,N1B,N1C,N1D,N2A,N2B,N2C,N2D,N3A,N3B,N3C,N3D,N3E,N4A,N4B,N4C,N4D,N4E,N5A,N5B,N5C,N5D,N6,J1A,J1B,J1C,J2A,J2B,J2C,J2D,J2E,J3A,J3B,J3C,J3D,J3E,J3F,J3G) VALUES (<VALUES>)"
    sValues = ""
    For Each oRng In InputRange.Cells
        If Len(sValues) > 0 Then sValues = sValues & ", "
        sValues = sValues & "'" & oRng.Value & "'"
    Next
    SQLStr = Replace(INSERT_BASE, "<VALUES>", sValues)
    GetInsertStatementForRowRange = SQLStr
End Function

Sub SO45427529()
    Dim lRow As Long
    Const COLS_BASE As String = "E<R>:BD<R>"
    ' Below example is just for columns E to BD on row 2 of ActiveSheet
    ' You need to modify the Do-Loop to suit your useful range, assuming stop when it's empty
    lRow = 7 ' Start from row 7
    Do Until IsEmpty(Cells(lRow, "E"))
        ' Observe the output in Immediate Window
        Debug.Print "Row " & lRow, GetInsertStatementForRowRange(Range(Replace(COLS_BASE, "<R>", lRow)))
        lRow = lRow + 1
    Loop
End Sub