Jump to content

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


Go to solution Solved by Danp2,

Recommended Posts

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

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

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 - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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
Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...