Jump to content
Sign in to follow this  
spoo

Request object failed for Excel

Recommended Posts

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 

 

 

For $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


  

 

 

Share this post


Link to post
Share on other sites

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...

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
20 minutes ago, Juvigy said:

is a valid sheet name

... or index.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites
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...

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  

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...