时间戳

首页 > 解决方案 > 从结果中排除选定的行

excel - 从结果中排除选定的行

问题描述

我以前从未使用过 vb 脚本。我需要根据 excel 表中的一些示例数据生成插入语句。

我研究了一个 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')

现在，我有一个改变的要求。我在开头添加了另一列，其中包含一些值，如新、旧、删除等。

我希望仅在“新”标记的行上生成插入。例如在下面我只想要新的结果： 示例数据

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

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

标签： excelvba

解决方案

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

推荐阅读