RisaAudr Posted July 18, 2014 Share Posted July 18, 2014 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! Link to comment Share on other sites More sharing options...
RisaAudr Posted July 18, 2014 Author Share Posted July 18, 2014 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 Link to comment Share on other sites More sharing options...
water Posted July 21, 2014 Share Posted July 21, 2014 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
RisaAudr Posted July 21, 2014 Author Share Posted July 21, 2014 (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! Edited July 21, 2014 by RisaAudr Link to comment Share on other sites More sharing options...
Solution Danp2 Posted July 21, 2014 Solution Share Posted July 21, 2014 Looks like you have some frames to deal with. Take a look at _IEIsFrameSet, _IEFrameGetCollection, and _IEFrameGetObjByName in the help file. Latest Webdriver UDF Release Webdriver Wiki FAQs Link to comment Share on other sites More sharing options...
RisaAudr Posted July 21, 2014 Author Share Posted July 21, 2014 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! Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now