spoo Posted August 23, 2019 Share Posted August 23, 2019 Hello guys, I really cannot find a way to solve this error.so please help I have an excel file (example Test.xlsx) say with 34 sheets (sheets can vary from excel file to file) i need to traverse to all the 34 sheets and check for a word . After finding the word i need to apply certain condition and then write the information in another excel (Config.xlsm) when i do this process it throws Request object failed Error . However if the sheets are less lets say 10 then i will not have any problem. Since their is so many Read , Find , and Write to the excel i thought that could be the problem . so i only used find and _ExcelRead and after Finding the value i am writing it the Notepad. from NotePad i process the data. But unfortunately i still get that message rarely . Please help me expandcollapse popupFor $j=0 to UBound($VerMSN)-1 $Fileopen=FileOpen($fileName,1) $Fileopen1=FileOpen($fileName1,1) $realRank=StringSplit($VerMSN[$j][2],",") $Msn=StringSplit($VerMSN[$j][0],",") For $i=3 to UBound($sheet_names)-1 SplashTextOn("Progress","Reading through validity sheets...",500,100,Default,Default,33) if(StringInStr($sheet_names[$i][0],"Validity")<>0) Then $valSheet = $eWorkbook.Sheets($sheet_names[$i][0]) $valSheet.Activate() $fRes = FindRange($VerMSN[$j][1],$valSheet.Range("D:D")) If UBound($fRes)>0 Then If UBound($fRes)>1 Then For $f=0 to UBound($fRes)-1 $verAddress=$fRes[$f] WriteToFile($verAddress,$sheet_names[$i][0],$eWorkbook,$realRank,$Msn,$Fileopen,$Fileopen1,$Revision) Next Else $verAddress=$fRes[0] WriteToFile($verAddress,$sheet_names[$i][0],$eWorkbook,$realRank,$Msn,$Fileopen,$Fileopen1,$Revision) EndIf EndIf Next Next SplashOff() ;EndFunc Func WriteToFile($verAddress,$sheetName,$eWorkbook,$realRank,$Msn,$Fileopen,$Fileopen1,$Revision) $address= StringMid($verAddress,StringInStr($verAddress,"$",0,2)+1,3) $FAL=_Excel_RangeRead($eWorkbook,$sheetName,"B" & $address) $RangeFrom=_Excel_RangeRead($eWorkbook,$sheetName,"E" & $address) $RangeTo=_Excel_RangeRead($eWorkbook,$sheetName,"F" & $address) For $k=1 to UBound($realRank)-1 If $realRank[$k]<>"" Then $Circuit=StringLeft($sheetName,2) $Root =Rootvalue($Circuit,$sheetName) If $realRank[$k]>=$RangeFrom And $realRank[$k]<=$RangeTo Then FileWriteLine($Fileopen,$Revision &" " & $Msn[$k] &" "& $FAL &" "& $Circuit& " " & $Root) Else FileWriteLine($Fileopen1,$Revision &" " & $Msn[$k] &" "& "None" &" "& $Circuit& " " & $Root) EndIf EndIf Next EndFunc ;Find the version Func FindRange($Search,$vRange) Local $aResult[100],$iIndex = 0 Local $oMatch = $vRange.Find($Search) If IsObj($oMatch) Then $sFirst = $oMatch.Address While 1 $aResult[$iIndex] = $oMatch.Address $iIndex = $iIndex + 1 If Mod($iIndex, 100) = 0 Then ReDim $aResult[UBound($aResult, 1) + 100] $oMatch = $vRange.Findnext($oMatch) If Not IsObj($oMatch) Or $sFirst = $oMatch.Address Then ExitLoop WEnd EndIf ReDim $aResult[$iIndex] Return $aResult EndFunc Func Rootvalue($Circuit,$sheet_names) $Root=StringReplace(StringReplace($sheet_names,"-Validity",""),$Circuit,"") Return $Root EndFunc Link to comment Share on other sites More sharing options...
Juvigy Posted August 23, 2019 Share Posted August 23, 2019 And where exactly do you get the error? Link to comment Share on other sites More sharing options...
spoo Posted August 23, 2019 Author Share Posted August 23, 2019 $valSheet = $eWorkbook.Sheets($sheet_names[$i][0]) Link to comment Share on other sites More sharing options...
Nine Posted August 23, 2019 Share Posted August 23, 2019 Unfortunately, your code is partial and unrunable. So it is impossible for us to test it. I would suggest that you make a runable snippet of your code and provide the .xlsx file so we can fully test it. In any case, you may need to intercept COM errors. See help file under Obj/COM reference to get an example how to do it... “They did not know it was impossible, so they did it” ― Mark Twain Spoiler Block all input without UAC Save/Retrieve Images to/from Text Monitor Management (VCP commands) Tool to search in text (au3) files Date Range Picker Virtual Desktop Manager Sudoku Game 2020 Overlapped Named Pipe IPC HotString 2.0 - Hot keys with string x64 Bitwise Operations Multi-keyboards HotKeySet Recursive Array Display Fast and simple WCD IPC Multiple Folders Selector Printer Manager GIF Animation (cached) Screen Scraping Multi-Threading Made Easy Link to comment Share on other sites More sharing options...
Juvigy Posted August 23, 2019 Share Posted August 23, 2019 1 hour ago, spoo said: $valSheet = $eWorkbook.Sheets($sheet_names[$i][0]) Are you sure $sheet_names[$i][0] is a valid sheet name? Put a check before that line. Link to comment Share on other sites More sharing options...
water Posted August 23, 2019 Share Posted August 23, 2019 20 minutes ago, Juvigy said: is a valid sheet name ... or index. 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...
Nine Posted August 23, 2019 Share Posted August 23, 2019 37 minutes ago, Juvigy said: is a valid sheet name? I supposed he is using _Excel_SheetList ? Like I said, the script is too incomplete, we can only guess... “They did not know it was impossible, so they did it” ― Mark Twain Spoiler Block all input without UAC Save/Retrieve Images to/from Text Monitor Management (VCP commands) Tool to search in text (au3) files Date Range Picker Virtual Desktop Manager Sudoku Game 2020 Overlapped Named Pipe IPC HotString 2.0 - Hot keys with string x64 Bitwise Operations Multi-keyboards HotKeySet Recursive Array Display Fast and simple WCD IPC Multiple Folders Selector Printer Manager GIF Animation (cached) Screen Scraping Multi-Threading Made Easy 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