Need help with excel script

[WesleyG]

Hi All,

I require something that can

1. copy data from an excel sheet (hostname)
2. paste that data along with other text into the start menu (hostname\c$)
3. copy, paste a software in above location
4. run a command string with (2)+some commands, to install the software in (3)
5. repeat (1) with next hostname in the list

unfortunately for me all i know about writing an autoit software consists of google and copy paste, was unable to get past part 1.

can anyone help somehow? or point me to resources that is simple to understand?

 

Thanks in advance!

[Earthshine]

you have a nice pseudo code going. now develop each line into AutoIt code by using the included Help file. The Help file has MANY examples you can just click on the links to load the code into the SciTe editor so you can run them. that is the best way for me to learn. read the help file, walk through the examples.

Feel free to post your script so others can look at it and comment as well. Thanks. If you post your script, please use the Code Tags <> and paste code in there. It will be readable that way.

Edited January 10, 2018 by Earthshine

[BigDaddyO]

_Excel_RangeRead will get you #1, there is a nice example on how to use it in the help file.

#2/3 = FileCopy()

#4 = RunWait, or ShellExecuteWait

[Earthshine]

17 hours ago, BigDaddyO said:

_Excel_RangeRead will get you #1, there is a nice example on how to use it in the help file.

#2/3 = FileCopy()

#4 = RunWait, or ShellExecuteWait

If I could give you an Internet Cookie I would. Great answer!

Edited January 11, 2018 by Earthshine

[WesleyG]

Hello guys! Thanks for the guidance, took me a whole lot of googling... had trouble understanding the help files.

This is what I managed to come up with so far...

expandcollapse popup

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

; *****************************************************************************
; Open Excel
; *****************************************************************************

Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Test.xlsx")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

; *****************************************************************************
; Read data from a single cell on the active sheet of the specified workbook
; *****************************************************************************

Do ;Repeat till hostnames run out on the list
Local $hostname = _Excel_RangeRead($oWorkbook, Default, "A1")       ;how do I make "A1" increase to A2,A3,A4... and so on everytime it loops back here?

; *****************************************************************************
; Paste data in start menu to go to network drive.
; *****************************************************************************

Send ("{LWIN}")
Sleep ( 200 )
Send ("\\" & $hostname & "\C$")
Sleep ( 500 )
Send ("{ENTER}")
Sleep ( 5000 )

; *****************************************************************************
; Check if drive is online
; *****************************************************************************

If WinExists ("Network Error") Then
   Exit     ;how do i go back to line 24 and add an error code to I1, I2, I3... and so on instead of exiting?

ElseIf WinExists ("C$") Then
   FileCopy("C:\Users\SuperPC\Desktop\Test.txt", "\\" & $hostname & "\C$", 9) ; I have no idea why this does not work...
EndIf
   
;I think I know how to add #4 here now, just dont have the commands yet.

Until $hostname = ""

Any help greatly appreciated

Thanks!

Edited January 11, 2018 by WesleyG

[water]

Use

Local $hostname = _Excel_RangeRead($oWorkbook, Default)
For $i = 0 To UBound($hostname, 1) - 1
   ; Process $hostname[$i] here
Next

Remember: $hostname is an array when reading all cells in one go.

[WesleyG]

5 minutes ago, water said:

Use

Local $hostname = _Excel_RangeRead($oWorkbook, Default)
For $i = 0 To UBound($hostname, 1) - 1
   ; Process $hostname[$i] here
Next

Remember: $hostname is an array when reading all cells in one go.

Something like this?

; *****************************************************************************
; Read data from a single cell on the active sheet of the specified workbook
; *****************************************************************************

Do ;Repeat till hostnames run out on the list
Local $hostname = _Excel_RangeRead($oWorkbook, Default, "A1")
For $i = 0 To UBound($hostname, 1) - 1
   ; Process $hostname[$i] here
Next

Sorry, I don't understand how to use or where to put this...

Could you elaborate a little?

 

Thanks!

Edited January 11, 2018 by WesleyG

[benners]

To get the column into an array try this. This doesn't always return only the filled cells as it returns cells that have had a value at anytime, so when looping, check for blank strings

$as_HostName = _Excel_RangeRead($oWorkbook, Default, $oWorkBook.ActiveSheet.Usedrange.Columns('A'))

 

[water]

Quite easy:
Replace

Do ;Repeat till hostnames run out on the list
Local $hostname = _Excel_RangeRead($oWorkbook, Default, "A1")       ;how do I make "A1" increase to A2,A3,A4... and so on everytime it loops back here?

with the code I posted above.
Put everything you want to do with a host into a function (e.g.  _ProcessHost).
The loop I posted

For $i = 0 To UBound($hostname, 1) - 1
   ; Process $hostname[$i] here
Next

processes host by host from your Excel workbook.

To put it all together:

Local $aHostname = _Excel_RangeRead($oWorkbook, Default)
For $i = 0 To UBound($aHostname, 1) - 1
   _ProcessHost($aHostname[$i])
Next

Func _ProcessHost($sHost)
  ; Do what you need to do for a single host here. The hostname is stored in variable $sHost
EndFunc