How to import mysql data with VBA without unconfiguring the text

Question

How to import mysql data with VBA without unconfiguring the text

Navigation

#1 by (1 votes)

2

I have a macro that connects to mysql and executes a query that is in a text file and adds the records to an excel sheet. It occurs that the text comes unconfigured. Example: Correct: CALL QTDE MADE (CPF VISION) How It Comes: CALL QTDE HELD (VISION CPF)

How do I configure and where does the query bring me the results without problems with text?

Connection procedure:

Sub Conexao(SQL)

    On Error GoTo Error


    Set myCon = New ADODB.Connection
    myCon.ConnectionString = _
    "driver={mysql odbc 5.2 Unicode Driver};" & _
    "server=172.17.0.140;database=DWH_RBZ;uid=usuario;pwd=senha;Option=3;"
    myCon.CursorLocation = adUseClient
    myCon.Open

    
    With myCmd
          Set .ActiveConnection = myCon
          .CommandType = adCmdText
          .CommandText = SQL
    End With
    
    With myRS
       .LockType = adLockPessimistic
       .CursorType = adOpenKeyset
       .CursorLocation = adUseClient
       .Open myCmd
    End With

    Exit Sub

Error:
    MsgBox Err.Number & vbCrLf & Err.Description, vbExclamation, "ERRO DE CONEXAO COM O BANCO"
    

End Sub

Procedure that pastes the query data into the worksheet

Sub Roda_Query_1()
Dim SQL As String
Dim dias As Integer

    dias = CDate(Dt_Final) - CDate(Dt_Incio)
    For i = 0 To dias
        SQL = LinhaALinhaLer
        SQL = Mid(SQL, 4, 1000000)
        SQL = Replace(SQL, "(@inicio)", "('" & Format(Dt_Incio, "yyyy-mm-dd") & " 00:00:00')")
        SQL = Replace(SQL, "(@fim)", "('" & Format(Dt_Final, "yyyy-mm-dd") & " 23:59:59')")
        
        Conexao (SQL)
        
        If myRS.RecordCount = 0 Then
            MsgBox ("Erro na consulta do relatório")
            myRS.Close
            myCon.Close
            Exit Sub
        End If
        
        
        If VerificaExistePlan("Produtividade") = False Then
           Call Cria_Plan_Dados
        End If
        Sheets("Dados").Range("A2").CopyFromRecordset myRS
    Next i



    myRS.Close
    myCon.Close
   End Sub

mysql vba

asked by anonymous 18.03.2015 / 20:58

1 answer

Android: how to group buttons? NFe XML decapsulation

score 1 · Answer 1

I believe that setting the Charset for the connection will solve the problem.

myCon.Charset = "utf-8"

Also check the table's Charset. You can also test using the ANSI driver instead of Unicode .

I hope to help, anything post a feedback that we will test with more time to find the solution.