Wildcard file open

[Zabman]

Hi,

I have some code that determines if a directory has any files present as follows:

Function FileChk() As Boolean
' Determine if there are files to convert
    strFile = Dir("c:\TNT\")
    If strFile = "" Then
        FileChk = False
        MsgBox "No New Orders Found"
    ElseIf strFile <> "" Then
        FileChk = True
    End If
End Function

This works fine, but I am struggling to open files with a wildcard search:

Workbooks.Open Filename:="C:\TNT\*.xlsx"

There can be anywhere from o to 50 files in this folder, but the code is returning a 'file cant be found (runtime 1004 I think) error.

Can anyone help?

 

[Ralajer]

I had some code that counted files of certain extension in a directory. I changed it around a little to open files. I test it twice and got it to work without any errors opening 6 empty 'xlsx' files.

Sub OpenFiles()

    Dim Folder As Object
    Dim file As Object
    Dim fso As Object
    
    Dim numFiles As Integer
    Dim folderPath As String, fileExt As String
        
    folderPath = "D:\Test\"
    fileExt = "xlsx"
       
   Set fso = CreateObject("Scripting.FileSystemObject")
   
    If folderPath <> "" Then
        Set Folder = fso.GetFolder(folderPath)
        
        For Each file In Folder.Files
            If Right(file, Len(fileExt)) = fileExt Then
                Workbooks.Open Filename:=file
            End If
        Next file
    End If

End Sub

EDIT: It would error out if a file was already open as it tried to open the hidden file '~$FileName.xlsx'

 

[Ralajer]

To prevent it from opening hidden files which are prefixed with '~$' replace

If Right(file, Len(fileExt)) = Then

in the For Each file Loop with

If Right(file, Len(fileExt)) = fileExt And Mid(file, Len(folderPath) + 1, 2) <> "~$" Then