Select all and paste (VBA/webpage)

[PGD15]

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

 

[PGD15]

*bump* - need help

 

[John_w]

This can be done with the InternetExplorer ExecWb method. For example, see https://www.mrexcel.com/forum/excel...bsite-into-excel-post4931228.html#post4931228.

 

[PGD15]

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:

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

 

[John_w]

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.

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

 

[PGD15]

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.

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

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

 

[PGD15]

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

GOT it to work using:

 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!

 

[PGD15]

.... 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)

 

[John_w]

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:

    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

 

[PGD15]

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:

    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

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.