none
Convert excel vba to vb.net Range.TexttoColumns RRS feed

  • Question

  • I am trying to parse text in a csv file opened in Excel.  This code worked perfect in vba but I keep getting an error that ConsecutiveDelimiter is not a member in vb.net.

    Here is the vba:

    Set xlApp = CreateObject("Excel.Application")

    With xlApp
    .Workbooks.OpenText FileName:="C:\Temp\Special\LockFile.csv", StartRow:=1, DataType:=xlDelimited, ConsecutiveDelimiter:=True, Space:=True

    .Worksheets(1).Activate
    .ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Activate
    xlast = .ActiveCell.Address

    For Each m In .Worksheets(1).Range("A1:" & xlast)
    xlApp.Worksheets(1).Range(m.Address).TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=True, Space:=True
    Next

    End With

    Here is my attempt at vb.net:

    With xlApp
     .Workbooks.OpenText(Filename:="C:\Temp\Special\LockFile.csv", StartRow:=1, DataType:=XlTextParsingType.xlDelimited, ConsecutiveDelimiter:=True, Space:=True)
     .Worksheets(1).Activate()
     .ActiveSheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Activate()
     xlast = .ActiveCell.Row  
     .Worksheets(1).Range("A1:A" & xlast).TextToColumns(DataType:=XlTextParsingType.xlDelimited)
    End With

    When opening the file none of the text is parsed so I created the second command to parse text (TextToColumns).

    Any help is greatly appreciated!



    • Edited by kay_some Thursday, August 14, 2014 5:05 PM
    Thursday, August 14, 2014 5:02 PM

All replies

  • You need to set a reference to Micorsoft.Office.Interop.Excel.dll - then take a look here:

    http://msdn.microsoft.com/fr-fr/library/microsoft.office.interop.excel.workbooks.opentext(v=office.11).aspx?cs-save-lang=1&cs-lang=vb#code-snippet-2

    Thursday, August 14, 2014 7:02 PM
  • Hi Kay_some,

    I failed to reproduce the issue, the error message seems a little strange. Which line caused this issue? Can you share a screenshot for the error message?

    Here is the test the code:

        Sub Main()
            Dim xlApp As Object
            Dim xlast As Integer
            xlApp = CreateObject("Excel.Application")
            With xlApp
                .Workbooks.OpenText(Filename:="C:\Users\UserName\Desktop\LockFile.csv", StartRow:=1, DataType:=XlTextParsingType.xlDelimited, ConsecutiveDelimiter:=True, Space:=True)
                .Worksheets(1).Activate()
                .ActiveSheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Activate()
                xlast = .ActiveCell.Row
                .Worksheets(1).Range("A1:A" & xlast).TextToColumns(DataType:=XlTextParsingType.xlDelimited)
            End With
        End Sub

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, August 21, 2014 4:41 PM
    Moderator