Trying to open webpage and save it as a pdf

[mario92]

I was wondering if anyone could help. I want to open a website and save the image that opens as a pdf. My code is below and when i save it to a pdf w/ current code, nothing shows up in the pdf file that is save. Its as if the file is corrupted or something. Anyone have any thoughts?


Sub CallWebPage()

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.AskToUpdateLinks = False

Dim newdate As Date
Dim StartTime As Date
Dim MinutesElapsed As Double
StartTime = Now()

Call makefolder(newdate)

Dim OpenPlace As String
Dim SavePlaceAlt As String
SavePlace = "T:\Daily Downloads\Fed Reserve\" & Format(newdate, "mm-dd-yyyy") & "\"
OpenPlace = "T:\Daily Downloads\Fed Reserve\" & Format(newdate, "mm-dd-yyyy") & "\"

Dim ManEmp As String
Dim Mort_30_yr As String

Oil_Price_Forcast = "https://www.dallasfed.org/research/...ch/surveys/des/2019/1904/des1904forecast1.png"



ActiveWorkbook.FollowHyperlink _
Address:=Oil_Price_Forcast, _
NewWindow:=True, _
AddHistory:=True
Application.WindowState = xlNormal


ActiveWorkbook.SaveAs filename:=SavePlace & "Oil Price Forcast " & Format(newdate, "mm-dd-yyyy") & ".pdf" ' , FileFormat:=51
ActiveWorkbook.Close




End Sub




Sub makefolder(newdate)

Dim filename As String
Dim zfilename As String


If Weekday(Now(), vbMonday) = 1 Then
newdate = Now() - 3
Else
newdate = Now() - 1
End If

filename = "T:\Daily Downloads\Fed Reserve\" & Format(newdate, "mm-dd-yyyy")
zfilename = Dir(filename, vbDirectory)

If zfilename = "" Then
MkDir ("T:\Daily Downloads\Fed Reserve\") & Format(newdate, "mm-dd-yyyy")

Else
Exit Sub
End If

End Sub

 

[mario92]

Hi Anthony,
Hope you are doing well during this crisis. Since you are the only one that helped me I was wondering if you could shed some light on this particular problem. With the help of your code (and using the new internetexplorermedium) i think i can finally pull some info off a website. Unfortunately a new problem arose. When i go to the below site in chrome it opens fine and i can see everything. when i open it in IE, the layout changes and i cant pull the info that i want ( Im trying to pull the holdings data, which is that table). Have you ever run into this, if so, any advice?

EASI Holdings - Amplify ETFs

amplifyetfs.com

 

[Anthony47]

Hi Mario, we are having dificult days, but we think we have difficult months ahead.. And I am afraid that the crisis is underevaluated in too many Countries.

When we talk about exstracting Tables from a web site then I always suggest the following macro, that is usable on any site that show their data in Tables (the html concept o Table):

'   >>> STRICTLY ON TOP OF A STANDARD VBA MODULE <<<

#If VBA7 Then
    Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
#Else
    Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

Sub GetWebTables()
Dim IE As Object, IESh As Worksheet, FlEx As Boolean, myTim As Single
Dim I As Long, KK As Long, aColl As Object, myUrl As String, OCLen As Long
Dim myItm As Object, tRtR As Object, TdTd As Object, J As Long

'BEWARE that the Sheet you are going to specify will be CLEARED at the beginning
Set IESh = Sheets("ImportedTables")                         '<<< The worksheet to be used for the imported data
myUrl = "https://amplifyetfs.com/easi-holdings.html"       '<<< The URL
'
If IE Is Nothing Then Set IE = CreateObject("InternetExplorer.Application")
With IE
    .Visible = True         '...
    .Navigate myUrl         '...
    Sleep 100
    Do While .Busy: DoEvents: Sleep (20): Loop  'Attesa not busy
    Do While .ReadyState <> 4: DoEvents: Sleep (20): Loop 'Attesa document
End With
'
I = 0: KK = 1
IESh.Range("A:Z").ClearContents
IESh.Range("A:Z").NumberFormat = "@"
myTim = Timer
'Wait for all Tables to assemble
Do
    Set aColl = IE.document.getElementsbyTagName("TABLE")
    If aColl.Length > OCLen Then
        OCLen = aColl.Length
        FlEx = False
        myTim = Timer
    Else
        If FlEx And aColl.Length > 0 Then Exit Do           'Tables stable
        FlEx = True
    End If
    Debug.Print aColl.Length, Timer - myTim
    If Timer > (myTim + 5) Or Timer < myTim Then Exit Do    'or Timeout
    Sleep 500
    DoEvents
Loop
For Each myItm In aColl
'    If ... Then                        'Spare
        IESh.Cells(I + 1, "A").Value = "TABLE#_" & KK: KK = KK + 1: I = I + 1
        For Each tRtR In myItm.Rows
            For Each TdTd In tRtR.Cells
                IESh.Cells(I + 1, J + 1) = TdTd.innertext
                J = J + 1
            Next TdTd
            I = I + 1: J = 0
        Next tRtR
'    End If
    I = I + 1
Next myItm
'
On Error Resume Next
IE.Quit
Set IE = Nothing
MsgBox (KK - 1 & " Tables have been imported")
End Sub

There are two lines to customize; they are marked <<<, and one is of course the page Url

It might ignore tables shown in any "iframe"

Bye

 

[Anthony47]

Ehmmm.... I realize that I already suggested you this solution...
Did you try on amplifyetfs.com/easi-holdings.html ??

 

[mario92]

it does run but i only get this. I think its b/c the website doesnt fully open up in IE. Are you able to run it and get all the data?

TABLE#_1
Name	Ticker	CUSIP	Shares	Market Value	% Market Value
TABLE#_2

 

[Anthony47]

Yes, I get this:



Well, having in mind your InternetExplorerMedium trick I modified my Sub GetWebTables to exploit IEMedium; modifications include:
-adding reference to MSHTML and InternetControls vba libraries
-modifying IE declaration and setting

So the first lines of the sub become:

Sub GetWebTables_EB()
'Requires reference to MSHTML and InternetControls vba libraries, early bind version
'
Dim IE As InternetExplorerMedium, IESh As Worksheet, FlEx As Boolean, myTim As Single
Dim I As Long, KK As Long, aColl As Object, myUrl As String, OCLen As Long
Dim myItm As Object, tRtR As Object, TdTd As Object, J As Long

'BEWARE that the Sheet you are going to specify will be CLEARED at the beginning
Set IESh = Sheets("ImportedTables")                         '<<< The worksheet to be used for the imported data
myUrl = "https://amplifyetfs.com/easi-holdings.html"       '<<< The URL
'
Set IE = New InternetExplorerMedium
With IE
    .Visible = True         '...
' etc etc

Remember to set in vba those two references

But .... SURPRISE!
When I ran this version I get the infamous Run time Error 462!

If I leave the reference two the libraries and use "early bindig" but IE declared InternetExplorer and set equal to New InternetExplorer then everything run smoothly to the end and the two tables are imported

Try on your computer weather using the modified Sub GetWebTables_EB you get the tables or the error…


While looking for suggestions on this error I arrived here: Automation error when getting ReadyState of InternetExplorer object
There, Elizabeth Amato replied:

The problem seems to be caused by Internet Explorer security settings - when switching between security zones, the current instance of IE is killed and a new instance is created, so your reference to the old process is no longer valid.

Some of the suggested solutions were:
1.Change IE security settings. Uncheck "enable protected mode" on the Security tab of Internet Options.
2.Navigate to the IP address directly instead of the URL. This is the one that fixed it for me. For example, ie.navigate "64.233.177.106" (Google's IP address)
3.Set ie = New InternetExplorerMedium instead of New InternetExplorer. Or in your case, vice versa

I tried "solution 1", i.e. remove security check, and the error went away. But I don't think it’s a good idea lowering the security check
As I said before, also solution 3 worked for me, i.e. passing from InternetExplorerMedium to InternetExplorer.

I use IE version 11, and you?

Bye

 

[mario92]

This is what i see when i open in ie. But its totally different when i use chrome. Since the page doesnt show the holdings i can't pull them. Its something to do with my IE im guessing.
you ever come across this

 

[Anthony47]

We will not fix this, and I.E. is obsolete and many sites no longer support it.
Did you, anyway, tryed by changing the security setting, as written in the previous message? And, by the way, which IE version do you use?

Bye

 

[mario92]

Hi Anthony,

Just a heads up, i was able to get the macro to work. It was the issue with IE not opening correctly. I just had to restore defalut setting in IE and it worked perfectly. Sometimes its just the small things

 

[Anthony47]

Good to know the trick
Bye

 

[mario92]

Hi Anthony,

I was wondering if you ever came across this. That code you gave me worked a few times and for some unknown reason now i got the "can't find object or library" error msg. I did some research and it looks like i need to check a reference but when i try to do that nothing ever apprears when i goto tools > references. Also, all the code is now gone so i cant export it to another sheet.

Any thoughts?