VBA copy and paste (Not web query) from open web page?

[ashleyw6]

Hi all,

What I am looking for is to be able to copy and paste data from an open web page into excel. Now the reason I am not using a web query is that I need to login to the page and navigate to the correct page before getting the data, the code I have got to do this is below, I have tried to run a web query using the querytable add function etc. after this code but it fails as the site keeps asking for login even with the IE window still open to the correct page and logged in... So any suggestions?

I have heard that there is a way to make the web query login but have tried everything that I could find with no success...

Here is the code I am using to login to the site:

Sub Test()

    Const cURL = "https://www.sharetrading.netwealth.com.au/LoginFailed.aspx"
    Const cUsername = "******"
    Const cPassword = "******"
    
    Dim IE As InternetExplorer
    Dim doc As HTMLDocument
    Dim LoginForm As HTMLFormElement
    Dim UserNameInputBox As HTMLInputElement
    Dim PasswordInputBox As HTMLInputElement
    Dim SignInButton As HTMLInputButtonElement
    Dim HTMLelement As IHTMLElement
    Dim qt As QueryTable
        
    Set IE = New InternetExplorer
    
    IE.Visible = True
    IE.Navigate cURL
    
    'Wait for initial page to load
    
    Do While IE.ReadyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop
    
    Set doc = IE.Document
    
    'Get the only form on the page
    
    Set LoginForm = doc.forms(0)
    
    'Get the User Name textbox and populate it
    '< input name="ctl00$ct$UserName" type="text" maxlength="30" id="ctl00_ct_UserName" style="width:160px;" />
 
    Set UserNameInputBox = LoginForm.elements("ctl00$LoginControl1$txtLogin")
    UserNameInputBox.Value = cUsername
    
    'Get the password textbox and populate it
    '< input name="ctl00$ct$Password" type="password" maxlength="30" id="ctl00_ct_Password" style="width:160px;" />

    Set PasswordInputBox = LoginForm.elements("ctl00$LoginControl1$txtPassword")
    PasswordInputBox.Value = cPassword
    
    'Get the form input button and click it
    '< input type="submit" name="ctl00$ct$uxBtnLogin" value="Sign In" o n c l i c k="javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions("ctl00$ct$uxBtnLogin", "", true, "Login", "", false, false))" id="ctl00_ct_uxBtnLogin" />
    
    Set SignInButton = LoginForm.elements("ctl00$LoginControl1$btnLogin$implementation$field")
    SignInButton.Click
            
    'Wait for the new page to load
    
    Do While IE.ReadyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop
    
    'Get the HTML document of the new page
    
    Set doc = IE.Document
    
    IE.Navigate "https://www.sharetrading.netwealth.com.au/Private/MarketPrices/CompanyProfile/Financials.aspx?stockCode=" & ActiveCell.Value
    
    Do While IE.ReadyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop
    
    End Sub

Thanks for any help!

Ash.

 

[excelsupernerd]

Once you've got the document loaded:

IE.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
IE.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT

' Now past into Excel with your fave method
' or one of the following
ActiveSheet.Paste
' Or
ActiveSheet.PasteSpecial Format:="HTML", Link:=False, _ 
    DisplayAsIcon:= False

Hope that helps! I'm currently researching how to copy a certain range of HTML. Involves the addrange() method of some DOM object in VBA (yikes!)

 

[excelsupernerd]

IE.ExecWB OLECMDID_COPY, 2

 

[ashleyw6]

THANKS!!!

This works I am also interested about copying a selection of the page not the whole page as this takes a long time to paste etc.

If you have any ideas please share

Thanks once again for your help!!!

Ash.

 

[Leopold Stotch]

Hi, I am also looking for help on this one and cannot seem to get anything to work, been at it for weeks!
- Trying to do a "select all" from webpage: https://www.barchart.com/stocks/quotes/GOOG/options?moneyness=allRows&view=sbs&expiration=2018-03-09
- paste into active excel sheet.
Here's what I'm working with but cannot get it to work , any help would be appreciated .

Sub CopyTables()


Dim ie As Object
Dim I As Long
I = 0
Set ie = CreateObject("InternetExplorer.Application")
ie.navigate "https://www.barchart.com/stocks/quotes/GOOG/options?moneyness=allRows&view=sbs&expiration=2018-02-23"
ie.Visible = True


Do While ie.Busy And Not ie.readyState = 4
DoEvents
Loop

DoEvents


ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
ie.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT


' Now past into Excel with your fave method
ActiveSheet.Paste



End Sub

Thank you!