Automatic Hyperlink documents in excel workbook using a command button

[nathandavies]

Hi All,
i was wondering if it is at all possible to automatically hyperlink documents in an excel spreadsheet?

What i'm trying to create is a register off all received documents from emails which are save on a server in a dated folder (2018-05-25) i was wondering if excel could look that folder (documents received) and then hyperlink all the documents into cells?

i have created a workbook template for you see what i'm trying to achieve.

22321

Thank
ND

[nathandavies]

bump

[mancubus]

assign below code to a CB



Sub vbax_62823_list_and_hyperlink_all_files_in_specific_folder()
'http://www.vbaexpress.com/forum/showthread.php?62823-Automatic-Hyperlink-documents-in-excel-workbook-using-a-command-button

Dim FilesPath As String
Dim FilesInFolder
Dim i As Long

FilesPath = "C:\PARENT_FOLDER\SUB_FOLDER\" 'change path to suit. care to include the trailing "\" in fullpath name
FilesInFolder = Split(CreateObject("WScript.Shell").Exec("cmd /c Dir """ & FilesPath & "*.*"" /b /o:n").StdOut.ReadAll, vbCrLf)

With Sheets("Documents Recieived")
.Range("A8").Resize(UBound(FilesInFolder)) = Application.Transpose(FilesInFolder)
For i = LBound(FilesInFolder) To UBound(FilesInFolder) - 1
.Hyperlinks.Add Anchor:=.Range("A" & i + 8), Address:=FilesPath & FilesInFolder(i), TextToDisplay:=Split(FilesInFolder(i), ".")(0)
Next i
End With

End Sub

[nathandavies]

Hi Mancubus,
i have just tried your code, and it doesn't unload the files in the folders. it just hyperlinks the folder directory.

I'm ideally wanting to look in each folder which will be dated (15/05/2018) and hyperlink the files within each folder in column A, we would then have to put the date in the columns G4 onward.

[mancubus]

is at all possible to automatically hyperlink documents in an excel spreadsheet?




and then hyperlink all the documents into cells?

that code meets this requirement.


i think i don't understand your further request(s) such as "unloading a file in a folder".
maybe a "native speaker" vbaexpress member may get your points more easily and offer a solution.
or you may wish to elaborate you requirement(s) and give more details.

for example is it;
- you have a parent folder
- there are several subfolders in it.
- each subfolder contains at least 1 subsubfolder whose name is "2018-05-25" (or can it be today's date in "yyyy-mm-dd" format?)
- each subfolder contains at 1 or more subsubfolders whose name is a date in "yyyy-mm-dd" format (in this case how to distinguish between the dated subfolders to hyperlink its files?)

- you will run the code one time
- you will run the code every day

- how will i get the date and put the date parts on rows 4-5-6 starting at column G?

- what does "unloading a file in folder (or is it folders) mean?

- etc etc


if you can provide the details to help me visualize your needs in my mind, i may, then, offer a solution.

cheers...

[nathandavies]

Hi Mancubus,

sorry for any confusion, i have answered your questions hopefully below. and also attached an example document with some comments on the cells so you can see what i'm trying to achieve a bit better

- There is a parent folder which (Document Received) which contains at least 1 subfolder (YYYY-MM-DD) this contains files received via email on that date. (.pdf / .xls / etc.).
- The code will be run every day, to keep a true record of files.
- The date which is to go in to G4/5/6 is the sub folder name.
- when i say unload, i just mean read the files that are located in the sub folder.

I hope this helps

[mancubus]

sorry for the late response.
i was busy preparing performance report of 2018Q1


what i understand is coded below. :hi:



Sub vbax_62823_list_and_hyperlink_all_files_in_specific_folder()
'http://www.vbaexpress.com/forum/showthread.php?62823-Automatic-Hyperlink-documents-in-excel-workbook-using-a-command-button

Dim FilesPath As String
Dim FilesInFolder
Dim i As Long, StartRow As Long, DtColNum As Long

FilesPath = "C:\Document Received\" & Format(Date, "yyyy-mm-dd") & "\"
'change "C:\ to suit. care to include the trailing "\" in fullpath name

FilesInFolder = Split(CreateObject("WScript.Shell").Exec("cmd /c Dir """ & FilesPath & "*.*"" /b /o:n").StdOut.ReadAll, vbCrLf)

With Sheets("Documents Recieived")
.Range("A8:B" & .Rows.Count).ClearContents
'remove this line if you want to keep the list of the files from precious dates

StartRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Row
.Cells(StartRow, 1).Resize(UBound(FilesInFolder)) = Application.Transpose(FilesInFolder)

For i = LBound(FilesInFolder) To UBound(FilesInFolder) - 1
.Hyperlinks.Add Anchor:=.Cells(i + StartRow, 1), Address:=FilesPath & FilesInFolder(i), TextToDisplay:=Split(FilesInFolder(i), ".")(0)
Next i

DtColNum = .Cells(4, .Columns.Count).End(xlToLeft).Offset(, 1).Column
.Cells(4, DtColNum) = Day(Date)
.Cells(5, DtColNum) = Month(Date)
.Cells(6, DtColNum) = Year(Date)
End With

End Sub