RESOLVED: Append data in Excel worksheet from text file

[kasteel]

Hi

With the following code I can import comma delimited data from a text file into Excel.

The problem is that I cannot seem to figure out how to append the data from the text file to an existing Excel file. (adding to a new row each time.)

Any ideas? Would appreciate any help.

        With objExcel
            .Visible = True
            .Workbooks.OpenText(Filename:="c:\scripts1\New Text Document.txt", StartRow:=1, Tab:=True, Semicolon:=False, Comma:=True, Space:=True, Other:=False)
        End With

 

[kasteel]

I have tried the code below and this insert data into a new sheet each time. Almost there

        Dim cnn As New ADODB.Connection
        Dim strSQL As String

        cnn.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=c:\logs\test.xls;Extended Properties=Excel 8.0;")

        strSQL = "SELECT * INTO [TextImportSheet3] FROM [Text;DATABASE=c:\logs\import].[outputfile1.txt]"

  cnn.Execute(strSQL)
        cnn.Close()

How could I change this to insert the data into a new row on the same sheet instead of creating a new sheet each time?

I tried the code below but it does not work. Does not recognize the first column name. And the coumn name is spelled correctly in my txt file.

        strSQL = "Insert INTO [TextImportSheet1] (MyName, LastName) SELECT MyName, LastName FROM [Text;DATABASE=c:\logs\import].[outputfile1.txt]"

 

[kasteel]

Now I am using ADO.NET which actually works well except for one thing. Each time it records the information into Excel it also records the last Column Name from the text file. Any idea how I can prevent that?

   Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=c:\logs\test.xls;Extended Properties=""Excel 8.0;HDR=NO;""")

        ExcelConnection.Open()

        Dim ImportCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [TextImportSheet] (MyName, LastName) SELECT * FROM [Text;HDR=NO;DATABASE=c:\logs\import].[outputfile1.txt]", ExcelConnection)

        ImportCommand.ExecuteNonQuery()
        ExcelConnection.Close()

 

[kasteel]

AHHHHHH

This does the trick:

   Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\logs\book1.xls;Extended Properties=""Excel 8.0;HDR=YES;""")

        ExcelConnection.Open()

        Dim ImportCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [TextImportSheet$] (SwipeCard,FirstName,LastName,Faculty,Degree,IDNr,EndDate,mail) SELECT * FROM [Text;HDR=YES;DATABASE=c:\logs\import].[output.txt]", ExcelConnection)

        ImportCommand.ExecuteNonQuery()
        ExcelConnection.Close()