downloading from a list of URLs.

X82 · Jul 10th 2017

Hi

I am after a file downloader to do a specific task using excel.

In short, we have Sharepoint. We can file our documents away to a Record Management Service, which is all fine. But trying to retrieve multiple files back once filed is an exercise in frustration, since every document has to be clicked in Sharepoint for it to then download from the RMS.

So if there is a library of 100 documents (all of which are filed to the RMS) and we need copies of these files for a USB stick or to put in another library, we have to download all 100 of them one at a time by clicking, waiting, saving and repeating.

The url for this function when we click is:

Sharepointsite.com/_layouts/RMS/Redirect.aspx?Action=GetFile&item=RMS://EDR1/Record[Extension=docx]/2403589&target=sharepointsite.com/Documents/Example.doc

In actuality, this is what happens when downloading directly from the RMS site

edr1.uk/RMS/browse/downloadContent.aspx?documentId=2403589&isRecord=true&launchMode=3&launchAs=0&OverrideLatestRecordVersion=true

You can see that the file id is key here. Both above URLS work on our system fine and both get the document in IE to display a open or save dialogue.

I can produce a spreadsheet with all these details In, I think. So there would be a list like

edr1.uk/RMS/browse/downloadContent.aspx?documentId=2266772&isRecord=true&launchMode=3&launchAs=0&OverrideLatestRecordVersion=true
edr1.uk/RMS/browse/downloadContent.aspx?documentId=2288772&isRecord=true&launchMode=3&launchAs=0&OverrideLatestRecordVersion=true
edr1.uk/RMS/browse/downloadContent.aspx?documentId=2288789&isRecord=true&launchMode=3&launchAs=0&OverrideLatestRecordVersion=true
edr1.uk/RMS/browse/downloadContent.aspx?documentId=2556472&isRecord=true&launchMode=3&launchAs=0&OverrideLatestRecordVersion=true

How can I get excel to take this list and download the files? If this even is possible?

I think an issue might be the fact that the URLs are not direct. They go through another process (forget the technical term). So it goes through an .aspx and then a ID to get the file. Unsure if excel vba can assist me here but I am hoping so.

So excel will try to get the document at each URL and save it to a pre-defined location.
I am unsure if there is a connection limit, so even limiting excel to downloading one at a time is still fine, since it saves the hassle of having to click each one.

This possible?

Logit · Jul 10th 2017

Re: downloading from a list of URLs.

.
This is partial included code from the project :

Code

'---------------------------------------------------
 'This module contains some auxiliary subs.




 'Written By:    Christos Samaras
 'Date:          02/11/2013
 'Last Update:   06/06/2015
 'E-mail:        [email protected]
 'Site:          http://www.myengineeringworld.net
 '---------------------------------------------------
    
Sub FolderSelection()
    
    'Shows the folder picker dialog in order the user to select the folder
    'in which the downloaded files will be saved.
    
    Dim FoldersPath     As String
    
    'Show the folder picker dialog.
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select a folder to save your files..."
        .Show
        If .SelectedItems.Count = 0 Then
            Sheets("Main").Range("B4") = "-"
            MsgBox "You did't select a folder!", vbExclamation, "Canceled"
            Exit Sub
        Else
            FoldersPath = .SelectedItems(1)
        End If
    End With
    
    'Pass the folder's path to the cell.
    Sheets("Main").Range("B4") = FoldersPath
    
End Sub




Sub Clear()
    
    'Clears the URLs, the result column and the folder's path.
            
    Dim LastRow     As Long
       
    'Find the last row.
     With Sheets("Main")
        .Activate
        LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
    End With
    
    'Clear the ranges.
    If LastRow > 7 Then
        With Sheets("Main")
            .Range("C8:D" & LastRow).ClearContents
            .Range("B4:D4").ClearContents
            .Range("B4").Select
        End With
    End If
    
End Sub

Display More

X82 · Jul 11th 2017

Re: downloading from a list of URLs.

Thanks for that. That actually works almost perfectly, however:

When downloading the file, it appears in the location but it doesn't have the original name.
For example the file:
edr1.uk/RMS/browse/downloadContent.aspx?documentId=2266772&isRecord=true&launchMode=3&launchAs=0&OverrideLatestRecordVersion=true
Actually should download "ExampleDocument.PDF"

Instead it downloads a document called:

downloadContent.aspx?documentId=2266772&isRecord=true&launchMode=3&launchAs=0&OverrideLatestRecordVersion=true

Changing this documents extension to PDF (or DOC or whatever the original was) works fine. But it needs to download the final file name.

If this isn't possible, I can produce a list of the original file names in a sheet. Maybe the download can use that to name them?
Also, thinking along the same lines, is there a way I can specify where to download based on a column?

In our sharepoint, 5000 files are organised by category. I can make a column of this category. So the end result would be:

It downloads a file, based on the category in a column in placed the file in a specific place and depending on the filename column, renames the downloaded file.

yiyawa7231 · Jun 11th 2021

Logit, thanks so much for this easy file. It worked exactly like it should!

Participate now!

Share