SQL Server generate SELECT script using Excel VBA

This blog article shows you how to build the SQL statement using VBA. The code needs to follow the following format of the Excel sheet.

– The Sheet name is the table name.

– Column A is the column name.

– Column B is the value of the WHERE condition.

– Column C is the condition such as =. LIKE and so.

– Column D is to check whether the column is string or numeric data type.

This VBA code is as follow, you can download the code from the link at the end of this blog article.

Dim sql As String

Sub GenSQL()

    Dim sqlsub As String

    Dim strSheet As String

    sql = “”

    strSheet = ActiveSheet.Name    

    sqlsub = “SELECT * FROM ” & strSheet & ” WHERE ”

    Gencondidtion

    sqlsub = sqlsub & sql

    Worksheets(“result”).Activate

    Range(“A1”).Value = sqlsub    

End Sub

Sub Gencondidtion()    

    Range(“A1”).Select

    While (ActiveCell.Value <> “”)

        If (ActiveCell.Offset(0, 2).Value = “=”) Then

            If ((ActiveCell.Offset(0, 3).Value = “numeric”)) Then

                sql = sql & NumericType

            Else

                sql = sql & StringType

            End If

        ElseIf (ActiveCell.Offset(0, 2).Value = “LIKE”) Then

            sql = sql & StringTypeLIKE

        End If        

        ActiveCell.Offset(1, 0).Select

        If (ActiveCell.Value <> “”) Then

            sql = sql & ” AND ”

        End If

    Wend

End Sub

Function StringType() As String

    Dim str As String    

    ‘str = ActiveCell & ” = ‘” & ActiveCell.Offset(0, 1).Value & “‘”

    str = ActiveCell & ” = ” & “‘” & ActiveCell.Offset(0, 1).Value & “‘”

    StringType = str

End Function

Function NumericType() As String

    Dim str As String    

    str = ActiveCell & ” = ” & ActiveCell.Offset(0, 1).Value

    NumericType = str

End Function

Function StringTypeLIKE() As String

    Dim str As String    

    str = ActiveCell & ” LIKE ‘%” & ActiveCell.Offset(0, 1).Value & “%'”

    StringTypeLIKE = str

End Function

Also: SQL Server generate Create Table script using Excel VBA

Above is the SQL statement generated when I executed the VBA code.

Source code down: https://github.com/chanmmn/vba/blob/master/2024/SQLQueryGenerate.xlsm/?WT.mc_id=DP-MVP-36769

Reference: https://learn.microsoft.com/en-us/office/vba/library-reference/concepts/getting-started-with-vba-in-office/?WT.mc_id=DP-MVP-36769