Request object failed for Excel

[spoo]

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 popup

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

  

 

 

[Juvigy]

And where exactly do you get the error?

[spoo]

         $valSheet = $eWorkbook.Sheets($sheet_names[$i][0])

[Nine]

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

[Juvigy]

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.

[water]

20 minutes ago, Juvigy said:

is a valid sheet name

... or index.

[Nine]

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