Excel VBA Open Hyperlinks in a loop and copy paste download link in a sheet

0 votes

I want to click on a number of sites to see if there is a PDF file available. If so, an Excel spreadsheet should be created and saved with the link to this file.

I attempted to make a code out of the content already present on this website. I'm having trouble understanding how to precisely copy the URL from the HTML code, though.

Sub CopyDownloadLink()

Dim ie As Object
Dim html As HTMLDocument
Dim codeLine As String
Dim startPos As Long
Dim stcheck As String
Dim ws As Worksheet

   Dim xmlHttp As Object
   Dim hyp As Hyperlink
   Dim mes As String
   
Set ws = ThisWorkbook.Sheets("Tabelle5")
Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True

For Each hyp In ws.Hyperlinks

 ie.navigate hyp.Address
 stcheck = "more download-link"

 While ie.Busy Or ie.readyState < 4: DoEvents: Wend
 
 Application.Wait Now + TimeSerial(0, 0, 5)
    
 mes = ie.document.body.innerHTML

 startPos = InStr(mes, stcheck)
 If startPos = 0 Then

     ThisWorkbook.Worksheets("Tabelle5").Cells(hyp.Range.Row, 4).Value = "Not Found"
 Else
     codeLine = "Copy URL to download file"  
     ws.Cells(hyp.Range.Row, 4) = codeLine
 End If


Next hyp

ie.Quit
Set ie = Nothing

End Sub
Dec 25, 2022 in Others by Kithuzzz
• 38,020 points
1,309 views

1 answer to this question.

0 votes
Refer this tutorial for your solution: https://evermap.com/Tutorial_ABM_FindingLinkErrors.asp
answered Dec 25, 2022 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer

Excel VBA- How to loop through specific sheets in a workbook and format the same ranges in each sheet

Range(...) instructs VBA to always use the ...READ MORE

answered Mar 21, 2023 in Others by Kithuzzz
• 38,020 points
1,652 views
0 votes
1 answer

Excel VBA if file closed, then open and paste, else just paste data

Slightly re-worked to add full workbook/sheet qualifiers ...READ MORE

answered Sep 21, 2022 in Others by narikkadan
• 63,720 points
905 views
0 votes
1 answer

Output python dataframe to excel and create a new data_validation column in the exported excel sheet

Use pandas.ExcelWriter with worksheet.data_validation from xlswriter : df["code"] = None items = list(range(1,10)) max_row, max_col = ...READ MORE

answered Jan 24, 2023 in Others by narikkadan
• 63,720 points
1,284 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
1,099 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,523 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
824 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
983 views
0 votes
1 answer

Converting Textboxes Link and/or Formula to Values in a Copied Sheet using Excel VBA

Copy the values using Range and Value2 With ActiveSheet.UsedRange ...READ MORE

answered Jan 31, 2023 in Others by narikkadan
• 63,720 points
552 views
0 votes
1 answer

I want to make Excel read a value in Calc and copy it to my sheet in Excel

Here is the sample code that will allow ...READ MORE

answered Oct 27, 2022 in Others by narikkadan
• 63,720 points
476 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP