none
Help translating a VB.NET module into VBA RRS feed

  • Question

  • hello mates, im making and application using VBA in which i call a VB.Net exe. it reads a text file, changes the decimal points fro commas and then imports

    the fields separated by ; into an already existing access table.
    my problem is that its a bit problematic to call the exe and also i dont want to use so many different languages so id like to know if theres a way and if

    you could help me to translate the vb.net code into and vba function
    thanks in advance


    Imports System.Data.OleDb

    Module puntos_por_comas

        Sub Main()

            Dim sr As New System.IO.StreamReader("..\texto.txt")
            Dim origen As String = sr.ReadToEnd
            sr.Close()

            Dim destino As String = origen.Replace(".", ",")

            Dim sw As New System.IO.StreamWriter("..\Destino.txt")
            sw.Write(destino)
            sw.Close()

            Dim cnn As New OleDbConnection( _
           "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=..\;" & _
           "Extended Properties='TEXT;HDR=Yes;FMT=Delimited(Wink'")

            Using cnn
                Try
                    Dim sql As String = _
                    "SELECT * INTO [Importados] " & _
                    "IN '..\base.mdb'" & _
                    "FROM ..\destino#txt"

                    Dim cnn2 As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\base.mdb")
                    Dim SchemaTable As DataTable
                    cnn2.Open()

                    SchemaTable = cnn2.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, _
                    New Object() {Nothing, Nothing, "Importados"})

                    If SchemaTable.Rows.Count <> 0 Then
                        'table exists
                        Dim sql2 As String = "DROP TABLE Importados"
                        Dim cmd2 As New OleDbCommand(sql2, cnn2)
                        cmd2.ExecuteNonQuery()
                        cnn2.Close()
                    End If

                    cnn.Open()

                    Dim cmd As New OleDbCommand(sql, cnn)
                    Dim regAfect As Int32 = cmd.ExecuteNonQuery

                Finally
                    cnn = Nothing

                End Try

            End Using

        End Sub

    End Module
    Thursday, June 7, 2007 4:01 PM