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

ashleyw6

New Member
Joined
Mar 7, 2011
Messages
6
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:

Code:
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.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

excelsupernerd

New Member
Joined
Jul 28, 2010
Messages
14
Once you've got the document loaded:
Code:
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!)
 

ashleyw6

New Member
Joined
Mar 7, 2011
Messages
6
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

New Member
Joined
Feb 5, 2018
Messages
2
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!
 

Forum statistics

Threads
1,141,847
Messages
5,708,950
Members
421,601
Latest member
Garlo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top