Qlik Community

New to Qlik Community

Discussion board for questions on how to use Qlik Community and its features.

Not applicable

Open all wokbooks from folder with macro

Hello everybody! I have this macro in VB, but I want open all workbooks that are in this folder, but I dont know how will be the names of all workbooks. I tried this but it doesn't work..

FUNCTION ConvertirFormatoArchivos()

  Dim  file, origen
  Set objExcel = CreateObject("Excel.Application")

  set file = CreateObject("Scripting.FileSystemObject")

         CurrentDirectory = path.GetAbsolutePathName(".")

         origen = CurrentDirectory & "\Bajadas\"  

         file  = origen & "*.xlsb"

          While (file <> "")

          

               objExcel.Workbooks.Open(file)

               file = origen & "*.xlsb" 'Tomo todos los archivos binarios de la carpeta \Bajadas

         Wend 

END FUNCTION

Thank you!!

1 Solution

Accepted Solutions
martynlloyd
Contributor III

Re: Open all wokbooks from folder with macro

This works in the edit module

sub Loopfiles

  Dim  file, origen
  Set objExcel = CreateObject("Excel.Application")

  set objFileSys = CreateObject("Scripting.FileSystemObject")

         origen = "mypath\"
     set  filelist  = objFileSys.GetFolder(origen)
   
     for each file in filelist.files
  msgbox(file)
  next

end sub

11 Replies
martynlloyd
Contributor III

Re: Open all wokbooks from folder with macro

Try using the DIR function instead.

Regards.

Not applicable

Re: Open all wokbooks from folder with macro

I had tried before with Dir() but it show this error:  "Type mismatch: 'Dir' "

martynlloyd
Contributor III

Re: Open all wokbooks from folder with macro

Here is a little example, replace 'mypath' with your own folder...

Sub LoopThroughFiles()

    Dim StrFile As String

    StrFile = Dir("mypath\*.docx")

    Do While Len(StrFile) > 0

        Debug.Print StrFile

        StrFile = Dir

    Loop

End Sub

Not applicable

Re: Open all wokbooks from folder with macro

Show the same error:  "Type mismatch: 'DIR' ".

I done this macro In Tools -> Edit module, not in the load script.

martynlloyd
Contributor III

Re: Open all wokbooks from folder with macro

Strange,

I tested it using Excel VBA, I'll try it in a QV module and let you know.

ML.

Not applicable

Re: Open all wokbooks from folder with macro

OK, Thank you!!

martynlloyd
Contributor III

Re: Open all wokbooks from folder with macro

Seems that the edit module does not support the required Type. I can't find any way to "dim as".

If it is any consolation, the follow works in the script:?

Sub LoopThroughFiles

Let FileMask= mypath & '\*.csv';

FOR Each File in filelist (FileMask)
 
TRACE(File);
 
Next  

End Sub

Call LoopThroughFiles

Marty.

martynlloyd
Contributor III

Re: Open all wokbooks from folder with macro

This works in the edit module

sub Loopfiles

  Dim  file, origen
  Set objExcel = CreateObject("Excel.Application")

  set objFileSys = CreateObject("Scripting.FileSystemObject")

         origen = "mypath\"
     set  filelist  = objFileSys.GetFolder(origen)
   
     for each file in filelist.files
  msgbox(file)
  next

end sub

Not applicable

Re: Open all wokbooks from folder with macro

sub fileopen()

mypath = "Your Path"

    fname = Dir(mypath & "*.xlsb")

    Do While Len(fname) > 0

    Workbooks.Open Filename:=mypath & fname

--your other coding what you want with file-----

    fname = Dir

    Loop

End Sub

Community Browser