Modification of a code to list sub-folders as well as folders

[wojciechziolkowski18]

Hi,

Firstly that's my first post so I would like to say hello to everyone in here.

I'm trying to modify following code to list also files from all subfolders. I'm a completely newbie. I get this code from random website. It seems to be working nice expect I need list of all files in subfolders as well:

Sub ListFilesFolder()
'MVP OShon from VBATools.pl
Dim ob As Object, pliki As Object, plik As Object
Dim folder As Object, sciezka$, r&: r = 2
Dim SubFolder As Object
Dim subfld As Object
Dim xRow As Long
sciezka = "C:\Users\Wojtek\Downloads\04. Unti Drawings\04. Unti Drawings\01. IW - Internal walls"
Cells(1, 1) = "nazwa pliku"
Cells(1, 2) = "plik ze ścieżka"
Cells(1, 3) = "rozmiar"
Cells(1, 4) = "rodzaj"
Cells(1, 5) = "utworzono"
Cells(1, 6) = "ostatnio otwarty"
Cells(1, 7) = "data modyfikacji"
Cells(1, 8) = "atrybut"
Cells(1, 9) = "ścieżka dos"
Set ob = CreateObject("Scripting.FilesystemObject")
Set folder = ob.getFolder(sciezka)
Set pliki = ob.getFolder(folder).Files

With Application
.ScreenUpdating = False
.EnableEvents = False
For Each plik In pliki
'przykład ograniczeń:
If Mid(LCase(plik), InStrRev(plik, ".") + 1, 3) = "pdf" Then 'ograniczenie po rodz pliku
'If CDate(plik.DateCreated) > Format(Now - 7, "YYYY-MM-DD") Then 'ograniczenie po dacie
Cells(r, 1) = plik.Name
Cells(r, 2) = folder & "" & plik.Name
Cells(r, 3) = plik.Size
Cells(r, 4) = plik.Type
Cells(r, 5) = plik.DateCreated
Cells(r, 6) = plik.DateLastAccessed
Cells(r, 7) = plik.DateLastModified
Cells(r, 8) = plik.Attributes
Cells(r, 9) = plik.ShortPath & plik.ShortName
r = r + 1
End If
'End If
Next plik
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

I would be glad if someone can take a look at this. Thanks in advance.

Basically what I'm after is a procedure, which will list all pdf files with subfolders and folders for location given (in function) next step will be modify it in a way, that location given is actually collumn of locations (paths) in excel). One location will do for a prototype

[Keebellah]

Hi welcome,
I suggest you Google for the many ways it's possible.
This is one of the many links
https://www.extendoffice.com/documen...ubfolders.html

[Fluff13]

Hi & welcome to the board.
Have a look here http://www.xl-central.com/list-files...lders-fso.html

[wojciechziolkowski18]

Hi, thanks for your reply. Believe me or not but I'm gooing it since last Wednesday and I allready came across a lot of webiste but each of them has a bit, which doesn't work for me. The one in the link shows pop-up window where you need to paste a link. I don't want to paste a link. I want a link to be in the code.

I believe this code needs to be changed for something with link

With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Choose the folder"
.Show
End With

? when I try to modify it I'm getting debug error.

[Keebellah]

How about attaching the file with the macro

[wojciechziolkowski18]

How about attaching the file with the macro

Hi,
I'm unable to attached the file as its size exceeds 1000kBs.
I'm also unable to attach links as I'm a new member.


I manage to trick this system, below is a link to a website where a file can be downloaded

ufile.io/5owt2



So I'm still working on this prototype and to be fair I've got no good results - im working my way around using standard excel functions.

At the moment there're couple things I struggle with:
First thing is this code:

Please Login or Register  to view this content.

I'm trying to modify few things:

1. Change type of output files to "pdf" only

2. Change type of output files to the ones, which name is: 05-BYL-0XXX"*", where 05-BYL-0 is constant and XXX are numbers, "*" can be anything, IE files I want are 05-BYL-0194-blablabla, 05-BYL-0112-asfasdasd, but I don't want file named for example )05-STR-0XDF-blablabla

3. Change strPath = "path" to a value in a column (say C2) - when all files within path in cell C2 are listed procedure moves to another path (cell C3) and so on.

4. Find a way to print files directly from excel. I want a function, which will print pdf file if link to this file location is In ceratin cell.

[Keebellah]

Remove unnecessary empty rows which I am sure there are plenty of in your file, else just ZIP the file (compress) and attach

[wojciechziolkowski18]

Hi,

I manage to make it smaller. Basically this file is a report off "impact" which is quite advanced production monitoring tool. For some reason it creates 1000000 rows when generating a report.

Its attached now.

[Keebellah]

Will take a look, no promises