Slight Change To Code

[Dazzawm]

I have this line of code that only makes changes to .xlsx files. How do I change it so it does .xlsm files also please?

strF = Dir(strP & "\*.xlsx")

 

[saboh12617]

Hello,
What about

strF = Dir(strP & "\*.xls?")

 

[Dazzawm]

Hello,
What about

strF = Dir(strP & "\*.xls?")

That seemed to work, much obliged squire. Simple when you know how!

 

[saboh12617]

No problem, glad to help.
For information, it will also include the XLSB files since "?" represents "any single character".

 

[Dazzawm]

For some reason it didn't work on all codes I have this in, may be other parts of code affecting it. Could something like below be done?

strF = Dir(strP & "\*.xlsx", "\*.xlsm")

All codes work ok when I change between .xlsm and .xlsx but not when there are a mixture of both in a folder.

 

[saboh12617]

I do not think it is possible unfortunately. cf. Dir function (Visual Basic for Applications) | Microsoft Learn

I would do the check afterwards, but with all the code it would be easier to guide you because you probably need refactoring on your loop.
Something like

Sub Example()
  Dim strP As String, strF As String
  strP = "C:\French\Recipes\Secrets\FrogSoup\Ingredients\"
  strF = Dir(strP)
  Do While strF <> VbNullString
    If LCase$(strF) Like "*.xls[xm]" Then
      ' do your things
    End If
    strF = Dir()
  Loop
End Sub

 

[Dazzawm]

I do not think it is possible unfortunately. cf. Dir function (Visual Basic for Applications) | Microsoft Learn

I would do the check afterwards, but with all the code it would be easier to guide you because you probably need refactoring on your loop.
Something like

Sub Example()
  Dim strP As String, strF As String
  strP = "C:\French\Recipes\Secrets\FrogSoup\Ingredients\"
  strF = Dir(strP)
  Do While strF <> VbNullString
    If LCase$(strF) Like "*.xls[xm]" Then
      ' do your things
    End If
    strF = Dir()
  Loop
End Sub

Thanks. Some are very complex codes so I will just change to suit each time.