Loop through pages of .aspx webpage & copy/paste in Excel Sheets

[forbillian]

What I am trying to achieve:

1. Run a script that opens an .aspx webpage

2. Do a loop & search for hyperlink page numbers on the webpage from 1 to n & click on each.

3. During the loop I want to do a 'ctrl a' then a 'ctrl c' of each page clicked

4. Before going to the next page, open an existing excel workbook with sheet numbers 1 to n

   and paste the data from the webpage onto the respective Sheet# (e.g. Webpage 2 data pasted on Sheet2 of the workbook etc)

 

What I can achieve with my attempted coding script:

1.    I can get the loop working to go through each page on the webpage & select & copy all the text before moving to next page

2.    I can get the loop working to go through each page on the webpage & then each sheet in my workbook (as a test - while commenting out the code for copy paste)

What I cant achieve is:

Having the two running concurrently. 

 

 

Please see my attached script;  Apologies I cant reference the actual .aspx webpage (though any website with page numbers will work) for a real test

but I imagine the issue may seem clear to those & most of you that are more experienced with autoit than I.

 

expandcollapse popup

#include <AutoItConstants.au3>
#include <IE.au3>
#include <Excel.au3>


$sURL = "<any website with page numbers at the base>"
;$sURL = "https://www.xxxxxx.com.au/xxx/quote-data.aspx"
$oIE = _IECreate($sURL, 0, 0, 0)
$HWND = _IEPropertyGet($oIE, "hwnd")
WinSetState($HWND, "", @SW_MAXIMIZE)
_IEAction($oIE, "visible")
_IELoadWait($oIE)



;_Excel_Close($oApp)
ProcessClose("Excel.exe")


Local $oExcel = ObjCreate("Excel.Application")
$oExcel.visible=1
$oExcel.WorkBooks.Open("C:\TEST.xlsx")  ;create workbook with same number of sheets as the pages in the abovee webpage
$oExcel.Sheets("Sheet1").Select


;CHECKS THE .ASPX WEBPAGE TO FIND A PAGE NUMBER HYPERLINK FROM 1 TO n & CLICKS ON IT
 For $i = 1 to 4
   Tooltip($i)


   Local $sMyString = $i
   Local $iNumberOfWorksheets = $oExcel.Worksheets.Count
   Local $oLinks = _IELinkGetCollection($oIE)
      For $oLink In $oLinks
         Local $sLinkText = _IEPropertyGet($oLink, "innerText")
            If StringInStr($sLinkText, $sMyString) Then
           _IEAction($oLink, "click")
           _IELoadWait($oIE)

;THIS PART OF THE CODE WORKS IF I EXCLUDE THE 'oExcel.Sheets("Sheet" & $i).Select' ON NEXT LINE
;~                      WinActivate($hWnd)
;~                         Send("^a")
;~                            Sleep(1000)
;~                         Send("^c")
;~                      MouseClick($MOUSE_CLICK_LEFT, 0, 500, 0)

;THIS PART OF THE CODE WORKS IF I EXCLUDE THE ABOVE 5 LINES
                  Sleep(500)
               $oExcel.Sheets("Sheet" & $i).Select
                  Sleep(2500)

;THIS PART OF THE CODE EXAPLINS WHAT IM TRYING TO DO BUT CANT TEST YET
;~                      Send("^v")
;~                      Sleep(2000)

            ExitLoop
            EndIf

   Next

Next

 

If I try to run the code in full I get an error on this line.................  $oExcel.Sheets("Sheet" & $i).Selectrun

Any assistance would be met with indebted gratitude.

 

[Danp2]

Have you considered using _IEBodyReadHTML instead of copying the web page?

[forbillian]

I have used this option but the text I am scraping includes table data & it's a quicker method to do a simple copy and paste

into excel.    Because it is an .aspx type page the normal export to excel or read from excel only returns the first table.

 

The issue however in my above code is (regardless of how I am getting the data) why I cant get the data to paste into the excel sheets

during my loop function.

 

 

[Danp2]

There's also _Excel_RangeCopyPaste instead of sending ^V to Excel.

[forbillian]

Thanks Dan. I will give that a try.

Appreciate the feedback.

[forbillian]

So when I run my code using a manual ctrl copy to clipboard and loop through the excel sheets it works with the ctrl V paste method.

i.e. pastes the same data on each excel sheet 1 to n. There seems to be an issue in what is being stored in clipboard each instance when I loop through the  webpages.

I am wondering if I should copy each page to an array or a string e.g. using clipget during the loop?

 

With the Excel copypaste method could I change the copy from ;Local $oRange = $oWorkbook1.ActiveSheet.Range("I2:J4")  to this?

Local $oRange = $sData?

_Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, $oRange, "A1")

 

Any thoughts?

[Danp2]

Looks like it defaults to pasting from the clipboard, so I would think you could do something like this:

ClipPut($sData)
_Excel_RangeCopyPaste($oWorkbook1.Activesheet, Default, "1:1", Default, $xlPasteValues)

 

[forbillian]

I'm getting stuck.  Just wondering if should try looping through the pages using the class name or id: e,g page 4 shows this in source element info.

 

<a class="Pages" href="javascript:__doPostBack('ctl00$ctl00$cph$cphCenter$DP$ctl00$ctl03','')">4</a>

 

How can I use that to specify a page number change?

[Danp2]

You may want to review this thread for some ideas.