Sign in to follow this  
Followers 0
orichec

IECreate not working using Excelsheet values

4 posts in this topic

#1 ·  Posted (edited)

Hello,

I'm trying to save source of a particular webpage using its URL. When i tried to save source code using following command it works fine

$IE = _IECreate( 'www.example.com', 0, 0 )

But my problem is different I've saved different URL in my excel sheet, that's why, I tried to use "$IE" command in this way;

$URL = _Excel_RangeCopyPaste($oWorkbook1.Activesheet, "C1")
$IE = _IECreate($URL, 0, 0 )

But It's unable to fetch source codes using URL placed in excel sheet 1 at cell No. "C1".

Please guide me what's wrong with my codes and how can I make it possible to use URL placed in excel sheet instead of copying URL every time in "_IECreate" command.

Thanks

#RequireAdmin
#include <Excel.au3>
#include <IE.au3>
#include <String.au3>
#include <Array.au3>
Local $oExcel = _Excel_Open()
Local $oWorkbook1 = _Excel_BookOpen($oExcel, @ScriptDir & "\Software.csv", True)
$file = fileopen(@scriptdir & "\source01.txt", 10)
;------------Below Command is not working---------------------
$URL = _Excel_RangeCopyPaste($oWorkbook1.Activesheet, "C1"
;------------String $IE unable to fetch data from URL available in excel sheet at cell No. "C1"---------------------
$IE = _IECreate($URL, 0, 0 )
$source = _IEDocReadHTML($IE)
FileWrite($file, $source)

 

Edited by orichec

Share this post


Link to post
Share on other sites



Try replacing your _ExcelRAngeCopyPaste line with this:

$URL = _Excel_RangeRead ($oWorkbook1, Default, "C1")

Make use of Msgbox to display your variables and it will help you troubleshoot any issues you are having.

1 person likes this

Share this post


Link to post
Share on other sites

Your script isn't runable:

>Running AU3Check (3.3.14.2)  from:C:\Program Files\AutoIt3  input:C:\Users\Bert\AutoIt3.My\Temp\aa.au3
"C:\Users\Bert\AutoIt3.My\Temp\aa.au3"(9,59) : error: syntax error
$URL = _Excel_RangeCopyPaste($oWorkbook1.Activesheet, "C1"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\Users\Bert\AutoIt3.My\Temp\aa.au3"(11,21) : warning: $URL: possibly used before declaration.
$IE = _IECreate($URL,
~~~~~~~~~~~~~~~~~~~~^
"C:\Users\Bert\AutoIt3.My\Temp\aa.au3"(11,29) : error: syntax error
$IE = _IECreate($URL, 0, 0 )
~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\Users\Bert\AutoIt3.My\Temp\aa.au3"(12,29) : warning: $IE: possibly used before declaration.
$source = _IEDocReadHTML($IE)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\Users\Bert\AutoIt3.My\Temp\aa.au3"(12,30) : error: syntax error
$source = _IEDocReadHTML($IE)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\Users\Bert\AutoIt3.My\Temp\aa.au3"(13,25) : warning: $source: possibly used before declaration.
FileWrite($file, $source)
~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\Users\Bert\AutoIt3.My\Temp\aa.au3"(12,29) : error: $IE: undeclared global variable.
$source = _IEDocReadHTML($IE)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\Users\Bert\AutoIt3.My\Temp\aa.au3"(13,25) : error: $source: undeclared global variable.
FileWrite($file, $source)
~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Users\Bert\AutoIt3.My\Temp\aa.au3 - 5 error(s), 3 warning(s)
!>16:31:31 AU3Check ended. Press F4 to jump to next error.rc:2
+>16:31:31 AutoIt3Wrapper Finished.
>Exit code: 2    Time: 1.222

but why using excel when URL is in a CSV-File? This can be read by native AutoIt.

Share this post


Link to post
Share on other sites
2 hours ago, MuffinMan said:

Try replacing your _ExcelRAngeCopyPaste line with this:

$URL = _Excel_RangeRead ($oWorkbook1, Default, "C1")

Make use of Msgbox to display your variables and it will help you troubleshoot any issues you are having.

Thanks mate, it works for me....

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

  • Similar Content

    • SteveStrop
      By SteveStrop
      Hi 
       
      I'm trying to:
      1) Open a hidden browser session
      2) Do some stuff in the background
      3)  Make the hidden window visible
       
      This is my code:
       
      Opt("WinTitleMatchMode", 2) ;1=start, 2=subStr, 3=exact, 4=advanced, -1 to -4=Nocase $oIE = _IECreate("https://www.google.co.uk/",0,0) $oSearchBox = _IEGetObjById($oIE, "lst-ib") $oSearchBox.innertext = "AutoIT" $oForm = _IEFormGetObjByName($oIE,"f") _IEFormSubmit($oForm) WinSetState("Google","",@SW_SHOW)  
      It dosen't work
      The last line does make the window visible but it is an empty greyed out box that disappears as soon as I click on it.
      I have a sort of workaround that hides the browser window as soon as I have created it which works fine:
      $oIE = _IECreate("https://www.google.co.uk/") WinSetState("Google","",@SW_HIDE) . . . WinSetState("Google","",@SW_SHOW) But this looks a bit pants as the newly created window flashes on then off of the screen.
      Am I using the wrong method to make the browser window visible?
      Thanks
       
       
    • AnonymousX
      By AnonymousX
      Hello,
      I need to pull data from company intranet website. I created a script that I can give a list of project numbers and it will open up the the related webpage for each project, save the html comments for that project, then move on to the next. 
      However my problem comes in that each time I open up a project it locks it for other users, and if you just exit the page it keeps it locked, you have to actually press the "close" button on the page. So I'm not sure how to deal with this as I end up just locking every project in my name when I run my program as I don't understand the nature of how the website is built. I don't understand how to close with just using my automation code without manually having to press the stupid button. 
      My Function:
      Func Getscript($ProjectAddress) ;getting the page source code and storing it into text file for easy reading it $file = FileOpen(@ScriptDir & "\source.txt", 10) $IE = _IECreate($ProjectAddress & ".html", 0, 0) $source = _IEDocReadHTML($IE) FileWrite($file, $source) $target_source = _StringBetween($source, "<BODY>", "</BODY>") ; only take content between the specified tags _IEQuit($IE) FileClose($file) return $target_source[0] EndFunc ;==>Getscript  
      Source Code I got from website that I think is relevant:
      function closeForm(){ if(topButtonEnabled("closeButton")){ parent.mainFrame.closeForm(); } } <button id="closeButton" class="saveCloseButton" style="LEFT: 77px;" onclick=closeForm()>&nbsp;Close&nbsp;</button>  
      Any Ideas of how to tell website to either activate the closeform function or just give it the command to close?
       
      Thanks
    • anusha
      By anusha
      Hi I have jus started using auto-it . Please correct me if I'm wrong.
      I need to read data from an input in text box and search in excel file and return value in next column of matched cell on GUI.
      I have written below code but i cannot use variable which has data stored. it works only when search string is hard coded.
      Please help out.
       
      Example()
      Func Example()
      Local $GuiMain = GUICreate("EXCEL TEST", 399, 180) ;creates main GUI
      ;~ Local $idOK = GUISetOnEvent($GUI_EVENT_CLOSE, "Close")
      Local $iWidthCell = 70
      Local $idLabel = GUICtrlCreateLabel("PART NUMBER", 10, 30, $iWidthCell,50)
      Local $RUN_1 = GUICtrlCreateButton("OK", 70, 70, 85, 25)
      Local $Input_1 = GUICtrlCreateInput("PART NUMBER", 100, 20, 120, 20)
      Local $sMenutext = GUICtrlRead($Input_1, 1)
      GUISetState(@SW_SHOW, $GuiMain)

          While 1
          $MSG = GUIGetMsg()
          Select
              Case $MSG = $GUI_EVENT_CLOSE
                  Exit
              Case $MSG = $RUN_1
                  Local $oAppl = _Excel_Open()

      Local $sFilePath1 = "D:\Anu_WorkFolder\Components.xlsx"
      Local $oWorkbook = _Excel_BookOpen($oAppl, $sFilePath1, Default, Default, True)
      Local $aResult = _Excel_RangeFind($oWorkbook, $sMenutext , Default, Default, $xlWhole)
    • Nareshm
      By Nareshm
      How to Activate Opened Excel Windows Using Class not Tittle, Because Some time opened defferent excel that have different name.
      I Tried with
      Winactivate ("[CLASS:XLMAIN]") but not working
    • willichan
      By willichan
      Here is another one from my archives that filled a specific need.
       
      Here is the back story if you are interested.
       
      Keep in mind that I wrote this script over 3 years ago, so it may not compile or run directly without some minor tweaks.  It also requires the use of GraphViz to build the graph.
      #cs ---------------------------------------------------------------------------- Project Name: ExcelLinksMapper Description: Analyse an Excel file's links and map them out. Creation Date: 9/26/2014 AutoIt Version: Author: willichan Requires: Graphviz (http://graphviz.org/) #ce ---------------------------------------------------------------------------- Opt("MustDeclareVars", 1) ;0=no, 1=require pre-declare Opt("TrayAutoPause", 0) ;0=no pause, 1=Pause Opt("TrayMenuMode", 0) ;0=append, 1=no default menu, 2=no automatic check, 4=menuitemID not return Opt("TrayIconHide", 0) ;0=show, 1=hide tray icon Global Const $MyName=StringLeft(@ScriptName, StringInStr(@ScriptName,".", 0, -1)-1) ;get just the name portion of the script/exe name Global Const $MyMutex=$MyName & "-82243BEBC30533A3" ;name the mutex for this app Global $SQLloaded = False Global $sDbName = @ScriptDir & "\db2gv.db" ConsoleWrite($sDbName & @crlf) If _MutexExists($MyMutex) Then Exit #include <SQLite.au3> #include <SQLite.dll.au3> #include <file.au3> #include <array.au3> #include <excel.au3> _ConfigInitialize() _Main() Func _ConfigInitialize() OnAutoItExitRegister("_ConfigDestroy") ;initializers here Global $sSQliteDll = _SQLite_Startup() If @error Then MsgBox(0, "SQLite Error", "could not load the DLL") Global $sSQLiteDB = _SQLite_Open($sDbName) If $sSQLiteDB = 0 Then MsgBox(0, "SQLite Error", "could not open the database") $SQLloaded =True __CreateTables() EndFunc ;==>_ConfigInitialize Func _ConfigDestroy() ;destructors here If $SQLloaded Then _SQLite_Close() _SQLite_Shutdown() EndIf EndFunc ;==>_ConfigDestroy Func _MutexExists($sOccurenceName) Local $ERROR_ALREADY_EXISTS = 183, $handle, $lastError $sOccurenceName = StringReplace($sOccurenceName, "\", "") $handle = DllCall("kernel32.dll", "int", "CreateMutex", "int", 0, "long", 1, "str", $sOccurenceName) $lastError = DllCall("kernel32.dll", "int", "GetLastError") Return $lastError[0] = $ERROR_ALREADY_EXISTS EndFunc ;==>_MutexExists Func __CreateTables() _SQLite_Exec($sSQLiteDB, "DROP TABLE IF EXISTS nodes;") _SQLite_Exec($sSQLiteDB, "CREATE TABLE IF NOT EXISTS nodes( name TEXT PRIMARY KEY, fileexists INTEGER);") _SQLite_Exec($sSQLiteDB, "DROP TABLE IF EXISTS links;") _SQLite_Exec($sSQLiteDB, "CREATE TABLE IF NOT EXISTS links( id INTEGER PRIMARY KEY, name1 TEXT, name2 TEXT, weight INTEGER);") EndFunc Func _Main() Local $sInfile, $vResult, $iErrLoop $sInfile = FileOpenDialog("Source File", @WorkingDir, "Excel files (*.xl*)", 1 + 2) If Not FileExists($sInfile) Then MsgBox(0, "Excel Links Mapper Error", "Unable to locate source file") Exit EndIf $vResult = $SQLITE_IOERR $iErrLoop = 5 While $vResult = $SQLITE_IOERR $vResult = _SQLite_Exec($sSQLiteDB, "INSERT OR IGNORE INTO nodes ('name', 'fileexists') VALUES (" & _SQLite_FastEscape($sInfile) & ", 1);") If Not $vResult = $SQLITE_OK Then Sleep(100) $iErrLoop -= 1 If $iErrLoop = 0 Then ConsoleWrite($iErrLoop & " tries" & @CRLF & $sInfile & @CRLF) $vResult = $SQLITE_OK EndIf WEnd _GetExcelLinks($sInfile) Global $hOutfile = FileOpen(@ScriptDir & "\" & $MyName & ".gv", 2) If $hOutfile = -1 Then MsgBox(0, $MyName & " ERROR", "Unable to upen file for output") Exit EndIf _WriteHeader() _WriteNodes() _WriteLinks() _WriteFooter() FileClose($hOutfile) _GenerateGraph() ShellExecute(@ScriptDir & '\ExcelLinksMapper.png') EndFunc ;==>_Main Func _GetExcelLinks($strFileName) Local $hQuery, $aCount, $iErrLoop, $vResult ConsoleWrite($strFileName & @CRLF) Local $iLoop, $iExists Local $aLinks Local Const $xlExcelLinks = 1 Local $oExcel = _Excel_Open() Local $ret = _Excel_BookOpen_NoUpdate($oExcel, $strFileName, True, True) Local $err = @error If $err Then If Not IsObj($oExcel) Then ConsoleWrite($ret & " - " & $err & @CRLF) Exit EndIf EndIf $aLinks = $oExcel.ActiveWorkbook.LinkSources($xlExcelLinks) _Excel_BookClose($oExcel, False) _Excel_Close($oExcel, False, True) If IsArray($aLinks) Then If UBound($aLinks) > 0 Then For $iLoop = 0 To UBound($aLinks) - 1 If $aLinks[$iLoop] <> $strFileName Then $iExists = FileExists($aLinks[$iLoop]) ConsoleWrite("DEBUG - Calling WriteNode()") __WriteNode($aLinks[$iLoop], $iExists) ConsoleWrite("DEBUG - Calling WriteLink()") __WriteLink($strFileName, $aLinks[$iLoop]) If $iExists And ($aLinks[$iLoop] <> $strFileName) Then _GetExcelLinks($aLinks[$iLoop]) EndIf Next EndIf EndIf EndFunc ;==>_GetExcelLinks Func __WriteNode($sName, $iExists) Local $iErrLoop = 5 ;Number of attempts to make Local $vResult Do ConsoleWrite("DEBUG - WriteNode()" & @CRLF & " _SQLite_Exec(INSERT OR IGNORE INTO nodes ('name', 'fileexists') VALUES (" & _SQLite_FastEscape($sName) & ", " & $iExists & ");) - create node entry" & @CRLF) $vResult = _SQLite_Exec($sSQLiteDB, "INSERT OR IGNORE INTO nodes ('name', 'fileexists') VALUES (" & _SQLite_FastEscape($sName) & ", " & $iExists & ");") If Not $vResult = $SQLITE_OK Then Sleep(100) $iErrLoop -= 1 If $iErrLoop = 0 Then $vResult = $SQLITE_OK ;Used up all our attempts, so simulate a success Until $vResult = $SQLITE_OK EndFunc ;==>__WriteNode Func __WriteLink($sName1, $sName2) Local $iErrLoop = 5 ;Number of attempts to make Local $vResult, $hQuery, $vCount ConsoleWrite("DEBUG - WriteNode()" & @CRLF & " _SQLite_Query(SELECT weight FROM links WHERE 'name1'=" & _SQLite_FastEscape($sName1) & " AND 'name2'=" & _SQLite_FastEscape($sName2) & ";) - lookup link entry" & @CRLF) _SQLite_Query($sSQLiteDB, "SELECT weight FROM links WHERE 'name1'=" & _SQLite_FastEscape($sName1) & " AND 'name2'=" & _SQLite_FastEscape($sName2) & ";", $hQuery) ConsoleWrite("DEBUG - _SQLite_FetchData()" & @CRLF) _SQLite_FetchData($hQuery, $vCount) If UBound($vCount) > 1 Then _ArrayDisplay($vCount) If $SQLITE_OK And UBound($vCount) > 1 Then $vCount = $vCount[1] + 1 Else $vCount = 1 EndIf Do If $vCount = 1 Then ConsoleWrite("DEBUG - _SQLite_Exec() - create link entry" & @CRLF) $vResult = _SQLite_Exec($sSQLiteDB, "INSERT INTO links ('name1', 'name2', 'weight') VALUES (" & _SQLite_FastEscape($sName1) & ", " & _SQLite_FastEscape($sName2) & ", " & $vCount & ");") Else ConsoleWrite("DEBUG - _SQLite_Exec() - update link entry" & @CRLF) $vResult = _SQLite_Exec($sSQLiteDB, "UPDATE links SET 'weight'=" & $vCount & " WHERE 'name1'=" & _SQLite_FastEscape($sName1) & " AND 'name2=" & _SQLite_FastEscape($sName2) & ";") EndIf If Not $vResult = $SQLITE_OK Then Sleep(100) $iErrLoop -= 1 If $iErrLoop = 0 Then $vResult = $SQLITE_OK ;Used up all our attempts, so simulate a success Until $vResult = $SQLITE_OK EndFunc ;==>__WriteLink ; #FUNCTION# ==================================================================================================================== ; Author ........: SEO <locodarwin at yahoo dot com> ; Modified.......: litlmike, water, GMK, willichan ; =============================================================================================================================== Func _Excel_BookOpen_NoUpdate($oExcel, $sFilePath, $bReadOnly = Default, $bVisible = Default, $sPassword = Default, $sWritePassword = Default) If Not IsObj($oExcel) Or ObjName($oExcel, 1) <> "_Application" Then Return SetError(1, @error, 0) If Not FileExists($sFilePath) Then Return SetError(2, 0, 0) If $bReadOnly = Default Then $bReadOnly = False If $bVisible = Default Then $bVisible = True ;; changing the second parameter on the following line to a 0 tells Excel not to update any links. Local $oWorkbook = $oExcel.Workbooks.Open($sFilePath, 0, $bReadOnly, Default, $sPassword, $sWritePassword) If @error Then Return SetError(3, @error, 0) $oExcel.Windows($oWorkbook.Name).Visible = $bVisible ; If a read-write workbook was opened read-only then return an error If $bReadOnly = False And $oWorkbook.Readonly = True Then Return SetError(4, 0, $oWorkbook) Return $oWorkbook EndFunc ;==>_Excel_BookOpen_NoUpdate Func _GenerateGraph() RunWait(@ScriptDir & '\GraphViz238\bin\dot.exe -Tpng "' & @ScriptDir & '\' & $MyName & '.gv" -o "' & @ScriptDir & '\ExcelLinksMapper.png"') EndFunc ;==>_GenerateGraph Func _WriteHeader() __OutLine(0, "digraph main {") EndFunc ;==>_WriteHeader Func _WriteNodes() Local $aResult, $iRows, $iColumns, $iRval Local $iLoop, $sStyle __OutLine(1, "// Nodes") $iRval = _SQLite_GetTable($sSQLiteDB, "SELECT name, fileexists FROM nodes;", $aResult, $iRows, $iColumns) If @error Then ConsoleWrite("_WriteNodes()" & @CRLF & "_SQLite_GetTable") If $iRval = $SQLITE_OK Then For $iLoop = 1 To $iRows If $aResult[($iLoop * 2) + 2] > 0 Then $sStyle = "normal" Else $sStyle = "missing" EndIf __OutNode($aResult[1 + (2 * $iLoop)], $sStyle) ;; Next Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg() & @CRLF & "in _WriteNotes() calling _SQLite_GetTable()") Exit EndIf EndFunc ;==>_WriteNodes Func __OutNode($sName, $sStyle = Default) If $sStyle = Default Then $sStyle = "Normal" Switch StringLower($sStyle) Case "missing" __OutLine(1, __MakeName($sName) & ' [label="' & StringReplace($sName, "\", "\\") & '",color=red,fontcolor=red,shape=octagon];') Case Else ;"normal", Default __OutLine(1, __MakeName($sName) & ' [label="' & StringReplace($sName, "\", "\\") & '",color=black,fontcolor=black,shape=box];') EndSwitch EndFunc ;==>__OutNode Func _WriteLinks() Local $aResult, $iRows, $iColumns, $iRval Local $iLoop, $sStyle, $aCount __OutLine(1, "// Links") $iRval = _SQLite_GetTable($sSQLiteDB, "SELECT name1, name2 FROM links ORDER BY name1 ASC, name2 ASC;", $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then For $iLoop = 1 To $iRows __OutLink($aResult[1 + (2 * $iLoop)], $aResult[2 + (2 * $iLoop)]) Next Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg()) Exit EndIf EndFunc ;==>_WriteLinks Func __OutLink($sName1, $sName2, $iWeight = 1) __OutLine(1, __MakeName($sName1) & ' -> ' & __MakeName($sName2) & ';') EndFunc ;==>__OutLink Func _WriteFooter() __OutLine(0, "}") EndFunc ;==>_WriteFooter Func __OutLine($iTabs, $sText) Local $iLoop If $iTabs > 0 Then For $iLoop = 1 To $iTabs FileWrite($hOutfile, " ") ;ConsoleWrite(" ") Next EndIf FileWriteLine($hOutfile, $sText) EndFunc ;==>__OutLine Func __MakeName($sText) Local $sNewName = StringReplace($sText, "\", " ") $sNewName = StringReplace($sNewName, "/", " ") $sNewName = StringReplace($sNewName, "'", " ") $sNewName = StringReplace($sNewName, '"', " ") $sNewName = StringReplace($sNewName, ':', " ") $sNewName = StringReplace($sNewName, '.', " ") $sNewName = StringReplace($sNewName, '-', " ") $sNewName = StringReplace($sNewName, '$', " ") $sNewName = StringStripWS($sNewName, 8) Return StringLower($sNewName) EndFunc ;==>__MakeName