Do While file is Workbook ( open only XL files in folder )

[Akbarov]

Hello community,

I am trying to use following VBA , to open each file in folder ( but in folder there are different kind of files too like .pptx etc. ) I want to use it to open only Excel Workbooks

Do While MyFile <> "" And MyFile Is Workbook
   'Opens the file and assigns to the wbk variable for future use
   Set wbk = Workbooks.Open(Filename:=MyFolder & MyFile)
   wbk.Activate

Can anyone tell me what I do wrong?

 

[Joe4]

You cannot use "Workbook" like that. It is a data type, used when assigning variable data types.
Instead, loop at the file extensions, as shown here: VBA Loop Through All XLS Files in a Folder or Directory | VBAF1

Note that you can use "*.xls*" instead of "*.xls" to include all types like "xls", "xlsx", "xlsm", and "xlsb".

 

[Akbarov]

What I do wrong? it stops when faces different kind of file.
Instead I want it to loop next file

Do While MyFile <> ""
If Right(MyFile, 3) = "xls" Or Right(MyFile, 4) = "xlsm" Then
   Set wbk = Workbooks.Open(Filename:=MyFolder & MyFile)
Else
End If