From word file names to excel database

[petertheo]

Greetings to all.
I am new here and to VBA and would like assistance with the following:
I visit various clients and for which visit a Visit Report is done on Word.
The VR is saved with the file name Client yyyymmdd VR
What I need to do is: Have an excel with VBA code to collect all VR from the various country locations and file each and every VR in a spreasheet as data.
The file name in each cell in the spreadsheet to be 'active' so when I click on this the VR in the Word will open up for viewing/editing.

I would appreciate any help that I can have on this. The code perhaps will be great.

Thanking you in advance

[jonh]

Sub Example()
ListFiles "c:\myfolder\", 1, 1, 1, 1, 1
End Sub

folder - folder path
subfolders - include sub folders
astree - display as tree or list
foldernames - list folder names
rw - start row
col - start column


Sub ListFiles(folder As String, subfolders As Boolean, astree As Boolean, foldernames As Boolean, rw As Long, col As Integer)
Cells.Clear
If astree Then Cells.ColumnWidth = 3 Else Cells.ColumnWidth = 15
ListFilesMain CreateObject("Scripting.FileSystemObject").GetFolder(folder), subfolders, astree, foldernames, rw, col
End Sub

Sub ListFilesMain(folder, subfolders As Boolean, astree As Boolean, _
foldernames As Boolean, rw As Long, col As Integer)
If foldernames Then
Cells(rw, col) = folder.Path: rw = rw + 1
End If
For Each file In folder.files
If InStr(file.Name, ".doc") Then
Hyperlinks.Add Cells(rw + nr, col), file.Path, , , file.Name
nr = nr + 1
End If
Next
If nr Then
rw = rw + nr
Else
If foldernames Then
rw = rw - 1
Cells(rw, col) = ""
End If
End If
If subfolders Then
For Each folder In folder.subfolders
ListFilesMain folder, subfolders, astree, _
foldernames, rw, IIf(astree, col + 1, col)
Next
End If
End Sub

[petertheo]

Thank you Jonh.
Can I just copy this code and paste to my VBA page in the excel file (new one that I will make)?
Please advise what else I may need.
Thank you

[jonh]

Np.

Paste the code in a sheet module

change the path
ListFiles "c:\myfolder\", 1, 1, 1, 1, 1

Run the macro. e.g. Sheet1.Example

[petertheo]

Hi Jonh, did as follows on VBA in excel:
Sub Example()
ListFiles "c:\users\Peter\My Documents\1AA VISIT REPORTS\", 1, 1, 1, 1, 1
End Sub

If I debug/run to cursor
I get Compile error: Sub or Function not defined...

[petertheo]

Hey Jonh,
It works! I am so excited as this saves me a lot of hassle....
I owe you a few beers.... Now I will only need to 'smooth' it a bit as the data (i.e. File names are not on the same column but offsets here and there...
I wonder why.

[jonh]

Change the second 1 to a 0.

[petertheo]

Sorry I am not with you. You mean 1, 1, 1, 1, 1 to 1, 0, 1, 1 ,1?

[jonh]

Yes, that is correct.

ListFiles "c:\myfolder\", 1, 0, 1, 1 ,1

folder - folder path
subfolders - include sub folders
astree - display as tree or list
foldernames - list folder names
rw - start row
col - start column

[petertheo]

Wow I did try it before your reply just from curiosity and works like a charm. Thank you again but I am real happy to have joined this site.
So as I understand, I have these two 'codes' under one VBA page and when I 'Run the VBA it does the job.
So every time now that I add more VR as I visit will the system 'update' them at the bottom of the database or it will re-do the whole thing again?
Can I have an 'Update' Button to do this so I can have control of when it updates?
I know I ask for a lot but now that I found Help, I might as well do the best of it....

[snb]

Much faster:


sub M_snb()
sn=split(createobject("wscript.shell").exec("cmd /c dir ""c:\users\Peter\My Documents\1AA VISIT REPORTS\*.doc*"" /b/s").stdout.readall,vbcrlf)
sheet1.cells(1).resize(ubound(sn)+1)=application.transpose(sn)
End Sub

[jonh]

It'll clear the sheet and start again.

How to add a macro to a button. (https://support.microsoft.com/en-us/kb/141689)

[petertheo]

Hi Jonh,
I did add a button and did enter the code you send me in the page that opened up when I double clicked on the new button.
However when the excel page was updated the button is right on the top of the data. Can you advise How To Move the Button from there or I have to do all from the start? i.e. wipe all out and make new button more to the right hand side...

[jonh]

Right click the button, press Escape to close the pop up menu then move the button.

[petertheo]

But when I right click on the button nothing happens... I tried right click also with Alt Click and CTrl but no show...

[jonh]

I don't know then. For me it selects the button and puts sizing handles on it.