IMPORTAR PARA EXCEL

Private Sub Import()
    Dim linhaAtual, numLinhasPlan   As Long
    Dim abrePlan                    As String
    Dim valorPlan                   As String
    Dim strSQL, Sql_into            As String
    Dim insere_dados                As String
    Dim varConta                    As String
    Dim varValor                    As String
    Dim varData                     As String
    Dim nomeArquivo                 As String
    Dim tipoArquivo                 As String
    
    numLinhasPlan = 1
    linhaAtual = 1
'***************************************************************************************************'   VERIFICA E ARMAZENA NOME E DATA ARQUIVO
'***************************************************************************************************    teste = edit1.Text
    cont = 8
    Do While True
        a = Right(teste, cont)
        If (Mid(a, 1, 1) = "\") Then
            tipoArquivo = Right(a, 3)
            nomeArquivo = Mid(a, 2, cont - 5)
            Exit Do
        End If
        cont = cont + 1
    Loop
    dataArquivo = Date
    dataArquivo = Format(dataArquivo, "mm/dd/yyyy")
    Set tbARQUIVO = gdb.Execute("select * from tbArquivo WHERE nome = '" & nomeArquivo & "' ")
    If Not (Mid(nomeArquivo, 5, 4) = "AMEX") Then
        MsgBox "Arquivo não corresponde a conta Amex! ", vbInformation + vbOKOnly, "Crédito em Conta"
        Exit Sub
    End If
    If Not tbARQUIVO.RecordCount = 0 Then
        MsgBox "Arquivo já foi importado anteriormente, favor verificar! ", vbInformation + vbOKOnly, "Crédito em Conta"
        Exit Sub
    End If
    Set tbARQUIVO = gdb.Execute("select * from tbArquivo")
    insere_sql = "insert into tbArquivo(nome, dataImportacao, tipo) values('" & nomeArquivo & "'," _
    & "#" & dataArquivo & "#, '" & tipoArquivo & "')"
    gdb.Execute (insere_sql)
    If edit1.Text <> "" Then
        Set oleExcel = CreateObject("Excel.Application")
        Set oleWorkBook = oleExcel.Workbooks.Open(edit1.Text)
        temp = Len(edit1.Text)
        varData = Mid(edit1.Text, (temp) - 7, 2) & "/" & Mid(edit1.Text, (temp) - 9, 2) & "/" & Mid(edit1.Text, (temp) - 5, 2)
        numLinhasPlan = oleWorkBook.Application.Cells(65536, 1).End(xlUp).Row
        Do While (linhaAtual <= numLinhasPlan)
            If oleWorkBook.Application.Cells(linhaAtual, 2) <> "" Then
                If IsNumeric(oleWorkBook.Application.Cells(linhaAtual, 2)) Then
                    varConta = (oleWorkBook.Application.Cells(linhaAtual, 2))
                    varConta = CDbl(Trim(Mid(varConta, 1, 13)))
                    varValor = ((oleWorkBook.Application.Cells(linhaAtual, 6)))
                    varValor = Format(varValor, "#,##0.00")
                    varValor = TrocaCaracter(varValor, ".", "")
                    varValor = TrocaCaracter(varValor, ",", ".")
'***************************************************************************************************
'   select baseando-se na conta e valor1 (valor) + função para trocar "," pelo "."
'***************************************************************************************************
                   If Not gdb.Execute("select * From tbAmex WHERE tbAmex.valor = " & varValor & " And tbAmex.conta = '" & varConta & "' And tbAmex.data_regularizacao Is Null").RecordCount = 0 Then
                        Set tbAMEX = gdb.Execute("update tbAmex SET valor = " & varValor & ", data_regularizacao = #" & varData & "#  WHERE tbAmex.valor = " & varValor & " And tbAmex.conta = '" & varConta & "' And (tbAmex.data_regularizacao Is Null)")
                        statusBarCod.Panels.Item(1) = "Importando dados: " & varConta
                    Else
                        Set tbAMEX = gdb.Execute("select * from tbAmex")
                        insere_sql = "insert into tbAmex (conta, valor, data) values ('" & varConta & "', " & varValor & ",  #" & varData & "#)"
                        statusBarCod.Panels.Item(1) = "Importando dados: " & varConta
                        gdb.Execute (insere_sql)
                    End If
                End If
            End If
            linhaAtual = linhaAtual + 1
        Loop
        oleWorkBook.Application.Workbooks.Close
        Set oleExcel = Nothing
        Set oleWorkBook = Nothing
        Set oleWorkSheet = Nothing
        statusBarCod.Panels.Item(1) = "Pronto"
        MsgBox "Importação Concluida! ", vbInformation + vbOKOnly, "Crédito em Conta"
    End If
End Sub