B
Bobby
Hi,
I have this VBA code that give me this error message:
===> Keep run-time error '53'
===> File not found
But file exist!
The message that is driving me is the following line:
Set myFile = fso.GetFile(CStr(varFileList(l)))
Can someone help?
Thanks ahead.
(Code start here)
----------------------------------------------------------------------------------------------------------------
Option Explicit
Sub GetFileList()
Dim strFolder As String
Dim x, varFileList As Variant
Dim fso As Object, myFile As Object
Dim myResults As Variant
Dim l As Long
Set myFile = Nothing
Set fso = Nothing
strFolder = "D:\Robert\test"
' Get a list of all the files in this directory.
' Note that this isn't recursive... although it could be...
varFileList = fcnGetFileList(strFolder)
If Not IsArray(varFileList) Then
MsgBox "No files found.", vbInformation
Exit Sub
End If
' Now let's get all the details for these files
' and place them into an array so it's quick to dump to XL.
ReDim myResults(0 To UBound(varFileList) + 1, 0 To 5)
' place make some headers in the array
myResults(0, 0) = "Filename"
myResults(0, 1) = "Size"
myResults(0, 2) = "Created"
myResults(0, 3) = "Modified"
myResults(0, 4) = "Accessed"
myResults(0, 5) = "Full path"
Set fso = CreateObject("Scripting.FileSystemObject")
x = varFileList(0)
x = varFileList(l)
x = varFileList(2)
x = varFileList(3)
' Loop through our files
For l = 0 To UBound(varFileList)
Set myFile = fso.GetFile(CStr(varFileList(l)))
myResults(l + 1, 0) = CStr(varFileList(l))
myResults(l + 1, 1) = myFile.Size
myResults(l + 1, 2) = myFile.DateCreated
myResults(l + 1, 3) = myFile.DateLastModified
myResults(l + 1, 4) = myFile.DateLastAccessed
myResults(l + 1, 5) = myFile.Path
Next l
' Dump these to a worksheet
fcnDumpToWorksheet myResults
'tidy up
Set myFile = Nothing
Set fso = Nothing
End Sub
Private Function fcnGetFileList(ByVal strPath As String, Optional
strFilter As String) As Variant
' Returns a one dimensional array with filenames
' Otherwise returns False
Dim f As String
Dim i As Integer
Dim FileList() As String
If strFilter = "" Then strFilter = "*.*"
Select Case Right$(strPath, 1)
Case "\", "/"
strPath = Left$(strPath, Len(strPath) - 1)
End Select
ReDim Preserve FileList(0)
f = Dir$(strPath & "\" & strFilter)
Do While Len(f) > 0
ReDim Preserve FileList(i) As String
FileList(i) = f
i = i + 1
f = Dir$()
Loop
If FileList(0) <> Empty Then
fcnGetFileList = FileList
Else
fcnGetFileList = False
End If
End Function
Private Sub fcnDumpToWorksheet(varData As Variant, Optional mySh As
Worksheet)
Dim iSheetsInNew As Integer
Dim sh As Worksheet, wb As Workbook
Dim myColumnHeaders() As String
Dim l As Long, NoOfRows As Long
If mySh Is Nothing Then
'make a workbook if we didn't get a worksheet
iSheetsInNew = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Set wb = Application.Workbooks.Add
Application.SheetsInNewWorkbook = iSheetsInNew
Set sh = wb.Sheets(1)
Else
Set mySh = sh
End If
With sh
Range(.Cells(1, 1), .Cells(UBound(varData, 1) + 1, UBound(varData, 2)
+ 1)) = varData
..UsedRange.Columns.AutoFit
End With
Set sh = Nothing
Set wb = Nothing
End Sub
I have this VBA code that give me this error message:
===> Keep run-time error '53'
===> File not found
But file exist!
The message that is driving me is the following line:
Set myFile = fso.GetFile(CStr(varFileList(l)))
Can someone help?
Thanks ahead.
(Code start here)
----------------------------------------------------------------------------------------------------------------
Option Explicit
Sub GetFileList()
Dim strFolder As String
Dim x, varFileList As Variant
Dim fso As Object, myFile As Object
Dim myResults As Variant
Dim l As Long
Set myFile = Nothing
Set fso = Nothing
strFolder = "D:\Robert\test"
' Get a list of all the files in this directory.
' Note that this isn't recursive... although it could be...
varFileList = fcnGetFileList(strFolder)
If Not IsArray(varFileList) Then
MsgBox "No files found.", vbInformation
Exit Sub
End If
' Now let's get all the details for these files
' and place them into an array so it's quick to dump to XL.
ReDim myResults(0 To UBound(varFileList) + 1, 0 To 5)
' place make some headers in the array
myResults(0, 0) = "Filename"
myResults(0, 1) = "Size"
myResults(0, 2) = "Created"
myResults(0, 3) = "Modified"
myResults(0, 4) = "Accessed"
myResults(0, 5) = "Full path"
Set fso = CreateObject("Scripting.FileSystemObject")
x = varFileList(0)
x = varFileList(l)
x = varFileList(2)
x = varFileList(3)
' Loop through our files
For l = 0 To UBound(varFileList)
Set myFile = fso.GetFile(CStr(varFileList(l)))
myResults(l + 1, 0) = CStr(varFileList(l))
myResults(l + 1, 1) = myFile.Size
myResults(l + 1, 2) = myFile.DateCreated
myResults(l + 1, 3) = myFile.DateLastModified
myResults(l + 1, 4) = myFile.DateLastAccessed
myResults(l + 1, 5) = myFile.Path
Next l
' Dump these to a worksheet
fcnDumpToWorksheet myResults
'tidy up
Set myFile = Nothing
Set fso = Nothing
End Sub
Private Function fcnGetFileList(ByVal strPath As String, Optional
strFilter As String) As Variant
' Returns a one dimensional array with filenames
' Otherwise returns False
Dim f As String
Dim i As Integer
Dim FileList() As String
If strFilter = "" Then strFilter = "*.*"
Select Case Right$(strPath, 1)
Case "\", "/"
strPath = Left$(strPath, Len(strPath) - 1)
End Select
ReDim Preserve FileList(0)
f = Dir$(strPath & "\" & strFilter)
Do While Len(f) > 0
ReDim Preserve FileList(i) As String
FileList(i) = f
i = i + 1
f = Dir$()
Loop
If FileList(0) <> Empty Then
fcnGetFileList = FileList
Else
fcnGetFileList = False
End If
End Function
Private Sub fcnDumpToWorksheet(varData As Variant, Optional mySh As
Worksheet)
Dim iSheetsInNew As Integer
Dim sh As Worksheet, wb As Workbook
Dim myColumnHeaders() As String
Dim l As Long, NoOfRows As Long
If mySh Is Nothing Then
'make a workbook if we didn't get a worksheet
iSheetsInNew = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Set wb = Application.Workbooks.Add
Application.SheetsInNewWorkbook = iSheetsInNew
Set sh = wb.Sheets(1)
Else
Set mySh = sh
End If
With sh
Range(.Cells(1, 1), .Cells(UBound(varData, 1) + 1, UBound(varData, 2)
+ 1)) = varData
..UsedRange.Columns.AutoFit
End With
Set sh = Nothing
Set wb = Nothing
End Sub