问从结果中排除所选行
EN

Stack Overflow用户

提问于 2020-04-30 17:25:37

回答 1查看 61关注 0票数 0

我以前从来没有在vb脚本上工作过。我需要根据excel表格中的一些示例数据生成insert语句。

我在一个vb宏上工作，它为工作表中的所有列生成结果。

例如，对于两行，它将生成如下结果：

insert into [Col1] ([col2], [col3], [col4], [col5c], [col6])
values
('ABC', 'PF', 'Loan_amount', 'GDTG/_Prod', 'IDW/logs'), 
('DFGC', 'PF', 'Investment', 'GDTG/_Prod', 'ERW/logs')

现在，我有了一个更改的要求。我在开头添加了另一个列，其中包含一些值，如new、old、delete等。

我希望只在"new“标记的行上生成插入。例如，在下面的代码中，我只想要新的结果：Sample data

另外，下面是我当前的脚本：

Sub GetInsertSQL()
    Dim wsSrc As Worksheet: Set wsSrc = ActiveSheet
    Dim LastRow As Long: LastRow = wsSrc.UsedRange.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim LastCol As Long: LastCol = wsSrc.UsedRange.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    Dim i As Long, j As Long
    Dim strQuery As String
    Dim strOutput As String

    strQuery = ""
    For j = 1 To LastCol
        strQuery = strQuery + "[" + CStr(wsSrc.Cells(1, j)) + "], "
    Next j
    strQuery = Left(strQuery, Len(strQuery) - 2)
    strQuery = "insert into [" + wsSrc.Name + "] (" + strQuery + ")"
    strOutput = strQuery + Chr(13) + Chr(10) + "values"

    For i = 2 To LastRow
        strQuery = ""
        For j = 1 To LastCol
            strQuery = strQuery + "'" + Replace(CStr(wsSrc.Cells(i, j).Text), "'", "''") + "', "
        Next j

        strQuery = "(" + Left(strQuery, Len(strQuery) - 2) + "), "
        strOutput = strOutput + Chr(13) + Chr(10) + strQuery
    Next i

    strOutput = Left(strOutput, Len(strOutput) - 2)
    OutputForm.txtOutput.Text = strOutput
    OutputForm.Show vbModal
End Sub

excel

vba

回答 1

Stack Overflow用户

回答已采纳

发布于 2020-04-30 19:28:09

Sub GetInsertSQL()
    Dim wsSrc As Worksheet: Set wsSrc = ActiveSheet
    Dim LastRow As Long: LastRow = wsSrc.UsedRange.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim LastCol As Long: LastCol = wsSrc.UsedRange.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    Dim i As Long, j As Long
    Dim strQuery As String
    Dim strOutput As String

    strQuery = ""
    For j = 1 To LastCol
        strQuery = strQuery + "[" + CStr(wsSrc.Cells(1, j)) + "], "
    Next j
    strQuery = Left(strQuery, Len(strQuery) - 2)
    strQuery = "insert into [" + wsSrc.Name + "] (" + strQuery + ")"
    strOutput = strQuery + Chr(13) + Chr(10) + "values"

    For i = 2 To LastRow
        strQuery = ""
        'Adding this if condition to check if value in 1st column is New
        If (lcase(wsSrc.Cells(i, 1).Text) = "new") Then
        For j = 1 To LastCol
                strQuery = strQuery + "'" + Replace(CStr(wsSrc.Cells(i, j).Text), "'", "''") + "', "
        Next j

        strQuery = "(" + Left(strQuery, Len(strQuery) - 2) + "), "
        strOutput = strOutput + Chr(13) + Chr(10) + strQuery
        End If
    Next i

    strOutput = Left(strOutput, Len(strOutput) - 2)
    OutputForm.txtOutput.Text = strOutput
    OutputForm.Show vbModal
End Sub

票数 1

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/61520133

复制

相似问题

问从结果中排除所选行
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从结果中排除所选行EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从结果中排除所选行
EN