CODEWITHSUNDEEP
excel
wierts
wierts

Reputation: 101

create a list of documents in excel

I am managing a project where a large number of documents have to accompany the final product. Think of manuals to certain off-the-shelf products that are in the product (a boat).

I thought it would be handy to have a list in excel with the documents that have to be shipped with the final product and than a (hyperlink) to the document in question. All these documenters (mostly pdfs) are located in the same directory.

Any suggestions on how to achieve this?

Thanks in advance

Upvotes: 0

Views: 93

Answers (1)

MMerry
MMerry

Reputation: 334

Sounds like you will need to loop through a directory of files and generate a hyperlink next to the file names.

I'd recommend using the fileDialog method to select the files and fill a collection or array and then export each row to a row worksheet.

sub writeFilesToWorksheet()
Dim lngCount As Long
Dim ws       As worksheet 

'Set up our new sheet'
set ws = worksheets.add

'Add some headers'
ws.cells(1,1).value = "File Name"
ws.cells(1,2).value = "Hyperlink"

'Select files'
With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = True
        .Show

        'Add the selected files to the sheet'
        For lngCount = 1 To .SelectedItems.Count
            ws.cells (lngCount + 1, 1).value = .SelectedItems(lngCount)
            ws.cells (lngCount + 1, 2).formula = "=HYPERLINK(" & .SelectedItems(lngCount) & ")"
        Next lngCount
end with

ws.activate
set ws = Nothing

end sub

If you want to use the friendly names parameter for the hyperlink you could include something like

Dim pos as integer
Dim strFriendlyName as string

pos = InStrRev(.selectedItems(lngCount), "\")
strFriendlyName = Right$(.selectedItems(lngCount), Len(.selectedItems(lngCount)) - pos)

and update the second part of the for loop to:

ws.cells (lngCount + 1, 2).formula = "=HYPERLINK(" & .SelectedItems(lngCount) & ", " & """" & strFriendlyName & """" & ")"

Upvotes: 1

Related Questions