Sign in to follow this  
Followers 0
RisaAudr

Automate getting info from webpage and saving to specific cells in Excel

6 posts in this topic

Hi all! I am still relatively new to AutoIt and would really appreciate help or at least a jumping off point to get started with a new script.

Basically in Excel I have a list of IP addresses (pointing to printers) and I need to retrieve tabulated information from those webpages (MAC address, Serial Number, Host-name, etc.) to then save back into specific columns in the spreadsheet.  If a printer is offline/ does not exist then a simple message box with the IP address can help me to manually do those few.  With more than 200 printers/webpages to visit, it gets tedious quickly doing it manually.  

Any help would be appreciated.  Thanks!

 

Share this post


Link to post
Share on other sites



Update:  I have tried a few things, but it seems like several methods are outdated that I am finding online.  E.g. _ExcelSheetActivate is an unknown function name, and _ExcelBookOpen is also an unknown, but _Excel_BookOpen seems to work.  

I suppose I have a couple of specific questions at the moment:  1) How do I select which sheet I would like to use (#3 in my case)?  2) How do I edit the value of a cell based on row/col number?  Also, any suggestions as to how to actually navigate the web page itself and then copy the data would be helpful too (I can post screencap later if it helps).

Below I pasted my code that I have thus far.  Thanks in advance!

#include <IE.au3>
#include <Excel.au3>
#include <Array.au3>
#include <MsgBoxConstants.au3>

Local $filePath="C:\Users\*username*\Documents\MyExcelFile.xlsx"
Local $oExcel= _Excel_Open()
Local $oExcelBook= _Excel_BookOpen($oExcel, $filePath)
_ExcelSheetActivate($oExcelBook,"Sheet3")

;declaring these for when I will copy Column 2 (IP addresses) into array
Local $iStartRow=2
Local $iStartColumn=2
Local $iRowCnt=0
Local $iColCnt=1
Local $iColShift=False

Local $excelIPAddresses=_ExcelReadSheetToArray ($oExcelBook, $iStartRow, $iStartColumn, $iRowCnt, $iColCnt, $iColShift)
Local $arrSize=UBound($excelIPAddresses)
MsgBox(0, "Start", "Number of IP addresses: " & $excelIPAddressesSize)

Local $oIE = _IECreate("about:blank")

;Loop to test if I can actually open the webpages, will actually need functionality later
For $i=1 to 5;$arrSize
   ;Process: lookup IP, copy data, paste to excel, goto next IP
   _IENavigate($oIE, $excelIPAddresses)
   sleep(3000)
Next

Share this post


Link to post
Share on other sites

Starting with Autoit 3.3.12.0 a complete rewrite of the Excel UDF is delivered. The script breaking changes section in Autoit 3.3.1.20 describes what has changed.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

Read through and fixed the Excel-related issues.  Thanks water for the useful link!
 
So, now I think I have one last challenge.  I need to read the text on a webpage as it is displayed, and find specific fields in it.  However, this text is NOT stored in the html (if i do view source, there isn't much there).  I have been using the IE.au3 class thus far, but since the text isn't on the html, I'm not exactly sure how to proceed.  Any suggestions?  I attached a screenshot of an example webpage & copied it's html code below.

<html dir="ltr">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta http-equiv="Content-Style-Type" content="text/css">
<meta http-equiv="Content-Script-Type" content="text/javascript">
<title></title>
<script language="JavaScript" type="text/javascript">
if( 1 != 1 )
{
document.write('<link rel="shortcut icon" href="../favicon.ico">');
}
var lang="eng";
var currentpage="";
var testData = new Array();
testData[0] = 0;
var curPageFlag = false;
</script>
</head>
<frameset border=0 cols=* frameBorder=NO frameSpacing=0 rows=1,*>
<frame name=topframe noresize scrolling=no
src="../top.htm">
<frame name=main
src="../start/start.htm">
</frameset>
<noframes></noframes>
</html>

Thanks guys!

 

EDIT:  

I have found a way to copy all of the text into clipboard, and used clipGet() to save this into a string variable.  However, when outputting the contents of that variable to see what was in it, it is not complete (it only holds so much, and I suspect there is a size limitation as to how long strings can be made). Any ideas?

I have tried pasting into notepad (and can do such successfully), but then have the issue of finding the fields that I need and extracting the data.  Saving this notepad file then trying to read it with _FileReadLine() and etc. kind of defeats the purpose.

Thanks again!

post-86722-0-12973400-1405947937_thumb.j

Edited by RisaAudr

Share this post


Link to post
Share on other sites

Looks like you have some frames to deal with. Take a look at _IEIsFrameSet, _IEFrameGetCollection, and _IEFrameGetObjByName in the help file.

Share this post


Link to post
Share on other sites

Thanks Danp2!  I was able to work with the frames and get the info that I wanted out of the webpages, and then export that data to excel.  Worked like a champ.

Thanks guys for your help!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0