Desculpe pelo erro, sou novo em perguntas.
Aqui segue o codigo
Private Function PreecheRecordSet(ByVal data As String, _
ByVal OrdemServico As String, _
ByVal Tag As String, _
ByVal Area As String, _
ByVal Isolacao As String) As Recordset
On Error GoTo TrataErro
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim SQL As String
Dim sqlWhere As String
Dim sqlOrderBy As String
Dim i As Integer
Dim campo As Field
Dim myArray() As Variant
Set conn = New ADODB.Connection
With conn
.Provider = "Microsoft.JET.OLEDB.4.0"
.ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";Extended Properties=Excel 8.0;"
.Open
End With
SQL = "SELECT * FROM [Fornecedores$]"
'monta a cláusula WHERE
'NomeDaEmpresa
Call MontaClausulaWhere(txtNomeEmpresa.Name, "Data", sqlWhere)
'NomeDoContato
Call MontaClausulaWhere(txtNomeContato.Name, "OrdemServico", sqlWhere)
'Endereço
Call MontaClausulaWhere(txtEndereco.Name, "Tag", sqlWhere)
'Cidade
For i = 1 To lstCidades.ListCount
'verifica se o item está selecionado
If lstCidades.Selected(i - 1) Then
'Monta a cláusula WHERE com OR
Debug.Print lstCidades.List(i - 1) & " selecionado"
If sqlWhere <> vbNullString Then
sqlWhere = sqlWhere & " OR"
End If
sqlWhere = sqlWhere & " UCASE(Cadastro) LIKE UCASE('%" & Trim(lstCidades.List(i - 1)) & "%')"
End If
Next
'Telefone
Call MontaClausulaWhere(txtTelefone.Name, "Area", sqlWhere)
'Região
Call MontaClausulaWhere(txtRegiao.Name, "Isolacao", sqlWhere)
'faz a união da string SQL com a cláusula WHERE
If sqlWhere <> vbNullString Then
SQL = SQL & " WHERE " & sqlWhere
End If
'faz a união da string SQL com a cláusula ORDER BY
If cboOrdenarPor.ListIndex <> -1 Then
sqlOrderBy = " ORDER BY " & cboOrdenarPor.List(cboOrdenarPor.ListIndex, 0)
'define a direção
Select Case cboDirecao.ListIndex
Case Ascendente
sqlOrderBy = sqlOrderBy & " ASC"
Case Descendente
sqlOrderBy = sqlOrderBy & " DESC"
End Select
'une a query order ao sql
SQL = SQL & sqlOrderBy
End If
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
With rst
.ActiveConnection = conn
.Open SQL, conn, adOpenForwardOnly, _
adLockBatchOptimistic
End With
Set rst.ActiveConnection = Nothing
' Fecha a conexão.
conn.Close
Set PreecheRecordSet = rst
Exit Function
TrataErro:
Set rst = Nothing
End Function
