Consulting

Results 1 to 7 of 7

Thread: Automatic Hyperlink documents in excel workbook using a command button

  1. #1

    Automatic Hyperlink documents in excel workbook using a command button

    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.

    Document Register -1.xlsx

    Thank
    ND

  2. #2
    bump

  3. #3
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "Where I lay my head is home" :D
    Posts
    2,644
    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
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

  4. #4
    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.

  5. #5
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "Where I lay my head is home" :D
    Posts
    2,644
    Quote Originally Posted by nathandavies View Post
    is at all possible to automatically hyperlink documents in an excel spreadsheet?
    Quote Originally Posted by nathandavies View Post
    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...
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

  6. #6
    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
    Attached Files Attached Files

  7. #7
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "Where I lay my head is home" :D
    Posts
    2,644
    sorry for the late response.
    i was busy preparing performance report of 2018Q1


    what i understand is coded below.

    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
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •