Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Partner - Creator III
Partner - Creator III

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

View solution in original post

11 Replies
martynlloyd
Partner - Creator III
Partner - Creator III

Try using the DIR function instead.

Regards.

Not applicable
Author

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

martynlloyd
Partner - Creator III
Partner - Creator III

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
Author

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

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

martynlloyd
Partner - Creator III
Partner - Creator III

Strange,

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

ML.

Not applicable
Author

OK, Thank you!!

martynlloyd
Partner - Creator III
Partner - Creator III

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
Partner - Creator III
Partner - Creator III

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
Author

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