Jump to content

_ExcelWriteArray error


Recommended Posts

When I try to write an array to an excel document I am getting an error of

C:\Program Files\AutoIt3\Include\Excel.au3 (530) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.:
$oExcel.Activesheet.Cells(($xx - $iIndexBase) + $iStartRow, $iStartColumn).Value = $aArray[$xx]
$oExcel.Activesheet.Cells(($xx - $iIndexBase) + $iStartRow, $iStartColumn).Value = ^ ERROR

I am pulling the uninstall strings from a remote system and placing them in an excel doc.. I can do a

_ArrayDisplay($sSft)

and everything is "normal"?

Here is my code..

#include <Excel.au3>
#include <Array.au3>
#include <file.au3>
#include <ad.au3>
#include <GuiListView.au3>
#RequireAdmin
_AD_Open()
;****************************************************************
;~ Pull items from C4 OU
;****************************************************************
Global $aComputers
if FileExists (@scriptdir & "\OU.txt") Then
    $sOU = FileReadLine (@scriptdir & "\OU.txt",1)
Else
    FileOpen (@scriptdir & "\OU.txt",1)
$sOU =  InputBox("OU?","What is your OU's Distinguidhed Name?","","",250,125)
FileWriteLine (@scriptdir & "\OU.txt",$sOU)
FileClose(@scriptdir & "\OU.txt")
If  @error then
    MsgBox(0,"Error","You have submitted and invalid OU!")
    exit
elseif $sOU = "" then
    MsgBox(0,"Error","You have submitted and invalid OU!")
    exit
endif
endif
$aComputers = _AD_GetObjectsInOU($sOU,"(objectcategory=computer)",2,"name,location") ;calls function in the AD.au3 to query AD
_AD_Close()
$1=1

Global $i
Local $sSft

ProgressOn("Progress", "Start Scan","")
Do
ProgressSet(($1 / UBound($aComputers)*100),"Scanning " & $1 & " of " & UBound($aComputers)-1 & " " & $aComputers[$1][0],StringFormat("%.2f", ($1 / UBound($aComputers)*100)) & "% Complete")
$var = Ping($aComputers[$1][0])
if $var Then
_ComputerGetSoftware($sSft)
if $sSft = 0 Then
    sleep(1)
    $1+=1
Else
_ArrayDisplay($sSft)
$oExcel1 = _ExcelBookNew(0)
_ExcelBookSaveAs($oExcel1, @scriptdir & "\InstalledSoftware\"& $aComputers[$1][0] , "xls", 0, 1)
_ExcelBookClose($oExcel1, 0)
sleep(1000)
$oExcel2 = _ExcelBookOpen(@scriptdir & "\InstalledSoftware\"& $aComputers[$1][0] & ".xls",0)
_ExcelWriteArray($oExcel2, 1, 1, $sSft,1)

_ExcelBookSaveAs($oExcel2, @scriptdir & "\InstalledSoftware\"& $aComputers[$1][0] , "xls", 0, 1)
_ExcelBookClose($oExcel2, 0)
$1+=1
endif
Else
    sleep(10)
    $1+=1
    EndIf
Until $1 = UBound($aComputers)

while 1
    sleep(10)
WEnd
Func _AllExit()
    GUIDelete($sGui)
    Exit
EndFunc
Func _Uninstall()
    Local $proc = StringSplit(GUICtrlRead(GUICtrlRead($sLvw)), '|', 1)
    If $proc[1] == 0 Then Return -1
    If $proc[5] Then ShellExecuteWait ($proc[5])
        exit
EndFunc
Func _Copy2Clip()
    Local $proc = StringSplit(GUICtrlRead(GUICtrlRead($sLvw)), '|', 1)
    If $proc[1] == 0 Then Return -1
    If $proc[5] Then ClipPut($proc[5])
EndFunc

Func _ComputerGetSoftware(ByRef $aSoftwareInfo)
    Local Const $UnInstKey = "\\"& $aComputers[$1][0]&"\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall"
;~  MsgBox(0,"",$UnInstKey)
    Local $i = 1
    Dim $aSoftwareInfo[1][4]

$input = 'ALL'
    If @Error = 1 Then Exit
    If $input = 'ALL' Then
    For $j = 1 To 500
        $AppKey = RegEnumKey($UnInstKey, $j)
        If @error <> 0 Then Exitloop
        If RegRead($UnInstKey & "\" & $AppKey, "DisplayName") = '' Then ContinueLoop
        ReDim $aSoftwareInfo[UBound($aSoftwareInfo) + 1][4]
        $aSoftwareInfo[$i][0] = StringStripWS(StringReplace(RegRead($UnInstKey & "\" & $AppKey, "DisplayName"), " (remove only)", ""), 3)
        $aSoftwareInfo[$i][1] = StringStripWS(RegRead($UnInstKey & "\" & $AppKey, "DisplayVersion"), 3)
        $aSoftwareInfo[$i][2] = StringStripWS(RegRead($UnInstKey & "\" & $AppKey, "Publisher"), 3)
        $aSoftwareInfo[$i][3] = StringStripWS(RegRead($UnInstKey & "\" & $AppKey, "UninstallString"), 3)
        $i = $i + 1
    
    Next
    $aSoftwareInfo[0][0] = UBound($aSoftwareInfo, 1) - 1
    If $aSoftwareInfo[0][0] < 1 Then SetError(1, 1, 0)     
    $1+=1
    Return _ArraySort($aSoftwareInfo)
    
Else
        
    For $j = 1 To 500
        $AppKey = RegEnumKey($UnInstKey, $j)
        If @error <> 0 Then Exitloop
        $Reg = RegRead($UnInstKey & "\" & $AppKey, "DisplayName")
        $string = stringinstr($Reg, $input)  
        If $string = 0 Then Continueloop
        ReDim $aSoftwareInfo[UBound($aSoftwareInfo) + 1][4]
        $aSoftwareInfo[$i][0] = StringStripWS(StringReplace(RegRead($UnInstKey & "\" & $AppKey, "DisplayName"), " (remove only)", ""), 3)
        $aSoftwareInfo[$i][1] = StringStripWS(RegRead($UnInstKey & "\" & $AppKey, "DisplayVersion"), 3)
        $aSoftwareInfo[$i][2] = StringStripWS(RegRead($UnInstKey & "\" & $AppKey, "Publisher"), 3)
        $aSoftwareInfo[$i][3] = StringStripWS(RegRead($UnInstKey & "\" & $AppKey, "UninstallString"), 3)
        $i = $i + 1
    
    Next
    $aSoftwareInfo[0][0] = UBound($aSoftwareInfo, 1) - 1
    If $aSoftwareInfo[0][0] < 1 Then SetError(1, 1, 0)        
    Return _ArraySort($aSoftwareInfo)
    
    Endif
EndFunc
Func _Expand()
    _GUICtrlListView_SetColumnWidth($sLvw, 1, $LVSCW_AUTOSIZE)
    _GUICtrlListView_SetColumnWidth($sLvw, 2, $LVSCW_AUTOSIZE)
    _GUICtrlListView_SetColumnWidth($sLvw, 3, $LVSCW_AUTOSIZE)
    _GUICtrlListView_SetColumnWidth($sLvw, 4, $LVSCW_AUTOSIZE)
EndFunc
Edited by myk3
Link to comment
Share on other sites

The _ExcelWriteArray() function will only take a 1D array. If you only want to write one column or row of data from a 2D array, you'll have to extract that to a 1D array first. If you meant to write the 2D data to Excel then use _ExcelWriteSheetFromArray() instead.

:)

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
Link to comment
Share on other sites

The _ExcelWriteArray() function will only take a 1D array. If you only want to write one column or row of data from a 2D array, you'll have to extract that to a 1D array first. If you meant to write the 2D data to Excel then use _ExcelWriteSheetFromArray() instead.

:)

Thanks for the help.. I will try it again when I get back to work tomorrow.. thanks again..

EDIT: Worked great thanks.

Edited by myk3
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...