Sign in to follow this  
Followers 0
javip

What am I doing wrong with this array?(first time excel read)

19 posts in this topic

I'm trying to learn of arrays and reading from excel.

I know there's various GUIs out there for sysinternal

suites and such but I want to create my own.

I'm recycling some of my old code that I used for another

cmd based program and using it for this project.

can someone tell me what's wrong this?

I get no errors, but nothing happens. I have an empty

notepad.exe window open with nothing in it so it won't

prompt me to save. In practice it's going to be a kill process

for powerpoint across 30 computers.

The excel spreadsheet only has IP addresses within the first column.

Func kill()
 
#include <Excel.au3>
#include <Process.au3>
 
$sFilePath1 = GUICtrlRead($Input1) ; takes the filepath from $Input$
$oExcel = _ExcelBookOpen($sFilePath1)
$ipArray = _ExcelReadArray($oExcel, 1, 1, 5, 1, 1)
For $cells = 1 To $ipArray
$console=Run("C:\stuff\SysinternalsSuite\pskill.exe" & $ipArray[$cells] & "notepad.exe")
Next
EndFunc

here's the fileopen section in case i'm doing something wrong.

Func filebrowse()
$capd = FileOpenDialog("Choose a name.", $MyDocsFolder, "excel spreadsheet (*.xls)", 2)
GUICtrlSetData($Input1, $capd) ;this sets the input box contents to whatever the $capd variable is holding
EndFunc   ;==>filebrowse

any help would be great!

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

You lost me here:

$console=Run("C:\stuff\SysinternalsSuite\pskill.exe" & $ipArray[$cells] & "notepad.exe")

Are you sure this syntax is correct? I'm not sure what you expect to happen. Could you describe the problem you are having in more detail, along with the expected outcome. Just because you get no errors, doesn't automatically imply that your code is correct or well structured.

Edit

After looking again at this, I'm still none the wiser, however I spotted one mistake here:

For $cells = 1 To $ipArray
$ipArray is an array, and not an integer! This code will do very little, if anything at all. Edited by czardas

Share this post


Link to post
Share on other sites

This part should change:

For $cells = 1 To $ipArray

Use Ubound to detimine how many or in your case you wanted just the first five lines from the spreadsheet so use five then instead of $ipArray.

Are you trying to kill the Notepad.exe process on all the IP adresses?


Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

You lost me here:

$console=Run(&quot;C:\stuff\SysinternalsSuite\pskill.exe&quot; &amp; $ipArray[$cells] &amp; &quot;notepad.exe&quot;)

Are you sure this syntax is correct? I'm not sure what you expect to happen. Could you describe the problem you are having in more detail, along with the expected outcome. Just because you get no errors, doesn't automatically imply that your code is correct or well structured.

Edit

After looking again at this, I'm still none the wiser, however I spotted one mistake here:

For $cells = 1 To $ipArray
$ipArray is an array, and not an integer! This code will do very little, if anything at all.

the syntax is correct for pskill.

for instance it goes as:

pskill -t HOST processname/PID

so in autoit, it's

Run('&quot;C:\stuff\SysinternalsSuite\pskill.exe&quot; &quot;-t&quot;' &amp; $ipArray[$cells] &amp; &quot;notepad.exe&quot;)

essentially what i'm trying to do is pass one ip after another into the $ipArray[$cells] portion of that syntax.

so my commands would run as:

pskill - t HOST IN COLa ROW1

pskill - t HOST IN COLa ROW2

etc.

This part should change:

For $cells = 1 To $ipArray

Use Ubound to detimine how many or in your case you wanted just the first five lines from the spreadsheet so use five then instead of $ipArray.

Are you trying to kill the Notepad.exe process on all the IP adresses?

yes, I have a list of hostnames / ip address that I want to kill any given running process on.

I tried:

For $cells = 1 To 5

but no ips from the excel sheet went into that portion of the pskill syntax.

Edited by pixeldotz

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

May I suggest that you take all the #includes and put it at the top of your script?

Then try this:

#include <Excel.au3> ;Place these outside your your
#include <Process.au3>;funtion right at the
#include <Array.au3> ;top of your script
 
Func kill()
;$sFilePath1 = GUICtrlRead($Input1) ; takes the filepath from $Input$
$oExcel = _ExcelBookOpen($sFilePath1)
If @error = 1 Then
MsgBox(0, "Error!", "Unable to Create the Excel Object")
Exit
ElseIf @error = 2 Then
MsgBox(0, "Error!", "File does not exist - Shame on you!")
Exit
EndIf
$ipArray = _ExcelReadArray($oExcel, 1, 1, 5, 1, 1)
_ArrayDisplay($ipArray)
For $cells = 1 To 5
$console = Run("C:\stuff\SysinternalsSuite\pskill.exe -t" & $ipArray[$cells] & " notepad.exe", "", @SW_MAXIMIZE)
ConsoleWrite($ipArray[$cells] & " Error ===> " & @error & @CRLF)
Next
EndFunc ;==>kill

Please note the space before notepad.exe!

Edited by JoHanatCent

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

May I suggest that you take all the #includes and put it at the top of your script?

Then try this:

#include <Excel.au3> ;Place these outside your your
#include <Process.au3>;funtion right at the
#include <Array.au3> ;top of your script
 
Func kill()
;$sFilePath1 = GUICtrlRead($Input1) ; takes the filepath from $Input$
$oExcel = _ExcelBookOpen($sFilePath1)
If @error = 1 Then
MsgBox(0, "Error!", "Unable to Create the Excel Object")
Exit
ElseIf @error = 2 Then
MsgBox(0, "Error!", "File does not exist - Shame on you!")
Exit
EndIf
$ipArray = _ExcelReadArray($oExcel, 1, 1, 5, 1, 1)
_ArrayDisplay($ipArray)
For $cells = 1 To 5
$console = Run($console = Run("C:\stuff\SysinternalsSuite\pskill.exe -t" & $ipArray[$cells] & " notepad.exe", "", @SW_MAXIMIZE)
ConsoleWrite($ipArray[$cells] & " Error ===> " & @error & @CRLF)
Next
EndFunc ;==>kill

Please note the space before notepad.exe!

this seems to be going in the right direction :mellow:

i'm redoing the console line to just pass PING so it's not so cluttered. I'll change it back to the proper program and command once it's sorted out. That part is simple.

$console = Run("ping" & $ipArray[$cells], "", @SW_MAXIMIZE)

so using you're code is opening the xls file and displaying it for me, once i close the array display it runs the $console string but i get errors from PING saying it needs a hostname.

the autoit console window shows the following.

www.yahoo.com Error ===> 1
www.google.com Error ===> 1
www.cnn.com Error ===> 1
Error ===> 0
Error ===> 0

that's just what i have in my xls file right now as test sites for ping.

am i implementing this wrong?

Edited by pixeldotz

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

You seem to be going great.

So use this line for now:

$console = Run(@ComSpec & " /c " & "ping " & $ipArray[$cells], "", @SW_MAXIMIZE)

As for the error's see help file under the run command.

Failure: Returns 0 and sets @error to non-zero.

Global $ipArray[5] = ["Yahoo.com","cnn.com","google.com","iafrica.com","bing.com"]
kill()
Func kill()
For $cells = 0 To 4
$console = Run(@ComSpec & " /c " & "ping " & $ipArray[$cells], "", @SW_MAXIMIZE)
ConsoleWrite($ipArray[$cells] & " Error ===> " & @error & @CRLF)
Next
EndFunc ;==>kill
Edited by JoHanatCent

Share this post


Link to post
Share on other sites

You seem to be going great.

So use this line for now:

$console = Run(@ComSpec & " /c " & "ping " & $ipArray[$cells], "", @SW_MAXIMIZE)

As for the error's see help file under the run command.

Failure: Returns 0 and sets @error to non-zero.

Global $ipArray[5] = ["Yahoo.com","cnn.com","google.com","iafrica.com","bing.com"]
kill()
Func kill()
For $cells = 0 To 4
$console = Run(@ComSpec & " /c " & "ping " & $ipArray[$cells], "", @SW_MAXIMIZE)
ConsoleWrite($ipArray[$cells] & " Error ===> " & @error & @CRLF)
Next
EndFunc ;==>kill

i'll be trying this as soon as i get office installed on my home pc. just a question though: how come in that Global variable you are setting the sites like "yahoo". those are supposed to come from the excel spreadsheet correct?

as soon as i'm done installing i'll do some more testing.

thanks again for all your help.

Share this post


Link to post
Share on other sites

Correct.

I simply took a shortcut so that I don't have to set up an Excel spreadsheet etc.

It is also a lot easier if you post your complete (or working sample) code here so that we don't have to recreate what you describe. :mellow:


Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

Here's the entirety of the code. (almost put 'my code' but that's not right since i've gotten so much help :graduated:

I ran a persistant ping command, and it looks like it is running. Is there anyway to get it to run one after the other in a single cmd window?

That way I don't have like 100 cmds open when I finalize the script script ;).

again, thanks for all the help, reading a working array gives a lot of insight into how it's built. this is really helping me out.

I removed all references to my previous script. i had recycled the layout because i liked the way it was.

#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <Process.au3>
#include <GUIConstantsEx.au3>
#include <Constants.au3>
#include <Date.au3>
#include <File.au3>
#include <string.au3>
#include <Excel.au3>
#include <Array.au3>
 
#region ### START Koda GUI section ### Form=c:\documents and settings\pxl\my documents\dropbox\startstopcap.kxf
$MyDocsFolder = "::{450D8FBA-AD25-11D0-98A8-0800361B1103}"
$Form1_1 = GUICreate("pxldtz [pskill and kiosk frontend]", 720, 428, 295, 164)
$Button1 = GUICtrlCreateButton("kill powerpoints", 440, 56, 113, 33, $BS_FLAT)
;$Button2 = GUICtrlCreateButton("copy presentations", 600, 56, 113, 33, $BS_FLAT)
$Button3 = GUICtrlCreateButton("load list", 552, 56, 49, 33, $BS_FLAT)
;$Button4 = GUICtrlCreateButton("reboot machines", 440, 92, 273, 33, $BS_FLAT)
$Input1 = GUICtrlCreateInput("", 440, 32, 273, 21)
$Label1 = GUICtrlCreateLabel("selected file path", 440, 8, 228, 17)
$hEdit = GUICtrlCreateEdit("", 8, 8, 425, 377)
GUICtrlSetData(-1, "")
$Edit1 = GUICtrlCreateEdit("", 440, 160, 273, 225)
GUICtrlSetData(-1, "")
GUISetState(@SW_SHOW)
#endregion ### END Koda GUI section ###
 
Global $capd
Global $console
Global $console2
Global $aArray1
Global $sFilePath1 = GUICtrlRead($Input1)
Global $path = "1"
;==this entire section shows the output of the console into you're editbox
While 1
$data = StdoutRead($console)
If $data Then
  GUICtrlSetData($hEdit, $data, 1)
EndIf
 
$nMsg = GUIGetMsg()
Select
  Case $nMsg = $GUI_EVENT_CLOSE
   Exit
   ;===start capping
  Case $nMsg = $Button1
   kill()
 
   ;===filesave browser
  Case $nMsg = $Button3
   filebrowse()
EndSelect
WEnd
 
 
Func kill()
;$sFilePath1 = GUICtrlRead($Input1) ; takes the filepath from $Input$
$oExcel = _ExcelBookOpen(GUICtrlRead($Input1))
If @error = 1 Then
  MsgBox(0, "Error!", "Unable to Create the Excel Object")
  Exit
ElseIf @error = 2 Then
  MsgBox(0, "Error!", "File does not exist - Shame on you!")
  Exit
EndIf
$ipArray = _ExcelReadArray($oExcel, 1, 1, 5, 1, 1)
_ArrayDisplay($ipArray)
For $cells = 1 To 5
  $console = Run(@ComSpec & " /c " & "ping" & $ipArray[$cells], "", @SW_MAXIMIZE)
  ConsoleWrite($ipArray[$cells] & " Error ===> " & @error & @CRLF)
Next
 
Local $line
EndFunc   ;==>kill
 
Func filebrowse()
$capd = FileOpenDialog("Choose a name.", $MyDocsFolder, "excel sheet (*.xls; *.xlsx)", 2)
GUICtrlSetData($hEdit, "") ;This line erases the editbox
GUICtrlSetData($Input1, $capd) ;this sets the input box contents to whatever the $capd variable is holding
EndFunc   ;==>filebrowse
Edited by pixeldotz

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

JoHanatCent:

thanks for all the help. that single window thing isn't such a big deal - i'll look into it more later on. as it is i can add a

sleep (5000)

right before the end of the loop.

in case anyone else is interested, here's the remaning function i put together to use FILECOPY from the same spreedsheet.

Func copyto()
    $oExcel = _ExcelBookOpen(GUICtrlRead($Input1))
    $fileArray = _ExcelReadArray($oExcel, 10, 1, 5, 1, 1) ;- reads column 1, row 10 for the SOURCE
    $fileArray2 = _ExcelReadArray($oExcel, 10, 2, 5, 1, 1) ; - reads column 2 row 10 for the DESTINATION
    _ArrayDisplay($fileArray)
    _ArrayDisplay($fileArray2)
    For $cells = 1 to 5
    FileCopy($fileArray[$cells],$fileArray2[$cells],1) ; reads both arrays for SOURCE and DESTINATION
    Next
 
    ;ConsoleWrite($fileArray[$cells] &  $fileArray2[$cells] & " Error ===> " & @error & @CRLF)
EndFunc   ;==>copyto

ConsoleWrite spits out an error at the end, possibly because of the 2nd array - but everything runs as it should.

here's the attached excel sheet in case anyone wants to mess around with it for fun or whatever.

the top line is where the ip addresses that i will use pskill on will be pulled from.

test.7z

Edited by pixeldotz

Share this post


Link to post
Share on other sites

#12 ·  Posted (edited)

Glad you found some of it usefull.

I see you already discovered StdoutRead.

So maybe use:

While 1
$data = StdoutRead($console)
  If $data Then GUICtrlSetData($hEdit, $data, 1)
  If @error Then ExitLoop
        Sleep(10)
WEnd

instead of the sleep(5000) ?

Also remember we started to show the windows because you thought it was not working so now you can @SW_Minimize them.

Edited by JoHanatCent

Share this post


Link to post
Share on other sites

So maybe use:

While 1
$data = StdoutRead($console)
  If $data Then GUICtrlSetData($hEdit, $data, 1)
  If @error Then ExitLoop
        Sleep(10)
WEnd

instead of the sleep(5000) ?

You should check for error after StdOutRead()

While 1
   $data = StdoutRead($console)
   If @error Then ExitLoop
   If $data Then GUICtrlSetData($hEdit, $data, 1) 
   Sleep(10)
WEnd

Share this post


Link to post
Share on other sites

#14 ·  Posted (edited)

I'll try those out. Would this mean that the While loop will sleep for 10 microseconds, before continuing if there is an error?

Edited by pixeldotz

Share this post


Link to post
Share on other sites

What's the proper way of exiting the array loop? the script is running fine, but after running through all the ip addresses, the loop itself continues to run. So I get error msgs from pskill about "host computer" simply because it's trying to run without a host because it's already reached the end of the array.

Share this post


Link to post
Share on other sites

Use a for - next loop for arrays.

For $i = 0 to ubound($array)-1
;code
Next

Share this post


Link to post
Share on other sites

Beege:

I'll give that a try.

Again, thanks for all the help you guys.

Share this post


Link to post
Share on other sites

Beege:

I'll give that a try.

Again, thanks for all the help you guys.

If you use UBound on the result of your _ExcelReadArray then you know how many loops you need?

Share this post


Link to post
Share on other sites

#19 ·  Posted (edited)

I decided not to use Ubound.

the regular for loop stops after a certain time (in this case 22). i just never noticed it stopping since i was testing with a few ips at first even though i had a high number of cell count in the for loop.

For $cells = 1 To 22
Edited by pixeldotz

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