Possible to use multiple Dir() functions?

[russmann2000]

I have code I'm using to import a folder full of excel files and spitting out the data onto a word document. The code works good on the first run, but doesn't load the other files.

I use the Dir() function to load the files in the folder but also use the Dir() function in another process to check if the file exists. It seems like it's stripping out the rest of the filenames after the first run.

Is it possible to use the Dir() more than once in the same process?


Thank you in advance for your help.

 

[Ranman256]

don't use Dir(), instead use FileSystemObjects:

Public Sub ImportAllFilesInFolder(byval pvDir )
Dim sTbl As String, sSql As String, sPath As String, sFile As String
Dim FSO, oFolder, oFile, oRX

On Error GoTo ErrImp
    
Set FSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = FSO.GetFolder(pvDir)

For Each oFile In oFolder.Files
      MsgBox oFile
Next
      
      'clear memory
Set FSO = Nothing
Set oFolder = Nothing
Exit Sub
ErrImp:
MsgBox Err.Description, , Err
End Sub

 

[theDBguy]

Hi. I agree with using FSO. As you found out, the Dir() function reuses the "last" argument the function received. So, when you use Dir() again with a different argument, the first one is gone.

 

[russmann2000]

BRUH! Thanks so much, works perfect!