VBA Download URL and rename file

[Dippy001]

Hi All

I was wondering if I could get some help with a problem I am stuck on

I wanted some support on a way from a drop down list to select a drop down and it goes to a specific link based on the drop down name

e.g

Select Test 1 and run the macro which then will go on a specific URL and download the file and save the file to the workbook location and rename the file to Downloaded file

Please note that when URL is clicked it auto saves it into download folder which is named like this from any download link - Data Capture - 2024-10-022232132

The bold font always changes once it is downloaded so I would need the latest file from the download directory to be moved into the work book folder and renamed

Hope someone can help and look forward to your support - Thank you

 

[Jeffrey Mahoney]

Please give this a try.

On sheet1 I created a pulldown that references the named range URLList. I named cell B2 URLSelect. I added a URL List with links on the SETUP sheet. The named range for B2:B4 is called URLList. The named range for C2:C4 is called URLNames (not really important to have a name for these).

The code below gets triggered when a user selects a different item in the list. It goes and finds the chosen item from the URLList and returns the link next to it and then opens that as a hyperlink. The code needs to be placed in the SHEET module you have the pulldown on. Mine is Sheet1

Book2
	A	B	C
1
2		URL3
3
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
B2	List	=URLList

Book2
	B	C
1	URL List	Link
2	URL1	https://www.mrexcel.com/board/threads/vba-download-url-and-rename-file.1265122/
3	URL2	https://www.mrexcel.com/board/
4	URL3	https://www.mrexcel.com/board/forums/lounge-v-2-0.23/
SETUP

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim URL As String
  Dim Lnk As String
  Dim Cel As Range
  
  If Not Intersect(Target, Range("URLSelect")) Is Nothing Then
    URL = Range("URLSelect").Value
    For Each Cel In Sheets("SETUP").Range("URLList")
      If URL = Cel.Value Then
        Lnk = Cel.Offset(0, 1).Value
        ThisWorkbook.FollowHyperlink Lnk
         Exit For
      End If
    Next Cel
  End If
  
      
End Sub

 

[Dippy001]

Hello Jeffrey

Thank you so so much that works wonders - Is there anyway to get the download file to be moved into a specific users desktop and be renamed?

 

[Dippy001]

Please give this a try.

On sheet1 I created a pulldown that references the named range URLList. I named cell B2 URLSelect. I added a URL List with links on the SETUP sheet. The named range for B2:B4 is called URLList. The named range for C2:C4 is called URLNames (not really important to have a name for these).

The code below gets triggered when a user selects a different item in the list. It goes and finds the chosen item from the URLList and returns the link next to it and then opens that as a hyperlink. The code needs to be placed in the SHEET module you have the pulldown on. Mine is Sheet1

Book2
	A	B	C
1
2		URL3
3
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
B2	List	=URLList

Book2
	B	C
1	URL List	Link
2	URL1	https://www.mrexcel.com/board/threads/vba-download-url-and-rename-file.1265122/
3	URL2	https://www.mrexcel.com/board/
4	URL3	https://www.mrexcel.com/board/forums/lounge-v-2-0.23/
SETUP

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim URL As String
  Dim Lnk As String
  Dim Cel As Range
 
  If Not Intersect(Target, Range("URLSelect")) Is Nothing Then
    URL = Range("URLSelect").Value
    For Each Cel In Sheets("SETUP").Range("URLList")
      If URL = Cel.Value Then
        Lnk = Cel.Offset(0, 1).Value
        ThisWorkbook.FollowHyperlink Lnk
         Exit For
      End If
    Next Cel
  End If
 
     
End Sub

Hello Jeffrey

Thank you so so much that works wonders - Is there anyway to get the download file to be moved into a specific users desktop and be renamed?

 

[Jeffrey Mahoney]

Are those URLs that you are providing from a LAN or WAN folder and file name like?:
P:\Projects\Project BB1\Workbook22.xlsm

Or are they HTTP links to outside websites?

 

[Dippy001]

Are those URLs that you are providing from a LAN or WAN folder and file name like?:
P:\Projects\Project BB1\Workbook22.xlsm

Or are they HTTP links to outside websites?

Heyy

The link is it a http site so when I select the link edge opens and then downloads the file into downloads however the link does not change but the file name does

The unique part about the file is the start of the name which is - Data Capture - 2024-10-022232132
the bold font changes however.

I wanted the file to be moved to my desktop once downloaded and the name changed of the file.

 

[Jeffrey Mahoney]

So, when the file gets downloaded, where does it originally get saved? Or does the file just open in the native application and you save it?

What you're asking for is code to keep checking a certain folder for new files that meet that naming criteria with the latest date/time, then copy them to your desktop and rename them at the same time. Maybe we just have a macro that you run manually when you know a file has been downloaded.

I would need more information. Folder names, incoming file name criteria, new file name criteria.