VBA file extension wildcart problem

[kabanero]

Hello,
I have a number of *.xls files and one Master.xlsm file in C:\Rowfile.
The *.xls files should be moved to C:\Donefiles. The Master.xlsm should stay.
VBA moves all the files despite the fact I set *.xls for files to be moved.

Thank you for any help.
Here is the code:

Sub Move_xlsFiles_to_Done()

Dim FSO As Object
Dim fromDir As String
Dim toDir As String
Dim fExt As String
Dim fNames As String

fromDir = "C:\Rawfiles\"
toDir = "C:\Donefiles\"
fExt = "*.xls"
fNames = Dir(fromDir & fExt)

Set FSO = CreateObject("Scripting.FileSystemObject")
FSO.MoveFile Source:=fromDir & fExt, Destination:=toDir

End Sub

 

[John_w]

I don't know why *.xls also matches .xlsm (and .xlsx) with FSO, although FSO MoveFile does say "The source argument string can contain wildcard characters in the last path component only."

This uses native VBA functions instead of FSO:

Public Sub Move_xlsFiles_to_Done2()

    Dim fromDir As String
    Dim toDir As String
    Dim fExt As String
    Dim file As String
   
    fromDir = "C:\Rawfiles\"
    toDir = "C:\Donefiles\"
    fExt = "*.xls"
   
    file = Dir(fromDir & fExt)
    While file <> vbNullString
        Name fromDir & file As toDir & file
        file = Dir
    Wend

End Sub

 

[kabanero]

Thank you. I will try it tomorrow. The weird thing is that I copied the code from one of VBA youtube channels. It worked there but did not with me. A glitch may be.
Thanks again

 

[kabanero]

Hi John_w
Unfortunately, it did not work either. I tried to run the code on Excel 2019 and 2007. The result is the same. It moves *.xls files, and *.xlsm files as well.
For a reason it does not see the difference.
The code works if I save the file as *.xlsx and set fExt="*.xlsx".
Thank you again.

 

[Fluff]

I suspect that Dir is so old it only reads 3 character file extensions & so xls, xlsx & xlsm are all seen as xls.

Try

Sub Move_xlsFiles_to_Done()
   Dim FSO As Object
   Dim FsoFolder As Object
   Dim FsoFile As Object
   Dim fromDir As String
   Dim toDir As String
   
   fromDir = "C:\Rawfiles\"
   toDir = "C:\Donefiles\"
   
   Set FSO = CreateObject("scripting.FileSystemObject")
   Set FsoFolder = FSO.GetFolder(fromDir)
   For Each FsoFile In FsoFolder.Files
      If Right(FsoFile, 3) = "xls" Then
         FSO.MoveFile FsoFile, toDir
      End If
   Next
End Sub

 

[kabanero]

It worked.
Thank you very much Fluff.

 

[Fluff]

Glad we could help & thanks for the feedback

 

[John_w]

Hi John_w
Unfortunately, it did not work either. I tried to run the code on Excel 2019 and 2007. The result is the same. It moves *.xls files, and *.xlsm files as well.
For a reason it does not see the difference.
The code works if I save the file as *.xlsx and set fExt="*.xlsx".
Thank you again.

That's odd. The Dir "*.xls" definitely moves only .xls files for me (Excel 2016). The 'from' folder also contains .xlsx and .xlsm files which aren't moved.
You could use this inside the loop: If file Like fExt Then

 

[Fluff]

That's odd. The Dir "*.xls" definitely moves only .xls files for me (Excel 2016). The 'from' folder also contains .xlsx and .xlsm files which aren't moved.

You're right it does for me as well. I know I've had problems in the past just trying to open xls files with Dir & assumed that it couldn't tell the difference between xls & xlsx etc.