List Files (Specific File Type) from Folder & Sub-folders to Excel w/ VBA

[acerlaptop]

Hi Guys,

Anybody knows how to list all the PDF Files from a Folder and its Sub-folders to the Excel with VBA?

Thank you

 

[Yongle]

Anyway, where do I put this code on the first code you gave?

The 2 lines are in the code provided in post#6 - both lines require amending as indicated in post#9

I ask the file count so i'll know if there are multiple versions of 1 file to so I can check which one is valid.

Why "forget about that" ?
- it does not solve your problem

Try this formula in E2 and copy down
- does it give you what you need ?
=IFERROR(VALUE(RIGHT(SUBSTITUTE(A2,"."&B2,""),1)),"")

 

[acerlaptop]

The 2 lines are in the code provided in post#6 - both lines require amending as indicated in post#9


Why "forget about that" ?
- it does not solve your problem

Try this formula in E2 and copy down
- does it give you what you need ?
=IFERROR(VALUE(RIGHT(SUBSTITUTE(A2,"."&B2,""),1)),"")

The code returns now an error. It says Object doesn't support thes property of method.

=IFERROR(VALUE(RIGHT(SUBSTITUTE(A2,"."&B2,""),1)),"")

 

[acerlaptop]

The 2 lines are in the code provided in post#6 - both lines require amending as indicated in post#9


Why "forget about that" ?
- it does not solve your problem

Try this formula in E2 and copy down
- does it give you what you need ?
=IFERROR(VALUE(RIGHT(SUBSTITUTE(A2,"."&B2,""),1)),"")

Don't mind my previous reply. The code works now. Thanks.

By the way. As I'm studying VBA right now, I'm trying to understand your code, but I'm having a really hard time.

 

[Yongle]

I'm trying to understand your code, but I'm having a really hard time

FileSystemObject is probably not the easiest place to start learning!!

Using VBA FileSystemObject (FSO) in Excel - Examples

In this tutorial, I will show you how to use VBA FileSystemObject (FSO) to work with files and folders on your system or network drives.

trumpexcel.com

 

[acerlaptop]

T

FileSystemObject is probably not the easiest place to start learning!!

Using VBA FileSystemObject (FSO) in Excel - Examples

In this tutorial, I will show you how to use VBA FileSystemObject (FSO) to work with files and folders on your system or network drives.

trumpexcel.com

Thanks for this. Can I continue this thread but with different topic?

Also, Can your make your previous code so that it disregards Temp files (files with ~ in name).

thanks

 

[Yongle]

Can your make your previous code so that it disregards Temp files (files with ~ in name).

Wrap around required for If Err.Number = 0 Then .... = Array(file.Name, extn, file.DateCreated, Replace(file.path, file.Name, ""))

        If InStr(file.Name, "~") = 0 Then
            If Err.Number = 0 Then Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Resize(, 4) = Array(file.Name, extn, file.DateCreated, Replace(file.path, file.Name, ""))
        End If

Can I continue this thread but with different topic?

No

 

[acerlaptop]

T

Wrap around required for If Err.Number = 0 Then .... = Array(file.Name, extn, file.DateCreated, Replace(file.path, file.Name, ""))

        If InStr(file.Name, "~") = 0 Then
            If Err.Number = 0 Then Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Resize(, 4) = Array(file.Name, extn, file.DateCreated, Replace(file.path, file.Name, ""))
        End If

No

Thanks

 

[Tom2020]

Wrap around required for If Err.Number = 0 Then .... = Array(file.Name, extn, file.DateCreated, Replace(file.path, file.Name, ""))

        If InStr(file.Name, "~") = 0 Then
            If Err.Number = 0 Then Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Resize(, 4) = Array(file.Name, extn, file.DateCreated, Replace(file.path, file.Name, ""))
        End If

No

Hi Yongle,
maybe you can help me.

I found your previous VBA-code very useful, but I would like to know how to customize it for my proposal.
First of all, I changed your Code to this:
No Cells.Clear (that is important for my proposal)

however, my excel sheet hast 5 columns, like
ColumnA, empty
ColumnB, File name
ColumnC, Type
ColumnD, Created
ColumnE, File Path

Second of all, I need some change in your code like:

status​	Already File in the list​	Created​	New added File​
1 (folder got new file)	-​	-​	+​
2 (Created of files changed & folder got new files)	-​	+​	+​
3 (nothing happened msgBox)	-​	-​	-​

1) If
files in the list (Created) didn’t change but the folder got some new files, then add new files at the end of empty Row.
2) ElseIf
Files (Created) did change in the list and the folder got some new files then
-add new files at the end of empty Row and
-update the cell belong to the file with Created
3) Else
files in the list (Created) didn’t change and the folder didn’t get any new files, then msgBox = no change at all.

Thx for your help