InStr function VBA: trouble comparing file name with text object

Question

General Background:

I am trying to iterate through lines of text from Word document, where each line is a unique sample name (e.g. Sample1, Sample2, Sample3) and then insert images from a specific folder which contain the sample name in their file name (e.g. Sample1 a.png, Sample1 b.png, Sample2 xyz.png, Sample3 xxx.png).

Example of Code I'm Using:

Dim fso As New FileSystemObject
Set MySource = fso.GetFolder("C:\Test\")
numParas = ActiveDocument.Paragraphs.Count

counter = 0
While counter < numParas
    counter = counter + 1
    sampleName = ActiveDocument.Paragraphs(counter).Range.Text
    For Each File In MySource.Files
        If InStr(File.Name, sampleName) > 0 Then 
            ' Insert script here to insert pictures
            MsgBox ("File found")
        End If
    Next File
Wend

Questions /Problems:

I can't get InStr to work when I comapre File.Name with sampleName (a Range.Text object).
However, it does work if I use If InStr(File.Name, "Sample1") > 0 Then instead of If InStr(File.Name, sampleName) > 0 Then; though this does not allow me to iterate through many sample names.

Other option for adding files from a folder:

This thread (Loop through files in a folder using VBA?) shows quicker ways of looping through files in a folder. I tried using Dir with the following code:

Dim StrFile As String
Dim FolderStr As String

numParas = ActiveDocument.Paragraphs.Count
counter = 0
While counter < numParas
    counter = counter + 1
    sampleName = ActiveDocument.Paragraphs(counter).Range.Text
    FolderStr = "C:\Test\*" + sampleName + "*.png"
    MsgBox (FolderStr) 'First message box
    StrFile = Dir("C:\Test\*" + sampleName + "*.png")
    Do While Len(StrFile) > 0
        ' Insert script here to insert pictures
        MsgBox ("File found")
        StrFile = Dir
    Loop
Wend

Questions/problems:

I have trouble creating Dir object iteratively as I can't concatenate the sampleName text object with strings.

For example, in the first message box in the script above I get the following output, with the file address on two separate lines instead of one string.

C:\Test\*Sample1
*.png

Thanks for any help you can provide!

score 2 · Accepted Answer · answered Mar 19 '15 at 07:05

When you compare two items File.Name and sampleName using inStr you forget that Samplename is a text from paragraph which includes paragraph ending mark. You need to cut the last characters in any way, like:

sampleName = ActiveDocument.Paragraphs(counter).Range.Text
sampleName = Left(sampleName, Len(sampleName) - 1)   'this new line removes paragraph ending mark

InStr function VBA: trouble comparing file name with text object

1 Answers1