My macro requires me to press enter hundred times to run query (how to avoid it)

[Mughees]

Hi All,

I am new on this forum. I have recently developed a macro through which I am able to list all files present in the sub-folders. However, there are large number of files (in thousands) and once I run my macro, it wants me to keep my hand on enter key for nearly 5 mins. Can any one tell me a command through which I can run macro for once and it does not ask me to press enter for each row and run it once and for all. Thank you in advance.

Below is my query


Option Explicit


Sub listallfiles()


Dim objfso As Scripting.FileSystemObject
Dim objfolder As Scripting.Folder


Set objfso = CreateObject("scripting.filesystemobject")
Set objfolder = objfso.GetFolder("E:\data\2019 data")


Call getfiledetails(objfolder)




End Sub




Function getfiledetails(objfolder As Scripting.Folder)




Dim objfile As Scripting.File
Dim nextrow As Long
Dim objsubfolder As Scripting.Folder




nextrow = Cells(Rows.Count, 1).End(xlUp).Row + 1
For Each objfile In objfolder.Files
Cells(nextrow, 1) = objfile.Name
Cells(nextrow, 2) = objfile.Path
Cells(nextrow, 3) = objfile.Size
Cells(nextrow, 4) = objfile.Type
Cells(nextrow, 5) = objfile.DateCreated
Cells(nextrow, 6) = objfile.DateLastModified
nextrow = nextrow + 1
Next


For Each objsubfolder In objfolder.SubFolders
Call getfiledetails(objsubfolder)

Next


For Each objfile In objfolder.Files
MsgBox objfile.Name
Next














End Function

 

[GirishDhruva]

Just Try to comment the Message box.

 

[Mughees]

Just Try to comment the Message box.

Sorry, I didn't get you.

 

[GirishDhruva]

Try

Option Explicit

Sub listallfiles()

Dim objfso As Scripting.FileSystemObject
Dim objfolder As Scripting.Folder

Set objfso = CreateObject("scripting.filesystemobject")
Set objfolder = objfso.GetFolder("E:\data\2019 data")

Call getfiledetails(objfolder)

End Sub


Function getfiledetails(objfolder As Scripting.Folder)

Dim objfile As Scripting.File
Dim nextrow As Long
Dim objsubfolder As Scripting.Folder

nextrow = Cells(Rows.Count, 1).End(xlUp).Row + 1
For Each objfile In objfolder.Files
Cells(nextrow, 1) = objfile.Name
Cells(nextrow, 2) = objfile.Path
Cells(nextrow, 3) = objfile.Size
Cells(nextrow, 4) = objfile.Type
Cells(nextrow, 5) = objfile.DateCreated
Cells(nextrow, 6) = objfile.DateLastModified
nextrow = nextrow + 1
Next

For Each objsubfolder In objfolder.SubFolders
Call getfiledetails(objsubfolder)

Next

End Function

 

[Mughees]

Thanks alot. Matter resolved

Try

Option Explicit

Sub listallfiles()

Dim objfso As Scripting.FileSystemObject
Dim objfolder As Scripting.Folder

Set objfso = CreateObject("scripting.filesystemobject")
Set objfolder = objfso.GetFolder("E:\data\2019 data")

Call getfiledetails(objfolder)

End Sub


Function getfiledetails(objfolder As Scripting.Folder)

Dim objfile As Scripting.File
Dim nextrow As Long
Dim objsubfolder As Scripting.Folder

nextrow = Cells(Rows.Count, 1).End(xlUp).Row + 1
For Each objfile In objfolder.Files
Cells(nextrow, 1) = objfile.Name
Cells(nextrow, 2) = objfile.Path
Cells(nextrow, 3) = objfile.Size
Cells(nextrow, 4) = objfile.Type
Cells(nextrow, 5) = objfile.DateCreated
Cells(nextrow, 6) = objfile.DateLastModified
nextrow = nextrow + 1
Next

For Each objsubfolder In objfolder.SubFolders
Call getfiledetails(objsubfolder)

Next

End Function