Vb Code Freezing At Line Input Command

  • I have created an AddIn for a project I'm working on using the 'Open' command in Excel Vb. While this worked perfectly for the file I was testing it on (1,740,754 bytes) it doesn't work on a new file (121,445,125 bytes).
    On testing the code, one line at a time, it gets stuck on

    Code
    Line Input #1, DataLine


    The files only contain one line so I suspect there is too much data for the command to handle.


    Is there an alternative?

  • Re: Vb Code Freezing At Line Input Command


    Welcome to Ozgrid. Please take care where yopost your questions, you will get quicker reponses & keep the Forum tidy (not to mention less work for Admin) if you do.


    You have posted in a Forum clearly labeled "Email/Excel Help Only for questions that pertain to Excel and any email package"


    I will move it this time.

  • Re: Vb Code Freezing At Line Input Command


    I'm sorry about that royUK, Thank you.


    Can we bear in mind though that it is being created in Excel and I don't know any other versions of VB. I will get very confused if someone starts offering NET. VBA specific advise.


    Thanks again.

  • Re: Vb Code Freezing At Line Input Command


    Is that right you are trying to read a +120mb file into a variable?


    Can you explain exactly what you are trying to do maybe we can suggest an alternative approach.

  • Re: Vb Code Freezing At Line Input Command


    Sure Andy I'll try.


    The files i'm manipulating are xml files. The program that initially creates them is supposed to store 15 minutes worth of data on them for reading into a different application.


    Unfortunatly, something is going wrong and the xml files are sometimes being created in excess of 1 days worth of data.


    The AddIn searches through the xml file creates multiple files containing just the 15 minutes.


    I use the variable and Instr command to search for opening / closing tags and the timestamps within them.


    The seperate files that the AddIn creates uses these timestamps to decide which file to write the data to.


    I hope this helps.

  • Re: Vb Code Freezing At Line Input Command


    Thanks Andy.


    I have looked into the Get command but can't really work out how it will be useful. That said, I can't even read data from the file on the tests I've run using Get.


    The xml files that I'm trying to read do have some similarities in that each record is seperated by opening and closing tags but they vary in length depending on what has been recorded by the original application.


    The xml file always (so far) has four sections. I use the following code to seperate the original variable (DataLine) into these four sections for manipulation.



    The Next loop is used because there are two lines of data in the xml file, the first being an xml info tag which I then add to each file that is created.


    I hope this all makes sense. :)

  • Re: Vb Code Freezing At Line Input Command


    Can you put together a sample xlm file? Just need the tags that are used to delimit the sections. Anything in between can be junk but make sure it is well formatted in xml terms.

  • Re: Vb Code Freezing At Line Input Command


    The way I would do this would be to implement a buffer and read chunks of the file into the buffer using the get command and work from the buffer.


    I would also try and parse the line using a loop rather than straight line code, reading from the front of the buffer and getting another block of data using the get statement when the length of the buffer falls below a certain limit.


    Using the get command allows you to control how much data you read at one time. The upper limit for a single string variable is a lot lower that 120mb!


    Regards


    Rich

  • Re: Vb Code Freezing At Line Input Command


    Thanks all.


    Rich,
    I am experimenting with the Get command and might be getting somewhere


    Am I right to open the file in Random in this case?


    Vb-kid

  • Re: Vb Code Freezing At Line Input Command


    Try this bit of code. It uses the Input method to read chunks of the file.


    I'm not sure there is a good way to process your file as each section has a unique name. Assume you want to extract the text between tags
    <vxml_firsttable> and </vxml_firsttable> etc.


    [vba]Sub x()


    Dim strBuffer As String
    Dim strChunk As String
    Dim strData As String
    Dim intUnit As Integer
    Dim lngStartPos As Long
    Dim lngEndPos As Long
    Dim lngChunkSize As Long
    Const START_TAG = "<vxml_secondtable>"
    Const END_TAG = "</vxml_secondtable_Record>"

    lngChunkSize = 32768
    intUnit = FreeFile
    Open "C:\temp\test.xml" For Binary Access Read As intUnit

    Do While Not EOF(intUnit)
    ' get a chunk of text
    strChunk = Input(lngChunkSize, #intUnit)
    strBuffer = strBuffer & strChunk
    If lngStartPos > 0 Then
    lngEndPos = InStr(strBuffer, END_TAG)
    If lngEndPos > 0 Then
    strData = Mid(strBuffer, lngStartPos, lngEndPos + Len(END_TAG) - lngStartPos)

    ' do something with strData
    Debug.Print strData

    strBuffer = Mid(strBuffer, lngEndPos + Len(END_TAG) + 1)
    lngStartPos = 0
    End If
    Else
    lngStartPos = InStr(strBuffer, START_TAG)
    End If
    Loop

    Close intUnit

    Do While Len(strBuffer) > 0
    If lngStartPos > 0 Then
    lngEndPos = InStr(strBuffer, END_TAG)
    If lngEndPos > 0 Then
    strData = Mid(strBuffer, lngStartPos, lngEndPos + Len(END_TAG) - lngStartPos)

    ' do something with strData
    Debug.Print strData

    strBuffer = Mid(strBuffer, lngEndPos + Len(END_TAG) + 1)
    lngStartPos = 0
    End If
    Else
    lngStartPos = InStr(strBuffer, START_TAG)
    If lngStartPos = 0 Then strBuffer = "" ' no starting tag in remaining buffer
    End If
    Loop

    End Sub[/vba]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!