Option Base 1
Global CurrentData
Sub Collect()
OpenDB
Dim BB()
'Whole Sheet: SELECT * FROM [Sheet1$]
'Specific Range: SELECT * FROM [Sheet1$A1:E50]
'Named Range: SELECT * FROM [MyNamedRange]
'Filtering: SELECT * FROM [Sheet1$] WHERE [ColumnName] = 'Value'
'Joins: You can perform standard SQL JOIN operations across different worksheets within the same connection.
'Date Formats: Use the # symbol for dates in your WHERE clause, e.g., WHERE [DateColumn] = #2026-01-01#.
With ThisWorkbook.Sheets("List")
rr = Range("A1000").End(xlUp).Row
RG = "List$" & "A1:P" & rr
End With
strSQL = "SELECT * FROM [List$] WHERE [Status] = 'B' "
strSQL = "SELECT * FROM [" & RG & "]"
Mydata = GetData(strSQL, P)
ReDim BB(UBound(Mydata), 1)
For rdx = 1 To UBound(Mydata)
k = 3

Ty = TypeName(Mydata(rdx, 8))
Mydata(rdx, 16) = CV(Mydata(rdx, 5)) & CV(Mydata(rdx, 8)) & CV(Mydata(rdx, 1))
For cdx = 1 To UBound(Mydata, 2)
If IsNull(Mydata(rdx, cdx)) Then
Mydata(rdx, cdx) = Empty
k = 3
End If
Next cdx
Next rdx

BB = WorksheetFunction.Sort(BB, 1, -1, False)
dty = TypeName(BB(1, 1))
Mydata = Application.WorksheetFunction.Sort(Mydata, 16, -1, False)
ThisWorkbook.Sheets("OutPut").Range("A2").Resize(UBound(BB), 16) = Mydata
End Sub
Function CV(vl)
If IsNull(vl) Then vl = 0
'vl = WorksheetFunction.RandBetween(22, 99)
CV = Right("0000" & CStr(vl), 3)

End Function