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
• 28,900 points
109 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
• 53,520 points

Related Questions In Others

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
• 53,520 points
170 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 in Others by narikkadan
• 53,520 points
47 views
0 votes
1 answer

Sum the total of a column in excel and paste the sum to a different workbook

Get Column Sum From Closed Workbook Option Explicit Sub ...READ MORE

answered Jan 26 in Others by narikkadan
• 53,520 points
46 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
533 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
2,415 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
• 22,960 points
117 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,630 points
300 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 in Others by narikkadan
• 53,520 points
47 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
• 53,520 points
79 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