Ok, I managed to identify the source of errors, it was a data type error, but not where I expected.
Here is the codes you can use:
ClearData
ListMyFiles CStr(Range("B1").Text), CBool(Range("A1").Text)
HyperLinks
End Sub
Sub ClearData()
Worksheets("Sheet1").UsedRange.Offset(2, 0).ClearContents
End Sub
Sub ListMyFiles(mySourcePath As String, IncludeSubfolders As Boolean, Optional iRow As Long = 3)
Dim ShellObject As Object, MyObject As Object, MySource As Object, MyFile As Object, DirObject As Object, iCol As Byte
Set ShellObject = CreateObject("Shell.Application")
Set DirObject = ShellObject.Namespace(CVar(mySourcePath))
Set MyObject = CreateObject("Scripting.FileSystemObject")
Set MySource = MyObject.GetFolder(mySourcePath)
For Each MyFile In MySource.Files
iCol = 2
Cells(iRow, iCol).Value = MyFile.Path
iCol = iCol + 1
Cells(iRow, iCol).Value = MyFile.Name
iCol = iCol + 1
'Retrieve file extension
Cells(iRow, iCol).Value = Right(MyFile.Name, Len(MyFile.Name) - InStrRev(MyFile.Name, "."))
iCol = iCol + 1
Cells(iRow, iCol).Value = (MyFile.Size / 1024)
iCol = iCol + 1
Cells(iRow, iCol).Value = DirObject.GetDetailsOf(DirObject.ParseName(MyFile.Name), 20) 'author
iCol = iCol + 1
Cells(iRow, iCol).Value = MyFile.DateCreated
iCol = iCol + 1
Cells(iRow, iCol).Value = MyFile.DateLastModified
iRow = iRow + 1
Next
If IncludeSubfolders Then
For Each mySubFolder In MySource.SubFolders
Call ListMyFiles(mySubFolder.Path, True, iRow)
Next
End If
Set ShellObject = Nothing
Set DirObject = Nothing
Set MyObject = Nothing
Set MySource = Nothing
End Sub
The error source is in red, Namespace method expects a variant data type, not a string.
Function ListMyFiles() works smoothly now taking the auguments specifed on cell A1 & B2 instead of hard code. While ClearData() got a little problem - in case the number of files is less that previous run, then the excess portion in previous run won't be cleared out. Therefore, I kept my version without adopting it. Anyhow, I'm heartily grateful to you for all that you have done to me. You are the real "author" of this VBA script. I'll register you name on my file in honor of your contribution.
Great, glad to hear you're happy with the solution.
Worksheets("Sheet1").UsedRange.Offset(2, 0).ClearContents should work, it does not matter how many rows there are, it will identify the existing used range, in fact, it will clear the used range+ 2 more rows, because I used offset to preserve the first 2 rows.
Cheers,
Catalin
You are right, I found it's my fault because "Sheet1" was not my active worksheet. Your simple code is great for data content cleaning. I learned one more skill from you. Thanks again.
It does not have to be active, as long as it's in the same workbook. It might fail only if you have another workbook active, and that one does not have a worksheet named "Sheet1" (but if that workbook has a sheet with this name, that will be cleared, not the one you want).
In such cases, it's best to use fully qualified references:
ThisWorkbook.Worksheets("Sheet1").UsedRange.Offset(2, 0).ClearContents
This way you will now for sure what is cleared 🙂
Got it. Thanks for your further explanation.
Hi Catalin,
I got the following function which can open a browse window. If I want to implement it on your VBA, how your script can be modified? Would you like to teach me? Of course, if you can't spare your time, just forget it and forgive me bothering you so much!
Function BrowseForFolder(Optional OpenAt As Variant) As Variant
'Function purpose: To Browser for a user selected folder.
'If the "OpenAt" path is provided, open the browser at that directory
'NOTE: If invalid, it will open at the Desktop level
Dim ShellApp As Object
'Create a file browser window at the default folder
Set ShellApp = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please choose a folder", 0, OpenAt)
'Set the folder to that selected. (On error in case cancelled)
On Error Resume Next
BrowseForFolder = ShellApp.self.path
On Error GoTo 0
'Destroy the Shell Application
Set ShellApp = Nothing
'Check for invalid or non-entries and send to the Invalid error
'handler if found
'Valid selections can begin L: (where L is a letter) or
'\ (as in \servernamesharename. All others are invalid
Select Case Mid(BrowseForFolder, 2, 1)
Case Is = ":"
If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
Case Is = ""
If Not Left(BrowseForFolder, 1) = "" Then GoTo Invalid
Case Else
GoTo Invalid
End Select
Exit Function
Invalid:
'If it was determined that the selection was invalid, set to False
BrowseForFolder = False
End Function
Why would you use that? You have dedicated file and folder picker dialog:
Dim Fld as string
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & ""
.Title = "Please select a folderfolder"
.Show
If .SelectedItems.Count <> 0 Then
Fld = .SelectedItems(1)
Else
MsgBox "No folder selected.", vbExclamation
Exit Sub
End If
End With
ClearData
ListMyFiles Fld, CBool(Range("A1").Text)
HyperLinks
End Sub
If you want to use your function, use it like this, but note that you have to check if the function returned a valid path, because it's set to return a Variant type (it can return a path string, or a Boolean value of False if no folder is selected:
Dim Fld As Variant
Fld = BrowseForFolder
If TypeName(fld) = "Boolean" Then MsgBox("No Folder Selected"): Exit Sub
Fld=BrowseForFolder
ClearData
ListMyFiles Fld, CBool(Range("A1").Text)
HyperLinks
End Sub
It was easier if you declared the function with a String as the return type:
Function BrowseForFolder(Optional OpenAt As Variant) As String
.....
Invalid:
'If it was determined that the selection was invalid, set to a specific text:
BrowseForFolder = "No Folder"
Now you know it's a string comparison you need to do, and you also know what text is returned in case of error - "No Folder":
Sub ListFiles()
Dim Fld As String
Fld = BrowseForFolder
If Fld Like "No Folder" Then MsgBox("No Folder Selected"): Exit Sub
Fld=BrowseForFolder
ClearData
ListMyFiles Fld, CBool(Range("A1").Text)
HyperLinks
End Sub
As I mentioned before, you have to be careful at parameter types, if you understand this, it will be easier for you to manipulate codes.
Indeed, the script of your folder picker dialog is much concise than the Function BrowseForFolder( ) I obtained from others. Besides, I don't know why it would open the browse twice and I need to select the target folder twice accordingly for further processing. Anyway I'm so lucky to learn one more lesson from you. I do appreciate it.
