Create comma delimited string array from a column in excel to use in VBA sql query

You don't want to build an array but rather a string with the values you need. I stripped down your code to illustrate how this works:

Sub GetData()
   Dim values As String
   Dim mssql As String
   Dim TotalRows As Long
   Dim i As Long
   
   TotalRows = Rows(Rows.Count).End(xlUp).row

   For i = 1 To TotalRows
      values = values & "'" & Cells(i, 1).Value & "',"
   Next
   
   values = Mid(values, 1, Len(values) - 1)
   
   mssql = "SELECT Order.ID, Order.OrderDate, Order.Account " & _
           "FROM dbo.tbl_Order " & _
           "WHERE Order.ID IN (" & values & ")"

   MsgBox mssql
End Sub

Get Zero-Based From Column

• If you can use an array of strings (don't know SQL), for column A you could e.g. use:

  Dim strArray() As String: strArray = getZeroBasedFromColumn(Range("A2"))
  

• If you have to use a string then you will have to do:

  Dim strArray As String
  strArray = "'" & Join(getZeroBasedFromColumn(Range("A2")), "','") & "'"
  

  and use strArray without the parentheses.

• Qualify the first cell range (A2) appropriately e.g. WB.Worksheets("Sheet1").Range("A2").

• Both solutions use the following function.

The Code

Function getZeroBasedFromColumn( _
    FirstCell As Range) _
As Variant
    Const ProcName As String = "getZeroBasedFromColumn"
    On Error GoTo clearError
    
    If Not FirstCell Is Nothing Then
        Dim rg As Range
        Set rg = FirstCell.Resize(FirstCell.Worksheet.Rows.Count - FirstCell.Row + 1) _
            .Find("*", , xlFormulas, , , xlPrevious)
        If Not rg Is Nothing Then
            Set rg = FirstCell.Resize(rg.Row - FirstCell.Row + 1)
            Dim rCount As Long: rCount = rg.Rows.Count
            Dim Data As Variant
            If rCount = 1 Then
                ReDim Data(1 To 1, 1 To 1): Data(1, 1) = rg.Value
            Else
                Data = rg.Value
            End If
            Dim arr() As String: ReDim arr(0 To rCount - 1)
            Dim i As Long
            For i = 1 To rCount
                arr(i - 1) = CStr(Data(i, 1))
            Next i
            getZeroBasedFromColumn = arr
        End If
    End If

ProcExit:
    Exit Function
clearError:
    Debug.Print "'" & ProcName & "': Unexpected Error!" & vbLf _
              & "    " & "Run-time error '" & Err.Number & "':" & vbLf _
              & "        " & Err.Description
    Resume ProcExit
End Function

Sub getZeroBasedFromColumnTEST()
    Dim arr() As String: arr = getZeroBasedFromColumn(Range("A1"))
End Sub

An alternative (if you have the necessary database permissions) is to create a temporary table and use a JOIN in place of the WHERE IN().

Option Explicit

Sub GetData()
 
    ' get list of order numbers from Col A Sheet1 and COl B Sheet2
    Dim wb As Workbook, wsOut As Worksheet
    Dim rngA As Range, rngB As Range
    Dim ar, ar1, ar2, iLastRow As Long, SQL As String
    Dim i As Long, n As Long, id As String
    
    Set wb = ThisWorkbook
    Set wsOut = wb.Sheets("Sheet3")
 
    ' copy sheet1 Column A into array ar1
    iLastRow = Sheet1.Cells(Rows.Count, "A").End(xlUp).row
    Set rngA = Sheet1.Range("A1:A" & iLastRow)
    If rngA.Rows.Count = 1 Then
        ar1 = Array(0, rngA.Cells(1, 1).Value2)
    Else
        ar1 = Application.Transpose(rngA.Value2)
    End If
    'Debug.Print "A", LBound(ar1), UBound(ar1)

    ' copy sheet2 column B into array ar2
    iLastRow = Sheet2.Cells(Rows.Count, "B").End(xlUp).row
    Set rngB = Sheet2.Range("B1:B" & iLastRow)
    If rngB.Rows.Count = 1 Then
        ar2 = Array(0, rngB.Cells(1, 1).Value2)
    Else
        ar2 = Application.Transpose(rngB.Value2)
    End If
    'Debug.Print "B", LBound(ar2), UBound(ar2)

    ' connect to DB and create temporary table
    Dim oConn As New ADODB.Connection
    With oConn
        .ConnectionString = "driver={SQL Server};" & _
         "server=SERVER01;authenticateduser = TRUE;database=DATABASE01"
        .ConnectionTimeout = 30
        .Open
    End With

    oConn.Execute "CREATE TABLE #tmp (ID varchar(20) NOT NULL,PRIMARY KEY (ID ASC))"
    
    ' prepare insert query
    SQL = "INSERT INTO #tmp (ID) VALUES (?)"
    Dim cmd As New ADODB.Command
    With cmd
        .CommandType = adCmdText
        .ActiveConnection = oConn
        .CommandText = SQL
        .Parameters.Append .CreateParameter("p1", adVarChar, adParamInput, 20)
    End With
    
    ' insert array values into temp table
    Dim t0 As Single: t0 = Timer
    For Each ar In Array(ar1, ar2)
        oConn.BeginTrans
        For i = 1 To UBound(ar)
            id = Trim(ar(i))
            If Len(id) > 0 Then
                cmd.Execute n, id
            End If
        Next
        oConn.CommitTrans
        'Debug.Print i - 1 & " Inserted"
    Next
    n = oConn.Execute("SELECT COUNT(*) FROM #tmp")(0)
    'Debug.Print n & " records inserted into #tmp in " & Format(Timer - t0, "#.0 secs")

    ' select records using join as where filter
    SQL = " SELECT Ord.ID, Ord.OrderDate, Ord.Account" _
        & " FROM [tbl_Order] as Ord" _
        & " JOIN #tmp ON Ord.ID = #tmp.ID"
    
    ' output result
    Dim rs As New ADODB.Recordset
    Set rs = oConn.Execute(SQL, n)
    wsOut.Range("A:P").ClearContents
    
    ' header
    Dim fld, cell As Range
    Set cell = wsOut.Cells(5, 1)
    For Each fld In rs.Fields
        cell = fld.Name
        Set cell = cell.Offset(0, 1)
    Next
    
    ' data
    wsOut.Cells(6, 1).CopyFromRecordset rs
    oConn.Close
   
    ' end
    n = wsOut.Cells(Rows.Count, 1).End(xlUp).row - 6
    MsgBox n & " rows witten in " & Format(Timer - t0, "0.00 secs")
    
End Sub