VB 6.0 - need some additional advice while using Instr function

hi Experts,

Kindly suggest,

I have designed a VBA code where I want to compare a string with FileName in a directory. In this case I have used Instr function, this helps me in 3 cases only, but not dynamicaly.

Explaination:

  if the str=4567 and filename can be:

1. xs1234567.pdf
2. 4567.pdf
3. 4567(1).pdf
4. updated 4567(2).pdf

The code I have created, helped me to find all the files, but this is not correct. It should exclude first file name ie:xs1234567.pdf


Used and designed Code:

Dirfname = finDir 
fName = Mid((Dirfname), 1, (InStr(1, (Dirfname), ".") - 1)) 
fileExt = Mid((Dirfname), (InStr(1, (Dirfname), ".") + 1), 3) 
 
 
**If (InStr(1, fName, wkvalue) > 0 And wkvalue <> "") Then ** what addition needs to be done in this line 
    If (Trim(UCase(fileExt)) = "PDF" Or Trim(UCase(fileExt)) = "TIF") Then 
   Cells(recnum, 2).Value = "Yes" 
   'col = col + 1 
   ws.Hyperlinks.Add Anchor:=Cells(recnum, (col + 1)), _ 
   Address:=SourceFolderName & "\" & Dirfname 
'  'col = col + 1 
   'Else: Cells(recnum, 2).Value = "No" 
        End If 
  End If 

PLease can you suggest what can be done to obtain this result.

thank you.

how about using the regexp functions

Dim r As New VBScript_RegExp_55.RegExp
    r.Pattern = Pattern
    r.IgnoreCase = True

    If r.Test(fName) Then


// loads of docs on using regular expression pattern matching, and a google will find one to do what you're trying to do i reckon

Pattern of "^4567.*" would work perhaps, but in any case you'll be able to make it as complex or simple as you like

Hi
You don't have much left to do as you have determined if the filename contains the value 4567(example) or not.
You merely need to determine if it is one of the following 4 cases:

1. 4567
2. 4567(#)
3. *<space>4567
4. *<space>4567(#)

In cases 1 & 2, the instr returns the number 1.
Cases 3 and 4 can be eliminated by determining if the instr function returned a value greater than 1 and the value of the preceding character was a space.    you can turn this into cases 1 or 2 by taking everything from this returned value to the end.

Once you have done that you are only left with cases 1 and 2.
You can determine if the resulting filename conforms to cases 1 or 2 by using the "Like" operator with a pattern.   

          If   filename like  "4567" & (*)" = true then....yep

Of course you can replace "4567" with a variable and you don't need to check that characters between the "(" and the ")" are numeric since it is a filename returned by windows and the syntax will be correct.

db

Thanks David Braithwaite for your quick reply.

Yes you are right, But I have accidentily missed out one more thing in it.That is, there are 2 different folders, one with 4-5 digital numbers and one have like this eg:(xs124567111) type of filename. And has same(explained above) criteria as a naming convention. so if i dont compair the name then xs124567111 will also be trapped in the above case and a hyperlink will be added into the file.so I want that I should dynamically identify the filename. in Both scienerio.
 
and using a variable it is stating me a syntax error.
 
if varname like "* 4567" then
do something......
else
do something......

If possible can you send me a short snippet for this, Please  

It will be highly appreciated.
thank you.

I am a little confused....

are you saying:

If in folder-A then 
  If formats are like:   4567,  4567(2), old 4567, old 4567(*) then
       create hyperlink to them
    endif

elseif in folder-B then
    if formats are like: xls1244456,  xls1234556(3), old xls1234445, old xls123445(2) then
      create hyperlink to them
    endif
else
    ignore this file
endif

?
db

thanks for your reply.

I have tried this too.But I am unaware of this function.Please can you guide me how to encode it in my task(Macro).

I tried similar to this code:

Dim r As New VBScript_RegExp_55.RegExp  ** But this part of code is showing error.
    r.Pattern = Pattern
    r.IgnoreCase = True

    If r.Test(fName) Then
do something



Kindly advice.
Thank you.

Yes you are Partially right.

I am compairing the filename in 2 different directories.

Explaination:

If in folder-A then 
  If formats are like:   4567,  4567(2), old 4567, old 4567(*) then
     create hyperlink in a file(Worksheet)
    endif
else
  Ignore the file.
end if

this whole thing carried in a loop (number of files present in the drive).If the file is not present the it will go in second location (ie: folder-B).
 
in this (folder-B) mainly filename are in this format(xls1244456,  xls1234556(3), old xls1234445, old xls123445(2)). but not confirmed.


so my point is that, if I use Instr to compair the filename then filename(4567) might get trapped in folder-B with (*)xs12234567(*) and vis-a- versa.

thats why using regular expressions as in my reply would be amore powerful/flexible solution than instr

 Yes pete I am trying this since morning.I m not finding the way out of this. Kindly help.


I am stuck in the code.

Dim r As New VBScript_RegExp_55.RegExp

r.Pattern = " " & fname & "|" & fname & " | " & fname & "  " * here

Kindly help.

what are you trying to do with that line of code?

the pattern i suggested was "^4567.*"

i take it you've worked out to add a ref to the regexp 55?

what is fname?

Fname is a variable. And provided I have added ref regexp_55.

in the pattern i m trying to insert variable name. 

so that it satisfies 4 cases discrided below:

1. xs12334567.pdf should not concidered this case
2.4567(1).pdf
3.updated 4567.pdf
4.4567 updated.pdf
 

add a fucntion in vba as below, then you can just use the following in the sheet

=RegExecute(A1,B1)

where A1 is the cell you are trying to search in and B1 contains the pattern, then you can experiment with the pattern...

did the pattern i suggested not work then?

you'd probably want something like "^"&b1&".*" where B1 would be a cell containing the base filename you are looking for

quite fun messing about with regular expressions :-) which you can google to see what i'm upto

Function RegExecute(Value As String, Pattern As String, Optional IgnoreCase As Boolean = False)
    Dim r As New VBScript_RegExp_55.RegExp
    r.Pattern = Pattern
    r.IgnoreCase = IgnoreCase
    If r.Test(Value) Then
    Dim allMatches As MatchCollection
    Set allMatches = r.Execute(Value)
    RegExecute = allMatches(0)
    Else
    RegExecute = ""
    End If
End Function

how about the pattern \b4567.*

ie finds the first 4567 straight after the first non word char

matches the example you gave properly

quick ref link http://www.regular-expressions.info/reference.html

Do you know ahead of time that:
   in Folder_A
        file names s like  ##### are desired
        file formats like  xls###### are to be ignored

   in Folder_B
       file names like xls###### are desired
      file names like  ##### are to be ignored
  
If so, how do you know this?   by the folder name ?  by the fact order in which you process these folders?
by something else in the folders? 

db
      

Yes, I do considered the time ahead, David.

hence it is not sure that what type of file name will be coming.  filename can also include alpha numeric char.
 
Task description:

There is an Input file (xls format) in which I am using array(array1) to store the file names, that are to be searched in the respective directories.Then as per static sheet in the macro file,the user inputs the directory  in the static sheet, say cell "A2" (folder-A) and "A3"(Folder-b) then an array(array2) stores them. After storing the directory path, the macro goes in a single directory and stores the present filename from the directory in an array (array3).then (array1) will be compaired in (array3) that means file name from input file with the present filename in the directory.

then while compairing the filenames, the filename(#####) should be not be trapped, when filename in any of the directry is "xs########" .


For this I have tried Instr. But this function doesnot cover all the criterias.
Hecnce Now I m searching the way out of this.

This is what i have manage to compile, by looking at the snippet you gave.

Public Function RegExecute(ByRef fname As String, ByRef wkvalue) As Boolean
    Dim r As New VBScript_RegExp_55.RegExp
    Dim Pattern As String
    r.Pattern = "(\b|/"")" & wkvalue & "(\b|/"")"
    'r.IgnoreCase = IgnoreCase
    If r.Test(fname) Then
      Dim allMatches As MatchCollection
      Set allMatches = r.Execute(fname)
      RegExecute = allMatches(0)
    Regexecute=True
    Else
     RegExecute = False
    End If
End Function

i would have left my function as i pasted and done any of the control of the pattern in a cell/cells in the worksheet itself

but in any case does this now work?

perhaps you could attach a sample sheet for us to play about with

Thanks A lot  for your help.

It is working fine if I find any of the Bug then surely i will  return with my query.

I would be able to attach the sample file tomorrow morning.

Thank you for ur help

Lets see if I have the idea correct.
the following is "pseudo code" not vba  (although it will be similar)

let me know if this is what you need to do and if you can code from this.


sub main
  For each file in array1
     target = filename_only (no path nor extension)   'of form xxxx, xxxxx, or xlsxxxxxxx
     For each directory on static sheet
         if file_is_in_directory (directory_name,target) is true
           form a hyperlink to this file
           exit from for-directory-loop and proceed to next file in array1 
         endif
     end directory-for-loop
   end file-for-loop
end sub-main


Function file_is_in_directory (directory_name,target) as boolean

  For each file in directory directory_name
    dir_filename = filename from directory with neither path nor extension
    'determine if possible match
      position_in_dir_filename = instr(dir_filename,target) 
      select case position_in_dir_filename
        case 0
            'do nothing since there is no possible match ---- continue the for-loop

        case 1  'target  starts at beginning of dir_filename
                   and could be  xxxxxx or xxxxxx(#) or xlsxxxxxx or xlsxxxxxx(#)
             
             If  file_names_match (target, dir_filename) then
              exit function with true
            else
              'continue with for-loop
            end if

        case >1  'target is "inside"  and could be   xxxxxxfilename (illegal), xxxxx filename (legal)
            
            if mid(directory_filename,position_in_directory_filename -1 ,1) <> space then
              'this is like  xxxxxxxfilename (ignore it)
              'continue with for-loop
              else
                matchable_name = mid(directory_filename,position_in_directory_filename)
                   'this will be like  xxxxxx,  xxxxxx(#),  xlsxxxxxxxxx,  xlsxxxxxxx(#)
                If file_names_match(target, matchable_name) then
                  exit function with true
               else
                'continue with for-loop
               end if
            endif
        end select
      end for-loop

      exit function with false
end function-file_is_in_directory


function file_names_match (target, dir_matchable) as boolean
       
 'target is filename of form xxxxx or xlsxxxxxxx
'dir_matchables is filename of form   xxxxx or xxxxx(#)  or xlsxxxxxxx or xlsxxxxxx(#)

    position_of_right_paren = instr(dir_matchable,"(")
    If position_of_right_paren > 0 then
    dir_matchable = left(dir_matchable,position_of_right_paren-1)
    endif
  
  if lcase(target)=lcase(dir_matchable) then
      exit function with true
    else
       exit function with false
    endif
end function file_names_match    

sample macro.zip


Please find the files and

sample macro.zip


Please find the files and if

sample macro.zip


Please find the files and if possible t

sample macro.zip


Please find the files and if possible then

sample macro.zip


Please find the files and if possible then please

sample macro.zip


Please find the files and if possible then please point out the

sample macro.zip


Please find the files and if possible then please point out the loop

sample macro.zip


Please find the files and if possible then please point out

sample macro.zip


Please find the files and if possible then please point

sample macro.zip


Please find the files and if possible then please point out the loop holes

sample macro.zip


Please find the files and if possible then please point out the loop holes in

sample macro.zip


Please find the files and if possible then please point out the loop holes in the

sample macro.zip


PLease find the file, and please let me know if there is any week point in it.So that i can Improve the code. from my end


thanks alot for your help on this.

sample macro.zip


PLease find the file, and please let me know if there is any week point in it.So that i can Improve the code. from my end


thanks alot for your help on this.

sample macro.zip


PLease find the file, and please let me know if there is any week point in it.So that i can Improve the code. from my end


thanks alot for your help on this.

sample macro.zip


PLease find the file, and please let me know if there is any week point in it.So that i can Improve the code. from my end


thanks alot for your help on this.