Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Jul 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need help writing a simple macro

    Im trying to write a simple VBA, that opens 7 different .txt files and searches for a few differnet lines. Im not exactly sure how I should go about doing this.

    I've been succesful at writing a program that opens one file and searches for each line and the puts it in an excel spreadsheet. But I would like to automate this to search 7 through seven files. Any Ideas?

    Also, can someone explain to me what the following code means?

    Open fname For Input Access Read As #1

    i = 1
    j = 1

    While Not EOF(1)
    Line Input #1, entireline
    Wend

    What does the the #1 mean? also what is the significance of the 1 in EOF(1)?

    Any help is greatly apprecaited.

    Thank you!

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Sporty,

    Welcome to the lounge as a new poster.

    Open fname For Input Access Read As #1 {fname is replaced by the drive:/path/name of the file you want to open for Reading and it is assigned a file number of 1}

    i = 1 {assigns the value of 1 to the variable i}
    j = 1

    While Not EOF(1) {process the following lines of code while the End Of File is not found}
    Line Input #1, entireline {read the next line of text from file #1 into the variable entireline}
    Wend {end of the statements to be processed while EOF is not found}

    Some basics: You should always Declare your variable names and the type of data they hold. In the code above that would be:
    Dim i as Integer
    Dim j as Integer {both of these could also be declared as Long if they would hold numbers greater than 65535}
    Dim entireline as String

    Here's some Air Code {untested} to do what you want. Note ' denotes comments, i.e. non-processed info.

    Code:
    Sub ReadFilesCompare()
    
       Dim zFileList(7) as String
       Dim iFileCnt     as Integer
       Dim iCntr        as Integer
       Dim zCurLine     as String
    
    'Note: By default arrays are zero based!
    
       zFileList(0) = "Filename1"
       zfileList(1) = "Filename2"
       zFileList(2) = "Filename3"
       zfileList(3) = "Filename4"
       zFileList(4) = "Filename5"
       zfileList(5) = "Filename6"
       zFileList(6) = "Filename7"
    
       For iCntr = 0 to UBound(zFileList)-1
    
          Open zFileList(iCntr) For Input Access Read As #1
    
          While Not EOF(1)
               Line Input #1, zCurLine
               'Your comparison processing goes here
          Wend
    
          Close #1   'Close the File
    
       Next iCntr
    
       'Final processing goes here
    
    End Sub    'ReadFilesCompare


    Note: Code above assumes that the files are in the current directory. You need to include the drive/path info if they are not or add it to the Open command, e.g.
    Open "C:\Documents\" & zFileList(iCntr) ...
    Last edited by RetiredGeek; 2012-07-24 at 11:09.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Jul 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    RetiredGeek, Thank you so much for that information.

    Let's say I have something like this

    zFileList(0) = Application.GetOpenFilename

    How can I find the full directory of the file I actually opened? And maybe assign it to a variable.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Sporty,

    Like this:
    Code:
    Sub FileOpenPathTest()
    
       Dim zFileList(3) As String
       Dim iLoc         As Integer
       Dim zDrPath      As String
       
       zFileList(0) = Application.GetOpenFilename
       
       iLoc = InStrRev(zFileList(0), "\")
       zDrPath = Left(zFileList(0), iLoc)
       
       Debug.Print zFileList(0)
       Debug.Print iLoc
       Debug.Print zDrPath
       
    End Sub
    Results in Immediate Window are:
    G:\BEKDocs\Excel\Test\VBA - Copy Sheet to Other Workbook.xlsm
    22
    G:\BEKDocs\Excel\Test\
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Quote Originally Posted by RetiredGeek View Post
    Dim j as Integer {both of these could also be declared as Long if they would hold numbers greater than 65535}
    greater than 32,767 actually.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Rory,

    Thanks I must have had another brain fart!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    New Lounger
    Join Date
    Jul 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the help guys. And yes retiredgeek, your suggestion helped. Thank you.

    How would I go about searching that path now for specific characters?
    So lets say i wanted the program to print every word it finds that begins with Se and is 5 characters long?

    If Left(zDrPath, 5) Like "Se*" Then
    ........

    Am I on the right track? The Se i word can appear anywhere in the path(towards the left, right, center).

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Sporty,

    The only way to do that, that I know of, is using Regular Expressions. This is something that I am not very good at. With that said here is some code that does work.
    Code:
    Sub TestRegEx()
    
        Const strTest As String = "C:\Test\Seete\Hello"
        MsgBox "The pattern \Se***\ was " & IIf(bSearchUsingRegEx(strTest), "", "NOT") & _
               " found in the string: " & strTest, _
               vbOKOnly + vbInformation, "Regular Expression Search Results"
        
    End Sub
    
    
    Function bSearchUsingRegEx(zPath As String) As Boolean
    
       Dim oRegEx   As Object
       Dim oMatches As Object
       
       Set oRegEx = CreateObject("vbscript.regexp")
       
       With oRegEx
           .MultiLine = False
           .Global = False
           .IgnoreCase = True
           .Pattern = "\\Se\D{3}\\"
       End With  'oRegEx
       
       Set oMatches = oRegEx.Execute(zPath)
       bSearchUsingRegEx = IIf(oMatches.Count = 0, False, True)
       
    End Function
    Assumptions: The "Se" element is an entire section of the path, e.g. "\Seeac\" or "\Seabc\"
    Maybe some one else can provide a better .Pattern if this isn't what you want.

    Testing Results:

    Segment more that 5 chars:
    RegexTooLong.PNG
    Segment less than 5 chars:
    RegexTooShort.PNG
    Segment 5 chars but one is a number:
    RegexContainsNo.PNG
    Segment 5 chars...Just Right
    RegexJustRight.PNG

    Almost forgot, here are some references for Regular Expressions (RegEx)
    Regular Expression Cheat Sheet
    Regular Expressions Reference
    Regular Expressions and vbScript
    Last edited by RetiredGeek; 2012-08-01 at 19:22.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You can use Like:
    Code:
    If zDrPath Like "Se???" Then

    or use:
    Code:
    If Left(zDrPath, 2) = "Se" and len(zDrPath) = 5 Then
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Rory,

    I agree that would be the easy way if it fits the requirements, which are at best vague.

    How would I go about searching that path now for specific characters?
    So lets say i wanted the program to print every word it finds that begins with Se and is 5 characters long?
    The way I read it the SE word could be anywhere in the path and should only include characters not numbers. This is why I resorted to the regular expression. Of course in my pattern I made the assumption, as noted, that it is a whole path segment/folder/directory by including the back slashes on either end of the pattern.

    Perhaps the OP could enlighten us on the actual requirements with some examples?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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