Select all and paste (VBA/webpage)

P

PGD15

Board Regular
Joined
Aug 5, 2016
Messages
137
Hi guys,

Got a VBA code to log me into a website and navigate to a certain webpage. now i need some code to select all information on the webpage and paste this into cell a1 on the tab "sheet1".

Does anyone know how to do this? I've tried a couple of things but so far no luck :(
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
Hi John,

Thanks for the link, I can't quite get mine to work... it get stuck in a loop and I cannot solve it :'( my code is:

Code: 
Sub Button()

Dim IE As Object
   Dim objElement As Object
    Dim objCollection As Object
    
    Set IE = CreateObject("InternetExplorer.application")
    With IE
        .Visible = True
        .navigate ("URL1")
        While .Busy Or .readyState <> 4: DoEvents: Wend
    '.Document.getElementsbyname("User name").Focus
    .Document.getElementsbyname("username")(0).Value = "username"
    .Document.getElementsbyname("password")(0).Value = "Pword"
    
 Set objCollection = IE.Document.getElementsByTagName("input")
 
    i = 0
    While i < objCollection.Length
               If objCollection(i).Type = "submit" And _
               objCollection(i).Name = "" Then
                 ' "Search" button is found
                Set objElement = objCollection(i)
                
        End If
        i = i + 1
    Wend
         
   .navigate ("URL2")
  objElement.Click
        While .Busy Or .readyState <> 4: DoEvents: Wend
        Debug.Print .LocationURL
[COLOR=#b22222]               While .Busy Or .readyState <> READYSTATE_COMPLETE: DoEvents: Wend[/COLOR]




        .ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
          Application.Wait DateAdd("s", 1, Now)
        .ExecWB OLECMDID_COPY, OLECMDEXECOPT_DONTPROMPTUSER
        .ExecWB OLECMDID_CLEARSELECTION, OLECMDEXECOPT_DODEFAULT
    End With
    
    With Sheet1
        .Activate
        Sheets("Sheet1").Range("A1").Select
        .xlPasteValues
        Sheets("Sheet1").Range("A1").Select
    End With
       
End Sub

The line in red is the one that loops (also not too sure if my worksheet and paste special will work either (bit of a newbee) when it comes to VBA

I also tried the code on a simple webpage but the .execwb select all part gets an error too
 
Last edited:
Upvote 0
The line in red is before the ExecWB code, so the ExecWB code can't be the cause of the looping.

You might need a Busy/readyState wait after the URL2 navigate and after the objElement.click.

This ExecWB code, copied from the link I provided, and using this forum as the URL, works for me. You must set a reference to Microsoft Internet Controls in Tools->References.
Code: 
Public Sub IE_Copy_Page()
    Dim IE As InternetExplorer
    Dim URL As String
    Dim destSheet As Worksheet
    
    Set destSheet = ActiveSheet
    
    URL = "https://www.mrexcel.com/forum/excel-questions"
    
    Set IE = New InternetExplorer
    With IE
        .Visible = True
        .navigate URL
        While .Busy Or .readyState <> READYSTATE_COMPLETE: DoEvents: Wend

        .ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
        .ExecWB OLECMDID_COPY, OLECMDEXECOPT_DONTPROMPTUSER
        .ExecWB OLECMDID_CLEARSELECTION, OLECMDEXECOPT_DODEFAULT
    End With
    
    With destSheet
        .Activate
        .Range("A1").Select
        .Paste
        .Range("A1").Select
    End With
       
End Sub
 
Upvote 0
John_w said:
The line in red is before the ExecWB code, so the ExecWB code can't be the cause of the looping.

You might need a Busy/readyState wait after the URL2 navigate and after the objElement.click.

This ExecWB code, copied from the link I provided, and using this forum as the URL, works for me. You must set a reference to Microsoft Internet Controls in Tools->References.
Code: 
Public Sub IE_Copy_Page()
    Dim IE As InternetExplorer
    Dim URL As String
    Dim destSheet As Worksheet
    
    Set destSheet = ActiveSheet
    
    URL = "https://www.mrexcel.com/forum/excel-questions"
    
    Set IE = New InternetExplorer
    With IE
        .Visible = True
        .navigate URL
        While .Busy Or .readyState <> READYSTATE_COMPLETE: DoEvents: Wend

        .ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
        .ExecWB OLECMDID_COPY, OLECMDEXECOPT_DONTPROMPTUSER
        .ExecWB OLECMDID_CLEARSELECTION, OLECMDEXECOPT_DODEFAULT
    End With
    
    With destSheet
        .Activate
        .Range("A1").Select
        .Paste
        .Range("A1").Select
    End With
       
End Sub
Click to expand...

In response to the bits in red, 1) don't I have a ready state wait whilst loading built it before and after the second URL?
and 2) how do I do this? I can get to tools but my references option won't allow me to select/access this option.

Sorry if that was a dumb question i've not got much knowledge with vba

edit: got the second part to work, not sure what i was doing but it works now! but still unsure about the 1st part
 
Last edited:
Upvote 0
PGD15 said:
In response to the bits in red, 1) don't I have a ready state wait whilst loading built it before and after the second URL?
and 2) how do I do this? I can get to tools but my references option won't allow me to select/access this option.

Sorry if that was a dumb question i've not got much knowledge with vba

edit: got the second part to work, not sure what i was doing but it works now! but still unsure about the 1st part
Click to expand...

GOT it to work using:
Code: 
 Sub test()

Dim IE As Object
   Dim objElement As Object
    Dim objCollection As Object
    Set destsheet = Worksheets("Sheet1")
    Set IE = CreateObject("InternetExplorer.application")
    With IE
        .Visible = True
        .navigate ("http1/")
        While .Busy Or .ReadyState <> 4: DoEvents: Wend
    '.Document.getElementsbyname("User name").Focus
    .Document.getElementsbyname("username")(0).Value = "username"
    .Document.getElementsbyname("password")(0).Value = "Pword"
   While .Busy Or .ReadyState <> 4: DoEvents: Wend
 Set objCollection = IE.Document.getElementsByTagName("input")
 
    i = 0
    While i < objCollection.Length
               If objCollection(i).Type = "submit" And _
               objCollection(i).Name = "" Then
                 ' "Search" button is found
                Set objElement = objCollection(i)
                
        End If
        i = i + 1
    Wend
 
   .navigate ("URL2")
       objElement.Click
        While .Busy Or .ReadyState <> 4: DoEvents: Wend
        Debug.Print .LocationURL
        
    End With
 IE.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
        IE.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DONTPROMPTUSER
        IE.ExecWB OLECMDID_CLEARSELECTION, OLECMDEXECOPT_DODEFAULT
    


    With destsheet
        .Activate
        .Range("A1").Select
        .Paste
        .Range("A1").Select
    End With
End Sub

Though if i try change anything i get a couple of errors ... but this works!! thanks for the help!
 
Upvote 0
.... i got a couple more questions.

After pasting the information into excel I need to sumif range g31:g41 if its equal to "XXX" or "yyy" i know how to do this with formula but would like this in the macro (as i have to repeat this for other information)

1) I dont really know how to write formula in VBA i know its something like

.Formula = "=(sumif("a31:a41","value1","G31:G41")+Sumif("A31:A41","value2","G31:G41")/1000)"

but this doesn't work, and i don't know how to paste this value into a different cell as a value (xlpastespecial.value) and get it to work.

I know how to say worksheets("sheet1").select = Worksheet paste etc. etc....
but not sure how to make the sumif bit paste into where i want it too.

Where i want it to go is based on the below. .....

2) i need the value from the formula to be pasted in column D but the value must paste in the row where the date is equal to todays date (which will be column b) so like if =today() = B:B xlpastespecial(d:d)


and im pretttyyy confused (i will literally love you if you can help me with this! <3)
 
Last edited:
Upvote 0
Writing a formula in VBA is basically the same as entering a cell formula. Because the VBA .Formula property is a string and a string is enclosed by quotes, each quote within the formula must be changed to two quotes, and VBA interprets each pair as one quote.

Paste special as value isn't needed because you can just assign the Value of one cell to another cell's Value property.

To find today's date in column B we can use Application.Match to find the row number and use the result to put the formula value in the same row in column D.

The following code handles all these requirements:

Code: 
    Dim dateRow As Variant
    
    With ActiveSheet
        .Range("K24").Formula = "=SUMIF(A31:A41,""XXX"",G31:G41)+SUMIF(A31:A41,""YYY"",G31:G41)"
        dateRow = Application.Match(CLng(Date), .Columns("B"), 0)
        If Not IsError(dateRow) Then
            .Cells(dateRow, "D").Value = .Range("K24").Value
        Else
            MsgBox "Today's date not found in column B"
        End If
    End With
 
Upvote 0
John_w said:
Writing a formula in VBA is basically the same as entering a cell formula. Because the VBA .Formula property is a string and a string is enclosed by quotes, each quote within the formula must be changed to two quotes, and VBA interprets each pair as one quote.

Paste special as value isn't needed because you can just assign the Value of one cell to another cell's Value property.

To find today's date in column B we can use Application.Match to find the row number and use the result to put the formula value in the same row in column D.

The following code handles all these requirements:

Code: 
    Dim dateRow As Variant
    
    With ActiveSheet
        .Range("K24").Formula = "=SUMIF(A31:A41,""XXX"",G31:G41)+SUMIF(A31:A41,""YYY"",G31:G41)"
        dateRow = Application.Match(CLng(Date), .Columns("B"), 0)
        If Not IsError(dateRow) Then
            .Cells(dateRow, "D").Value = .Range("K24").Value
        Else
            MsgBox "Today's date not found in column B"
        End If
    End With
Click to expand...

Hi, thanks for the code, i've tried to edit this but to no avail (im a bit of a dumbo with vba).

so I need "sheet1" to to the .formula sumif etc. then this value to be pasted into worksheet("daily sales") where column d is equal to =today() (this date has a cell reference in another tab if cannot put =today()

I cant quite figure out how to get the formula to point at the right worksheets and cell references.

So summary. sumif Sheet1 a31:a41 = value1 etc. then paste this value into worksheet("Daily sales") column b if this is equal to today (this information would be in column b) (cell reference is Worksheet("report").Range("J1").

edit: not sure if the code already does this or not, but i the target cell in column d is already populated would this override the existing data? if not can this be added in as this will allow me to update my report based on live data instead of once a day.

I am in debt to you. :)
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

Anigito
  • Question Question
Get Webpage Title Text by URL Generated in MS Excel
Replies
1
Views
1K
Anigito
Anigito
F
  • Solved
Webscraping a site with no element ids and returning data
Replies
6
Views
2K
fizzydrink
F
L
  • Solved
copy cell and cell above onto different sheet, under the next available line.
Replies
5
Views
127
Alex Blakenburg
A
M
  • Question Question
Open webpage, copy all and paste in Excel
Replies
1
Views
2K
Billy Hill
B
J
  • Question Question
Power Query editor OR VBA to extract data from Grants.gov
Replies
3
Views
294
Jagadeshrao
J

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

Share this page

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
Back
Top