The Dir() function and FileSytemObect give the same result. As long as you put the result of the dir() function of of the objFile.Name directly in a cell, you get the original files name. But as sson as you put the result in a string, the uncides are olst. See picture
Added Febr 28-2016 the code as requested:
Function ListFiles(ByVal sPath As String, Optional ByVal sFilter As String = vbNullString, Optional NumberFilesFound As Integer = 0) As String()
'REF: http://www.exceltrick.com/formulas_macros/filesystemobject-in-vba/
'Create a reference of Microsoft Scripting Runtime Library VBA->Tools(Extra)-> References(verwijzingen) and mark Microsoft Scripting Runtime
'Creating a FileSystemObject
Dim FSO As New FileSystemObject
'Declaring variables
Dim objFolder As Folder
Dim objFile As File
Dim NextRow As Long
ReDim Rslt(0) As String
Dim I As Integer
Dim TempString As String
On Error GoTo 0
'Specify the path of the folder
'strPath = CurDir & "\berichten\"
'Create the object of this folder
Set objFolder = FSO.GetFolder(sPath)
'Check if the folder is empty or not
If objFolder.Files.Count = 0 Then
MsgBox "No files were found...", vbExclamation
Exit Function
End If
'Loop through each file in the folder
I = 0
For Each objFile In objFolder.Files
'List the name, size, and date/time of the current file
ReDim Preserve Rslt(I)
Rslt(I) = objFile.Name
I = I + 1
Next objFile
ListFiles = Rslt
NumberFilesFound = I
End Function
The picture show additional an output to the cell( 25,2) and I=I just for stopping the process.
Bookmarks