Results 1 to 15 of 15

Thread: VBA Question..

  1. #1
    Join Date
    Feb 2009
    Posts
    35

    Unanswered: VBA Question..

    Hi

    I was wondering if somebody can help or at least point me in the right direction..
    I have a txt input file and am trying to export specific data from this file into an output txt file.

    The input file looks something like this :

    CPNTCS
    LIN01
    LIN02
    LIN03
    CPNTCS
    LIN01
    LIN02
    CPNPTC
    LIN01
    CPNLPL
    LIN01
    LIN02

    For the output, I need the file to output ONLY the "CPNTCS" with their corresponding lines (to skip all other records.)

    I have attached the sample code I am using - this basically outputs the CPNTCS lines but no other info. See below :

    Option Compare Database
    Option Explicit

    Function OutputTCS(InFile As String, OutFile As String)

    Dim intInHandle As Integer
    Dim intOutHandle As Integer
    Dim strInLine As String
    Dim strOutLine As String

    intInHandle = FreeFile
    Open InFile For Input As #intInHandle
    intOutHandle = FreeFile
    Open OutFile For Output As #intOutHandle

    Do Until EOF(intInHandle)
    Line Input #intInHandle, strInLine
    If Left(strInLine, 6) = "CPNTCS" Then
    strOutLine = strInLine
    End If
    Print #intOutHandle, strOutLine
    Loop

    End Function

    How can this be done? or what statement would be required.

    Thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I suppose that in your example 'CPNTCS', 'CPNPTC', 'CPNLPL' are identifiers (I'll call them tags) and that when the tag is 'CPNTCS' you want to output is as well as all the following lines, until another tag is read from the input file.

    You need first to identify all possible tags, then you can use a function like this:
    Code:
    Function OutputTCS(InFile As String, OutFile As String)
    
        Dim intInHandle As Integer
        Dim intOutHandle As Integer
        Dim strInLine As String
        Dim strTag As String
        Dim strOutLine As String
        Dim booExport As Boolean
        
        
        intInHandle = FreeFile
        Open InFile For Input As #intInHandle
        intOutHandle = FreeFile
        Open OutFile For Output As #intOutHandle
        
        Do Until EOF(intInHandle)
            Line Input #intInHandle, strInLine
            
            ' Prepare the input line for the Eval() function.
            '
            strTag = "'" & Trim(strInLine) & "'"
            
            ' Parse the line to identify it as a tag or not.
            '
            If Eval(strTag & " IN ( 'CPNTCS', 'CPNPTC', 'CPNLPL' )") = True Then
            
            ' Last input line is a tag.
            '
                If strInLine = "CPNTCS" Then
                
                ' The tag identifies a record to export
                ' --> must export until next tag.
                '
                    booExport = True
                Else
                
                ' Not the tag that identifies a record to export
                ' --> do not export until next tag.
                '
                    booExport = False
                End If
            End If
            If booExport = True Then
                strOutLine = strInLine
                Print #intOutHandle, strOutLine
            End If
        Loop
        Close #intInHandle
        Close #intOutHandle
    
    End Function
    Have a nice day!

  3. #3
    Join Date
    Oct 2009
    Posts
    340
    I'm confused by the approaches posted here. If your post is showing a column of data:
    CPNTCS
    LIN01
    LIN02
    etc.

    and this column is imported into a table - - - then a very simple query can collect just the CPNTCS lines from that table - and then you export this query's record set.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As far as I can understand, the posted sample data do not represent rows in a column of some table but lines in a text file.

    CPNTCS, CPNPTC, etc identify a "record" in the file while LIN01, LIN02, etc are the data for a record (each line probably being the contents of a column in a database-table approach).
    Have a nice day!

  5. #5
    Join Date
    Feb 2009
    Posts
    35
    Yes correct Sinndho, the purpose is so that JUST all the 'CPNTCS' and their lines, LIN01, 02 etc are only output to an output file. The records are transferred directly to an output file which I am using as the inputfile. All other records 'CPNPTC' and their lines are ignored. I am quite new to VBA and self teaching myself via websites etc in my spare time so please accept my unfamiliarity with this subject.

  6. #6
    Join Date
    Feb 2009
    Posts
    35
    Hi Sinndho, now that this is working, is there a way of being able to use this if the line "CPNTCS" was followed with more data on the line e.g. CPNTCS00034526345t6363, but each CPNTCS had different info on it for each line. In a way I require a way of being able to input a "If Left(strinline, 6) = CPNTCS" so that it finds if there is a CPNTCS and then outputs the whole line (followed by the corresponding LIN of course!) Thanks

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If all the tags have the same length, you can try something like:
    Code:
    strTag = "'" & Left(Trim(strInLine), 6) & "'"
    Have a nice day!

  8. #8
    Join Date
    Feb 2009
    Posts
    35
    Hi, yes I have tried adding this line but the output is returning nothing.

    Although I am still including the Eval() statement. Which I assume is just looking for "CPNTCS"..etc. Does this need to be changed? Possible use a Like() statement?

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    My mistake: I forgot to tell you to also change another line of code, sorry!

    Here it is:
    Code:
            strTag = "'" & Left(Trim(strInLine), 6) & "'"
            
            ' Parse the line to identify it as a tag or not.
            '
            If Eval(strTag & " IN ( 'CPNTCS', 'CPNPTC', 'CPNLPL' )") = True Then
            
            ' Last input line is a tag.
            '
                If strTag = "'CPNTCS'" Then
                
                ' The tag identifies a record to export
                ' --> must export until next tag.
                '
                    booExport = True
                Else
                
                ' Not the tag that identifies a record to export
                ' --> do not export until next tag.
                '
                    booExport = False
                End If
            End If
    The other lines of the function do not change from what I previously posted.
    Have a nice day!

  10. #10
    Join Date
    Feb 2009
    Posts
    35
    Thanks, I finally got it to work, could you just explain the change which you made?
    The "'CPNTCS'" why not just "CPNTCS"?

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    St3ven,

    There are several things that need to be explained.

    1. Eval()

    Eval() is a function that evaluates an expression (in the VBA meaning of the word) and that returns its result. For instance: Debug.Print Eval("2 + 2") yields 4 to be printed in the immediate window. Here I use it because I want to use the IN (...) operator which is available in SQL but not in VBA.

    In SQL, I must enclose a string element between single quotes ('), that's why we have the line:
    Code:
    	strTag = "'" & Trim(strInLine) & "'"
    that just encloses the read line between single quotes, the Trim() function being there to eliminate any space character that could be at the begining and/or at the end of the line (this kind of things happen easily in text files).

    So:
    Code:
        strTag = "'" & Trim(strInLine) & "'"
        x = Eval(strTag & " IN ( 'CPNTCS', 'CPNPTC', 'CPNLPL' )")
    is the same as
    Code:
        strTag = Trim(strInLine) 
        x = (strTag = "CPNTCS") OR (strTag = "CPNPTC") OR (strTag = "CPNLPL")
    which would work directly in VBA (without the Eval function).

    As I'm 1) lazy, 2) used to the SQL syntax, I prefer the first solution which is shorter. You could also use a SELECT CASE that would also work in VBA:
    Code:
        SELECT CASE Trim(strLine)
            CASE "CPNTCS", "CPNPTC", "CPNLPL"
                x = True
            CASE ELSE
                x = False
        END SELECT
    2. Left(), strLine and strTag

    Since whe want to keep the first six characters only to identify the tags, we use the Left() function:
    Code:
        strTag = "'" & Left(Trim(strInLine), 6) & "'"
    We also need to only use these six caracters to compare the current tag (the one at the beginning of the last read line) if any with the tag ('CPNTCS') that identifies a "record" we want to write to the output file and not use the whole line in the comparison. This is why I should have changed:
    Code:
        If strInLine = "CPNTCS" Then
    to:
    Code:
        If strTag = "'CPNTCS'" Then
    You could also use:
    Code:
        If Left(Trim(strInLine), 6) = "CPNTCS" Then
    I hope this helps.
    Have a nice day!

  12. #12
    Join Date
    Feb 2009
    Posts
    35
    Yes that has cleared up some of the answers which I have been looking for. Thank you very much for your time and help Sinndho..

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  14. #14
    Join Date
    Feb 2009
    Posts
    35
    One last thing, when you include :

    x = Eval(strTag & " IN ( 'CPNTCS', 'CPNPTC', 'CPNLPL' )")

    is the x stated as a new variable?

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I used a variable x in my explanations because I wanted to write the three possible ways of making the comparison (IN(...), OR...OR...OR and SELECT CASE...) in parrallel.

    The three methods of comparison could not be written in symmetry without using an intermediate variable because of the SELECT CASE method. Due to its syntax, using this method without the x variable yields a slightly different code structure.

    In the "real" code we just compare the result of the evaluation to True:
    Code:
        If Eval(strTag & " IN ( 'CPNTCS', 'CPNPTC', 'CPNLPL' )") = True Then
    could be written:
    Code:
        Dim x As Boolean
        x = Eval(strTag & " IN ( 'CPNTCS', 'CPNPTC', 'CPNLPL' )")
        If x = True Then
    Once again, I preferred the short solution.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •