search for filenames in a directory within a macro

[bumin]

Hi together,

can anyone help with this issue?

I have created a macro to find the names of the  files (regarding a pattern) in a directory

for each file I find I want add the name to my result

if I find the files alpha, beta  and charly I will have as result "Dir\alpha;beta;charly"

but the Qlikview don't accept the the command " FOR EACH File in FileList(sDir&sSearchPattern)" saying type mismatch

this is my macro

-------------------------

sub vInvoice

    sDir = "D:\Data\Kunden\ProServ\WW\Project\Qlikview\DMS\"

    sFile = sDir

    sSearchPattern = "0101REGM-HV000000001886*.jpg"

    msgbox (sDir)

    msgbox (sSearchPattern)

    FOR EACH File in FileList(sDir&sSearchPattern)

        msgbox(File)

         sFile = sFile & ";" & File

     NEXT

end sub

[michael_anthony · Accepted solution]

Macro's use VBScript language, not the Qlikview Load Script language so the FOR EACH file .. function won't work as it only works withing QV Load Script.

Equivalent function for VBScript would use the filescriptingobject to search a directory.  You also lose some functionality in the searchpattern in this area as doesn't have a nice wildcard search by default.  If you google vbscript plenty of examples of how to use, an example would be:

sub test1
     sdir = "D:\......\
     sFile = sDir
     srchLeft = "0101"
     srchRight = ".jpg"

     set fso = CreateObject("Scripting.FileSystemObject")    
      set fldr = fso.GetFolder(sDir)
      set files = fldr.Files

     for each file in files
          tFile = file.Name
           if left(tFile,len(srchLeft))= srchLeft AND right(tfile,len(srchRight)) = srchRight then

                sFile = sFile & ";" & tFile
           end if
     next

      msgbox(sFile)

End Sub

[bumin]

thanks Micheal,

this is exactly what I want to have

works perfectly

kind regards

Bumin