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

 

[John_w]

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.

Simply delete the With... End With lines and qualify the range (.Range, .Cells and .Columns) references with the specific sheet names (instead of ActiveSheet). The code uses the VBA Date function for today's date, so it doesn't need to reference a cell containing today's date. Yes, the column D cell is overwritten. The code puts the formula in Sheet1!K24, but you can use any suitable cell - just change the two "K24" cells in the code as required.

    Dim dateRow As Variant
    
    Worksheets("Sheet1").Range("K24").Formula = "=SUMIF(A31:A41,""XXX"",G31:G41)+SUMIF(A31:A41,""YYY"",G31:G41)"
    
    dateRow = Application.Match(CLng(Date), Worksheets("Daily Sales").Columns("B"), 0)
    If Not IsError(dateRow) Then
        Worksheets("Daily Sales").Cells(dateRow, "D").Value = Worksheets("Sheet1").Range("K24").Value
    Else
        MsgBox "Today's date not found in column B of Daily Sales sheet"
    End If

 

[PGD15]

Simply delete the With... End With lines and qualify the range (.Range, .Cells and .Columns) references with the specific sheet names (instead of ActiveSheet). The code uses the VBA Date function for today's date, so it doesn't need to reference a cell containing today's date. Yes, the column D cell is overwritten. The code puts the formula in Sheet1!K24, but you can use any suitable cell - just change the two "K24" cells in the code as required.

    Dim dateRow As Variant
    
    Worksheets("Sheet1").Range("K24").Formula = "=SUMIF(A31:A41,""XXX"",G31:G41)+SUMIF(A31:A41,""YYY"",G31:G41)"
    
    dateRow = Application.Match(CLng(Date), Worksheets("Daily Sales").Columns("B"), 0)
    If Not IsError(dateRow) Then
        Worksheets("Daily Sales").Cells(dateRow, "D").Value = Worksheets("Sheet1").Range("K24").Value
    Else
        MsgBox "Today's date not found in column B of Daily Sales sheet"
    End If

This works brilliant thanks!

... though is it possible when pasting the information from the internet into "sheet1" to create a new tab and paste into this new tab. then after i've applied the formula delete the tab? (i've got to edit this a lot to include a load of information from different webpages and unforatently this merges some cells so I can run it once but then have to unmerge cells to run again (think creating a new tab and discarding this after would be best option to resolve my issue)

2) on one webpage I have a drop down box where i need to change the date in that box to the 1st of the current month, I'm not sure how to edit this in VBA to change the dropdown data entry is there any code for this?

This is my last Legit request (promise to leave u alone )

 

[John_w]

create a new tab and paste into this new tab. then after i've applied the formula delete the tab?

Add and delete a sheet with the macro recorder and see if you can incorporate the generated code into your existing code. The added sheet becomes the active sheet.

2) on one webpage I have a drop down box where i need to change the date in that box to the 1st of the current month, I'm not sure how to edit this in VBA to change the dropdown data entry is there any code for this?

Can be done with IE automation. If it's a conventional dropdown search this forum for HTMLDocument HTMLSelectElement and there should be example code which you'll need to adapt for your specific web page.

 

[PGD15]

Add and delete a sheet with the macro recorder and see if you can incorporate the generated code into your existing code. The added sheet becomes the active sheet.

got the code for adding a deleting a sheet to work! thanks for that, will play about with the HTML drop down etc next week (lost the will lol) thanks for the help much appreciated.

Have a good weekend!

 

[PGD15]

Hi Again,

In regards to the drop down box value change I can input a value. I want to input a Value from cell A1 (sheet1) into where the placeholder value is in the below HTML code. I've tried playing around with this but cannot get the value into the drop down box. I've used get element by name "From_Date". but not sure how to copy and paste/input value into webpage from excel when the value will change. Can you please help me out (also got a button which i need to click after inputting the value but i think i can get that to work will play around with that currently)

additionaly this field will always be populated (so i don't know if it does) but the input value will need to override the existing value

Class = "input" 
Placeholder="16-06-18"
Name = "From_Date"
Value="16-06-2018"

 

[PGD15]

Hi Again,

(also got a button which i need to click after inputting the value but i think i can get that to work will play around with that currently)

additionaly this field will always be populated (so i don't know if it does) but the input value will need to override the existing value

Class = "input" 
Placeholder="16-06-18"
Name = "From_Date"
Value="16-06-2018"

Turns out no i cannot get the search button to work :')

the HTML code around the button is < button class="btn btn-primary" > search < / button > - literally the only line relating to the button rest all are formatting rows etc
no name no ID no tag etc. so not sure how to get this to work.

 

[PGD15]

Hi Again,

In regards to the drop down box value change I can input a value. I want to input a Value from cell A1 (sheet1) into where the placeholder value is in the below HTML code. I've tried playing around with this but cannot get the value into the drop down box. I've used get element by name "From_Date". but not sure how to copy and paste/input value into webpage from excel when the value will change. Can you please help me out (also got a button which i need to click after inputting the value but i think i can get that to work will play around with that currently)

additionaly this field will always be populated (so i don't know if it does) but the input value will need to override the existing value

Class = "input" 
Placeholder="16-06-18"
Name = "From_Date"
Value="16-06-2018"

I said drop down box above, I need the code for a filter when I can type the information and the other where I must select from a designated field. Just thought i ought to clarify

 

[John_w]

It's very difficult to help you without seeing the web page.

In regards to the drop down box value change I can input a value. I want to input a Value from cell A1 (sheet1) into where the placeholder value is in the below HTML code. I've tried playing around with this but cannot get the value into the drop down box. I've used get element by name "From_Date". but not sure how to copy and paste/input value into webpage from excel when the value will change.

Maybe something like:

Dim HTMLdoc As HTMLDocument
Dim dropdown As HTMLSelectElement
Dim evtChange As Object

Set HTMLdoc = IE.document  'IE object with the page loaded and ready
Set evtChange = HTMLdoc.createEvent("HTMLEvents")
evtChange.initEvent "change", True, False
Set dropdown = HTMLdoc.getElementsByName("From_Date")(0)
dropdown.Value = Format(Worksheets("Sheet1").Range("A1").Value,"dd-mm-yyyy")  'date from A1
dropdown.dispatchEvent evtChange  'you might need this, or maybe something else

the HTML code around the button is < button class="btn btn-primary" > search < / button > - literally the only line relating to the button rest all are formatting rows etc
no name no ID no tag etc. so not sure how to get this to work.

Maybe:

Dim button As HTMLButtonElement
Set button = HTMLdoc.getElementsByClassName("btn btn-primary")(0)  'assuming it is the first (0) button with that class name
button.Click
While IE.busy Or IE.readyState <> READYSTATE_COMPLETE: DoEvents: Wend

The above code requires references to MS HTML Object Library and MS Internet Controls in Tools -> References in the VBA editor.