VBA to VB.net "Open filePath For Input As #1"

Question

Papa_Don 31 Posting Pro in Training

9 Years Ago

Hello Group,

I'm trying to convert some VBA code into VB.net. Here is that VBA code:

    Open filePath For Input As #1          ' filePath = the text file I need to read
    Do Until textRowNo = 8
        'discard these first 7 rows...
        Line Input #1, LineFromFile
        'this is the row counter
        textRowNo = (textRowNo + 1)
    Loop

Ultimately I need to throw out the first seven rows of the text file. But is there a VB.net equivalent to Open filePath For Input As #1?

After this is done, I want to continue and begin at line 8 and do the following:

        Do Until EOF(1)
        Line Input #1, LineFromFile
        arrival = Mid(LineFromFile, 1, 9)
        status = Trim(Mid(LineFromFile, 11, 3))
        typeText = Mid(LineFromFile, 18, 1)
        guestName = Trim(Mid(LineFromFile, 23, 28))
        roomType = Trim(Mid(LineFromFile, 54, 5))
        rateSched = Trim(Mid(LineFromFile, 60, 10))
        rateText = Mid(LineFromFile, 71, 11)
        roomRate = Val(rateText)
        CCtype = Mid(LineFromFile, 93, 2)
        CCNo = Mid(LineFromFile, 98, 9)
        textRowNo = (textRowNo + 1)

Here I'm moving specific portions of the line into the various variables. I then need to do the same for Line 9:

        If EOF(1) Then Exit Do      'checks to see if it's the end of file

        Line Input #1, LineFromFile
        departure = Mid(LineFromFile, 1, 9)
        source = Trim(Mid(LineFromFile, 48, 5))
        agent = Trim(Mid(LineFromFile, 122, 9))

And now I'm reading line 10

        Cells(rowNumber, 13).Value = los
        rowNumber = (rowNumber + 1)
        textRowNo = (textRowNo + 1)

' Row 10
        If EOF(1) Then Exit Do
       Line Input #1, LineFromFile
       textHead = Mid(LineFromFile, 52, 11)
        'this is a blank row - throw it away or it might be the end of file

        If textHead = "Reservation" Then
            Line Input #1, LineFromFile
            If EOF(1) Then 
                Exit Do
            End If
            Line Input #1, LineFromFile
            If EOF(1) Then 
                Exit Do
            End If
            Line Input #1, LineFromFile
            If EOF(1) Then 
                Exit Do
            End If
            Line Input #1, LineFromFile
            If EOF(1) Then 
                Exit Do
            End If
            Line Input #1, LineFromFile
            If EOF(1) Then 
                Exit Do
            End If
            Line Input #1, LineFromFile
            If EOF(1) Then 
                Exit Do
            End If
            textRowNo = (textRowNo + 5)
        End If
        textRowNo = (textRowNo + 1)
    Loop                ' Loop the code begining at line 11
    Close #1

Are there thoughts on how I might convert this VB.net?

Thanks,
Don

text vb.net vba

4 Contributors
8 Replies
9K Views
23 Hours Discussion Span
Latest Post 9 Years Ago Latest Post by Reverend Jim

All 8 Replies

cgeier 187 Junior Poster

9 Years Ago

The following would be a close translation:

Dim fn As String = "C:\MyTextFile.txt"
Dim sr As New StreamReader(fn)
Dim LineFromFile As String = Nothing
Dim textRowNo As Integer = 0
Dim arrival As String = Nothing
Dim status As String = Nothing

'...

While Not sr.EndOfStream
    textRowNo += 1
    LineFromFile = sr.ReadLine()

    If textRowNo > 8 Then
        arrival = Mid(LineFromFile, 1, 9)
        status = Trim(Mid(LineFromFile, 11, 3))

        '...

    End If

End While

sr.close()

Note: The above code is a translation of the code you posted and hasn't been reviewed for efficiency.

Click here for the resource code was translated from.

Edited 9 Years Ago by cgeier

Santanu.Das commented: Fine work +4

Reverend Jim 4,780 Hi, I'm Jim, one of DaniWeb's moderators.

9 Years Ago

I've noticed that a lot of your recent questions could be answered by even a cursory reading of just about any intro to vb.net programming book or one of the many similar (free) tutorial web sites. Might I suggest you get one and read it?

Edited 9 Years Ago by Reverend Jim

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

cgeier 187 Junior Poster · Answer 1 · 2015-01-08T05:25:03+00:00

Alternatively, read all text (the entire file). Then use split to split using the line delimiter--storing the result in an array. Then loop through the array as necessary.

Santanu.Das 125 Santanu Das · Answer 2 · 2015-01-08T07:00:17+00:00

I want to continue and begin at line 8 and do the following:

To throw the blank lines you can use the condition

If (textRowNo >= 8) And (Trim(LineFromFile) <> "") Then
        arrival = Mid(LineFromFile, 1, 9)
        status = Trim(Mid(LineFromFile, 11, 3))
        '...


    End If

Papa_Don 31 Posting Pro in Training · Answer 3 · 2015-01-09T00:02:26+00:00

Rev Jim,

I've purchased "Mastering Visual Basic 2010" but I don't see anything that suggests converting VBA code. I've also taken a couple of college courses in VB.net which were awesome. The codes suggestions above look pretty good. The next thing for me to do is to try them and build around them.

My assumption was I'd use StreamReader as it would allow me to loop through it which is exactly what I need to do. My biggest challenge comes when I get further down the text file and I start seeing header information from the actual output file that our system is creating (it's actually a multi-page report). I have to scroll through those 7 lines again as I did at the very beginning.

With these suggestions, I'll work from here and post what I actually do for someone looking to do the same thing in the future.

Don

Santanu.Das 125 Santanu Das · Answer 4 · 2015-01-09T03:03:33+00:00

Santanu.Das 125 Santanu Das

9 Years Ago

Is it by Evangelos Petroutsos ?

Papa_Don 31 Posting Pro in Training · Answer 5 · 2015-01-09T03:17:39+00:00

Shark 1, Yes, "Mastering Visual Basic 2010" is by Evangelos Petroutsos.

After reading earlier posts and then testing, it dawned on me that the code LineFromFile = sr.ReadLine() reads the next line. Further, While Not sr.EndOfStream is actually a loop. Therefore my code now looks like this:

            Dim sr As New StreamReader(filePath)
            Dim LineFromFile As String = Nothing
            Dim textRowNo As Integer = 0
            Dim arrival As String = Nothing
            Dim status As String = Nothing
            rowNumber = 5
            textRowNo = 1

            While Not sr.EndOfStream
                LineFromFile = sr.ReadLine()
                If (textRowNo >= 8) Then
                    arrival = Mid(LineFromFile, 1, 9)
                    status = Trim(Mid(LineFromFile, 11, 3))
                    typeText = Mid(LineFromFile, 18, 1)
                    guestName = Trim(Mid(LineFromFile, 23, 28))
                    roomType = Trim(Mid(LineFromFile, 54, 5))
                    rateSched = Trim(Mid(LineFromFile, 60, 10))
                    rateText = Mid(LineFromFile, 71, 11)
                    roomRate = Val(rateText)
                    CCtype = Mid(LineFromFile, 93, 2)
                    CCNo = Mid(LineFromFile, 98, 9)

                    textRowNo = textRowNo + 1

                    LineFromFile = sr.ReadLine()
                    departure = Mid(LineFromFile, 1, 9)
                    source = Trim(Mid(LineFromFile, 48, 5))
                    agent = Trim(Mid(LineFromFile, 122, 9))

                    sheet.Cells(rowNumber, 1).Value = arrival
                    sheet.Cells(rowNumber, 2).Value = departure
                    sheet.Cells(rowNumber, 3).Value = status
                    sheet.Cells(rowNumber, 4).Value = typeText
                    sheet.Cells(rowNumber, 5).Value = guestName
                    sheet.Cells(rowNumber, 6).Value = roomType
                    sheet.Cells(rowNumber, 7).Value = rateSched
                    sheet.Cells(rowNumber, 8).Value = roomRate
                    sheet.Cells(rowNumber, 9).Value = CCtype
                    sheet.Cells(rowNumber, 10).Value = CCNo
                    sheet.Cells(rowNumber, 11).Value = source
                    sheet.Cells(rowNumber, 12).Value = agent

                    departdate = CDate(sheet.Cells(rowNumber, 2).Value)
                    arrvdate = CDate(sheet.Cells(rowNumber, 1).Value)
                    los = (departdate.Subtract(arrvdate))
                    sheet.Cells(rowNumber, 13).Value = los.Days
                    rowNumber = (rowNumber + 1)
                    textRowNo = (textRowNo + 1)

                    LineFromFile = sr.ReadLine()
                    textHead = Mid(LineFromFile, 51, 11)
                    If textHead = "Reservation" Then
                        LineFromFile = sr.ReadLine()
                        LineFromFile = sr.ReadLine()
                        LineFromFile = sr.ReadLine()
                        LineFromFile = sr.ReadLine()
                        LineFromFile = sr.ReadLine()
                        textRowNo = (textRowNo + 5)
                    End If
                End If
                textRowNo = (textRowNo + 1)
            End While

I'm pleased to report, it works perfectly!

Thank you all for your help. Your responses got me on the right track. Most importantly, I've learned something else!

Don

score 0 · Answer 6 · 2015-01-09T03:45:22+00:00

For future reference, unless the text file is exceedingly large I find it much easier to read the entire file in at once as

Dim lines() As String =  System.IO.File.ReadAllLines(filename)

That way you know up front how many lines you are working with. You don't have to test for end of file. You can process the lines in any order you want and you can use Filter to extract specific lines.

VBA to VB.net "Open filePath For Input As #1"

Recommended Answers Collapse Answers

All 8 Replies

Recommended Answers