topic Re: search for filenames in a directory within a macro in QlikView

search for filenames in a directory within a macro

bumin — Tue, 01 Jan 2013 21:25:37 GMT

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

end sub

Re: search for filenames in a directory within a macro

michael_anthony — Wed, 02 Jan 2013 04:05:29 GMT

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

Re: search for filenames in a directory within a macro

bumin — Wed, 02 Jan 2013 08:55:07 GMT

thanks Micheal,

this is exactly what I want to have

works perfectly

kind regards

Bumin