• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Files in folder get directory info

Dear Abhijeet

I hope the below code will work.

Code: 
Sub FileDetails()
  Dim fso As New FileSystemObject
  Dim myFolder As Folder
  Dim myFile As File
  Dim i As Integer
  
  Set myFolder = fso.GetFolder("D:\ARRAY FORMULAS") ' change the folder path
     
  If myFolder.Files.Count > 0 Then
  i = 1
  For Each myFile In myFolder.Files
  Range("A" & i).Value = myFile.Name
  Range("B" & i).Value = myFile.DateLastAccessed
  Range("C" & i).Value = myFile.DateLastModified
  Range("D" & i).Value = myFile.Type
  Range("E" & i).Value = myFile.Size
  i = i + 1
  Next
  Else
  MsgBox "No files in the specified Folder", vbOKOnly, "No files"
  End If
End Sub
 
Last edited by a moderator:
Dear Abhijeet,

VBA code to work properly, you must first create reference to the Filesystemobject.
Tools -> References -> Microsoft Scripting Runtime
 
vijay.vizzu said:
Dear Abhijeet,

VBA code to work properly, you must first create reference to the Filesystemobject.
Tools -> References -> Microsoft Scripting Runtime
Click to expand...
Just for future readers, if you want to avoid having to set this (which is early binding), can can change vijay's code to use late binding like so:


Code: 
Sub FileDetails()
Dim fso As Object
Dim myFolder As Folder
Dim myFile As File
Dim i As Integer

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set myFolder = fso.GetFolder("D:\ARRAY FORMULAS") ' change the folder path

If myFolder.Files.Count > 0 Then
i = 1
    For Each myFile In myFolder.Files
        Range("A" & i).Value = myFile.Name
        Range("B" & i).Value = myFile.DateLastAccessed
        Range("C" & i).Value = myFile.DateLastModified
        Range("D" & i).Value = myFile.Type
        Range("E" & i).Value = myFile.Size
        i = i + 1
    Next
Else
    MsgBox "No files in the specified Folder", vbOKOnly, "No files"
End If
End Sub
 
Luke M said:
Just for future readers, if you want to avoid having to set this (which is early binding), can can change vijay's code to use late binding like so:


Code: 
Sub FileDetails()
Dim fso As Object
Dim myFolder As Folder
Dim myFile As File
Dim i As Integer

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set myFolder = fso.GetFolder("D:\ARRAY FORMULAS") ' change the folder path

If myFolder.Files.Count > 0 Then
i = 1
    For Each myFile In myFolder.Files
        Range("A" & i).Value = myFile.Name
        Range("B" & i).Value = myFile.DateLastAccessed
        Range("C" & i).Value = myFile.DateLastModified
        Range("D" & i).Value = myFile.Type
        Range("E" & i).Value = myFile.Size
        i = i + 1
    Next
Else
    MsgBox "No files in the specified Folder", vbOKOnly, "No files"
End If
End Sub
Click to expand...
Hi Luke when run this macro get Run Time Error 91 please tell me what changes need to do in this code or Setting in Excel
 
Hi Abhijeet ,

Change this line from :

Set myFolder = fso.GetFolder("D:\ARRAY FORMULAS") ' change the folder path

to :

Set myFolder = objfso.GetFolder("D:\ARRAY FORMULAS") ' change the folder path

Narayan
 
Hi Abhijeet ,

Better still , use this code , which has two changes to Luke's posted code :

Code: 
Sub FileDetails()
Dim objfso As Object
Dim myFolder As Folder
Dim myFile As File
Dim i As Integer

Set objfso = CreateObject("Scripting.FileSystemObject")
Set myFolder = objfso.GetFolder("D:\ARRAY FORMULAS") ' change the folder path

If myFolder.Files.Count > 0 Then
i = 1
    For Each myFile In myFolder.Files
        Range("A" & i).Value = myFile.Name
        Range("B" & i).Value = myFile.DateLastAccessed
        Range("C" & i).Value = myFile.DateLastModified
        Range("D" & i).Value = myFile.Type
        Range("E" & i).Value = myFile.Size
        i = i + 1
    Next
Else
    MsgBox "No files in the specified Folder", vbOKOnly, "No files"
End If
End Sub
Narayan
 
Hi Narayan it working fine but tell me when macro run then ask for choose folder how to do this because every time go into Code then change the folder path it is not possible
 
Dear Abhijeet

Just declare one variable - dim fldPath as string (to hold selected folder path)

then before set my folder paste the below code

Application.FileDialog(msoFileDialogFolderPicker).Show
fldPath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)
 

Hi Abhijeet,

just see again the method GetOpenFilename as we already told you in some of your previous posts ! :rolleyes:
​
 
You can avoid using APIs by using Vijay's code as above.

Here's one more:
Code: 
Sub SelectFolder()
Dim strFldName As String

'\\ Get folder browser
On Error Resume Next
strFldName = CreateObject("Shell.Application").BrowseForFolder(0, "Browse Folder", 0, "").Self.Path
On Error GoTo 0

'\\ Don't process further
If strFldName = "" Then
  MsgBox "No Folder selected!", vbExclamation
  Exit Sub
End If

'\\ Variable strFldName holds the folder path string

End Sub
 
shrivallabha said:
You can avoid using APIs by using Vijay's code as above.

Here's one more:
Code: 
Sub SelectFolder()
Dim strFldName As String

'\\ Get folder browser
On Error Resume Next
strFldName = CreateObject("Shell.Application").BrowseForFolder(0, "Browse Folder", 0, "").Self.Path
On Error GoTo 0

'\\ Don't process further
If strFldName = "" Then
  MsgBox "No Folder selected!", vbExclamation
  Exit Sub
End If

'\\ Variable strFldName holds the folder path string

End Sub
Click to expand...
Hi shrivallabha can please give combine both macro i tried but am not getting the output
 
I have used Narayan's code to add up the rest.
Code: 
Sub FileDetails()
Dim objfso As Object, myFolder As Object, myFile As Object
Dim strFldName As String
Dim i As Integer

'\\ Get folder browser
On Error Resume Next
strFldName = CreateObject("Shell.Application").BrowseForFolder(0, "Browse Folder", 0, "").Self.Path
On Error GoTo 0

'\\ Don't process further
If strFldName = "" Then
  MsgBox "No Folder selected!", vbExclamation
  Exit Sub
End If

Set objfso = CreateObject("Scripting.FileSystemObject")
Set myFolder = objfso.GetFolder(strFldName) ' change the folder path

If myFolder.Files.Count > 0 Then
i = 1
  For Each myFile In myFolder.Files
  Range("A" & i).Value = myFile.Name
  Range("B" & i).Value = myFile.DateLastAccessed
  Range("C" & i).Value = myFile.DateLastModified
  Range("D" & i).Value = myFile.Type
  Range("E" & i).Value = myFile.Size
  i = i + 1
  Next
Else
  MsgBox "No files in the specified Folder", vbOKOnly, "No files"
End If
End Sub

@NARAYANK991 & @Luke M
You need to late bind these as well.
Code: 
Dim myFolder As Folder
Dim myFile As File
to
Code: 
Dim myFolder As Object
Dim myFile As Object
 
Hi shrivallabha...

I learn new concept or you can say new way to get rid of this. Thank a lot for sharing this new way with us.

I never used Shell.application object. So i will practice this new object
 
vijay.vizzu said:
Hi shrivallabha...

I learn new concept or you can say new way to get rid of this. Thank a lot for sharing this new way with us.

I never used Shell.application object. So i will practice this new object
Click to expand...
Hi Vijay,

You are doing quite nicely in coding.

There are many ways to do the same task. If you want to some of the Shell features. Then in Visual Basic Editor set reference to:
Microsoft Shell Control and Automation [Shell.Application]
Windows Script Host Object Model [WScript.Shell]

And then use them in the code using Early Binding. This will let you explore lot of functionalities that these two objects hold.
 
You must log in or register to reply here.
Back
Top