Adding a Hyperlink, but changing the name of the downloaded file.

[karlwarnkester]

Hello! Another one I'm having trouble with.

In HTML you can add download=changedname.xyz to an <a> tag.
and the file will download as the changed name.

Goal: I'd like to append some info within the spreadsheet to the filename from a URL.

To add the hyperlink to a column I'm using .Hyperlink Add, but it doesn't seem to have that functionality to then modify the name once the link is clicked (downloaded).

Any thoughts on tools that can be used?

Col A is order of view (1st to 10th)
Col B is a hyperlink with the TextToDisplay as image, audio, or video that links to the downloadable file (ex: Bobsart.xyz)

Goal: When I click on the image hyperlink it downloads as 1stBobsart.xyz

Thank you!

[Leith Ross]

Hello karlwarnkester,

You can change the file name and location of the downloaded file using a hyperlink but is not as easy as in HTML. On your worksheet you will need two cells. The first will have the hyperlink to the file to be downloaded. This can be any file type. The second cell will have the new file name and extension. You can include the drive letter in the path also in the new file name.

Next, the hyperlink as to be captured using the "Worksheet_FollowHyperlink" event. Once the link is made, control is returned by to the VBA macro. The macro will then download the file from the URL and save it to the directory in the new file name. I have attached a workbook that is setup to do this.

Macro to Download File from Website

Please Login or Register  to view this content.

Worksheet Event Code

Please Login or Register  to view this content.

[karlwarnkester]

Wow! Thanks for all of that work! You certainly are a very skilled and knowledgeable programmer.

It will take me some time to go through and learn all of this, I like to know what each thing is doing and I'm new to all of this. Thanks so much, I'll dive in.

[Leith Ross]

Hello karlwarnkester,

Did it work the way you wanted?

[karlwarnkester]

Still figuring it out. Looking at my workflow and how your code fits with that.

It might get complicated.

I received an excel file with the data already in the tables. Column J has the html link already there.
Currently I have another macro so I can Select the cells and Open the links, instead of clicking on them individually. Column H has the review order (1st-10th) and that is the string I want to append to the file.

Your code might just do the trick, I just have to work on it a bit more to see.

[karlwarnkester]

Basically I have an excel sheet that acts as a program (I call command center), I import the other data from another excel spreadsheet, then format the results, add clickable URL to rows, etc.

A lot of your code is things I have to lookup and/or investigate what's going where and how

[karlwarnkester]

What if I wanted a duplicate file download to occur? EXAMPLE: If you run the test twice, it does not create a file Excel Forum(1).ico or whatever Windows does for redundant filenames. I tried to remove the "Remove any old copies of the file." Part But it didn't do the trick.

Why would I want to do that (you might ask)?
I don't have control of the incoming data and someone could put in two 1st with the same file name. In this case I would like them to both show up in the downloads folder.

Overall I'm still fighting the battle to get it to work for me.

[karlwarnkester]

Not quite working yet.

Please Login or Register  to view this content.

[Leith Ross]

Hello karlwarnkester,

This code is amended to look for a duplicate file and then append a sequence number (1) to the name.

Please Login or Register  to view this content.