Need help with creating individual batch files from spreadsheet

[coraxal]

Hello - I'm totally new to scripting and to AutoIt (Great tool by the way!...thx to everyone who's work on it)

The problem that I'm trying to solve is to generate a unique batch file to perform a specific task against a computer on my network (i.e. pinging). The list of computers is in an Excel file in column A, and the length of this list will vary as these are report extracts from a different tool.

So to summarize, I have a "source.xls" file that contains varying date in the following format:

A

1 Computer 1

2 Computer 2

3 Computer 3

4 ....

The objective is to create an individual file for each computer listed in "sources.xls" such that the batch file looks something like this:

<line 1> cd "C:\myfolder"

<line 2> ping \\<Computer n>

<line 3> pause

Steps:

1. Open source.xls, which is saved in C:\myfolder.

2. Read column A and determine how many cells contain data.

3. Use value determined in step # 2 for a loop to create each individual file.

4. The creation of the file will create three lines of text and will insert the value of A1, save as .bat file, and close it. Move on to the next record.

5. I would then create a different file to run all of those batch files in sequential order.

Again, I apologize for being new and not skilled in scripting but any help would be appreciated. Here's the code that I've been able to put together so far:

----------------------------------------------------------------------

#include <WinAPI.au3>

#include <Excel.au3>

Global $sFile, $hFile, $sText, $nBytes, $tBuffer

$sFilePath1 = @ScriptDir & "\source.xls" ;This file should already exist

$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

For $i = 1 To 39 ;Loop (I dont know what type of loop would be best for what I'm trying to do...or if an array would be better. As of now, I'm putting the number of cells with data manually (39))

$sCellValue = _ExcelReadCell($oExcel, $i, 1)

MsgBox(0, "Value of $i is:", $sCellValue)

$i = $i + 1

Next

; 1) create file and write data to it (This is not working for me...I simply want to write more than line of text to the file and just use a variable to bring the data in cell A1..A2...etc.

$sFile = @ScriptDir & '\test.bat'

$sText = '<line 1> cd "C:\myfolder"'

$sText = '<line 2> ping \\<Computer n> '

$sText = '<line 3> pause'

$tBuffer = DllStructCreate("byte[" & StringLen($sText) & "]")

DllStructSetData($tBuffer, 1, $sText)

$hFile = _WinAPI_CreateFile($sFile, 1)

_WinAPI_WriteFile($hFile, DllStructGetPtr($tBuffer), StringLen($sText), $nBytes)

_WinAPI_CloseHandle($hFile)

ConsoleWrite('1) ' & FileRead($sFile) & @LF)

[KaFu]

Why so complicated? Export/Copy computers to computers.txt and adjust this code to your needs:

#include <file.au3>
Dim $aRecords
If Not _FileReadToArray("computers.txt",$aRecords) Then
   MsgBox(4096,"Error", " Error reading log to Array     error:" & @error)
   Exit
EndIf

$output = FileOpen("Batch.bat",2)

For $x = 1 to $aRecords[0]
    FileWriteLine($output,'cd "C:\myfolder"')
    FileWriteLine($output,'ping \\' & $aRecords[$x] )
    FileWriteLine($output,'pause')
next

FileClose($output)

[coraxal]

Why so complicated? Export/Copy computers to computers.txt and adjust this code to your needs:

#include <file.au3>
Dim $aRecords
If Not _FileReadToArray("computers.txt",$aRecords) Then
   MsgBox(4096,"Error", " Error reading log to Array     error:" & @error)
   Exit
EndIf

$output = FileOpen("Batch.bat",2)

For $x = 1 to $aRecords[0]
    FileWriteLine($output,'cd "C:\myfolder"')
    FileWriteLine($output,'ping \\' & $aRecords[$x] )
    FileWriteLine($output,'pause')
next

FileClose($output)

Thank you for your reply. I've gone ahead and adjusted it and I'm able to create the "Batch.bat" file with the code in it that I need. Could you help me create an individual file for each computer name in that text file? In other words, I need some type of loop that creates a file for the first computer and saves it as file1.bat...the next as file 2.bat....right now, the output shows all of my computers into a single file....

-Thanks

[coraxal]

Thank you for your reply. I've gone ahead and adjusted it and I'm able to create the "Batch.bat" file with the code in it that I need. Could you help me create an individual file for each computer name in that text file? In other words, I need some type of loop that creates a file for the first computer and saves it as file1.bat...the next as file 2.bat....right now, the output shows all of my computers into a single file....

-Thanks

What I'm looking to do is use something (i.e. array number) to create the file name....something like this

For $x = 1 to $aRecords[0]

$output = FileOpen($aRecords[$x]."bat",2)

FileWriteLine($output,'cd "C:\myfolder"')

FileWriteLine($output,'something')

FileWriteLine($output,'something')

FileClose($output)

next

However, as expected the FileOpen is not working because of the wrong syntax....anybody have any suggestions as to how I can generate and save a different with a different file name?

[KaFu]

Just a little adjustment (presumes that the computernames dont contain characters violating file-name conventions).

#include <file.au3>
Dim $aRecords
If Not _FileReadToArray("computers.txt",$aRecords) Then
   MsgBox(4096,"Error", " Error reading log to Array     error:" & @error)
   Exit
EndIf

For $x = 1 to $aRecords[0]
    $output = FileOpen("Batch_" & $aRecords[$x] & ".bat",2)
    FileWriteLine($output,'cd "C:\myfolder"')
    FileWriteLine($output,'ping \\' & $aRecords[$x] )
    FileWriteLine($output,'pause')
    FileClose($output)
next

[coraxal]

Just a little adjustment (presumes that the computernames dont contain characters violating file-name conventions).

#include <file.au3>
Dim $aRecords
If Not _FileReadToArray("computers.txt",$aRecords) Then
   MsgBox(4096,"Error", " Error reading log to Array     error:" & @error)
   Exit
EndIf

For $x = 1 to $aRecords[0]
    $output = FileOpen("Batch_" & $aRecords[$x] & ".bat",2)
    FileWriteLine($output,'cd "C:\myfolder"')
    FileWriteLine($output,'ping \\' & $aRecords[$x] )
    FileWriteLine($output,'pause')
    FileClose($output)
next

Thank you SOOO much!!! that did it!.....now, if it's not too much to ask....is there an easy way to execute each of this files via a script? That's my next step...to figure that piece. Thanks again.

[KaFu]

#include <file.au3>
Dim $aRecords
If Not _FileReadToArray("computers.txt",$aRecords) Then
   MsgBox(4096,"Error", " Error reading log to Array     error:" & @error)
   Exit
EndIf

For $x = 1 to $aRecords[0]
    $output = FileOpen("Batch_" & $aRecords[$x] & ".bat",2)
    FileWriteLine($output,'cd "C:\myfolder"')
    FileWriteLine($output,'ping \\' & $aRecords[$x] )
    FileWriteLine($output,'pause')
    FileClose($output)
next

$output = FileOpen("_Start_all_Batch-Files.bat",2)
For $x = 1 to $aRecords[0]
    FileWriteLine($output,"Batch_" & $aRecords[$x] & ".bat")
next
FileClose($output)

[coraxal]

#include <file.au3>
Dim $aRecords
If Not _FileReadToArray("computers.txt",$aRecords) Then
   MsgBox(4096,"Error", " Error reading log to Array     error:" & @error)
   Exit
EndIf

For $x = 1 to $aRecords[0]
    $output = FileOpen("Batch_" & $aRecords[$x] & ".bat",2)
    FileWriteLine($output,'cd "C:\myfolder"')
    FileWriteLine($output,'ping \\' & $aRecords[$x] )
    FileWriteLine($output,'pause')
    FileClose($output)
next

$output = FileOpen("_Start_all_Batch-Files.bat",2)
For $x = 1 to $aRecords[0]
    FileWriteLine($output,"Batch_" & $aRecords[$x] & ".bat")
next
FileClose($output)

Thank you again for the reply. This sort of worked...I mean it does create a single batch file with all the names of the individual files...however, for my purpose I need each batch file to start in its own cmd window....if I run the single batch file, it will call the first batch file...then wait, then launch the second one....what I want is a bunch of cmd windows running all batch files simultaneously....any idea as to how to do that? Thanks again!

[KaFu]

#include <file.au3>
Dim $aRecords
If Not _FileReadToArray("computers.txt",$aRecords) Then
   MsgBox(4096,"Error", " Error reading log to Array     error:" & @error)
   Exit
EndIf

For $x = 1 to $aRecords[0]
    $output = FileOpen("Batch_" & $aRecords[$x] & ".bat",2)
    FileWriteLine($output,'cd "C:\myfolder"')
    FileWriteLine($output,'ping \\' & $aRecords[$x] )
    FileWriteLine($output,'pause')
    FileClose($output)
next

For $x = 1 to $aRecords[0]
    run("Batch_" & $aRecords[$x] & ".bat")
next

[coraxal]

Thank you for all the help!!!

[KaFu]

Your welcome , i'm just watching 24 Season 2 and switching to the forum from time to time ...

[coraxal]

Thank you for all the help!!!

One last question...assume that the command line command that I'm running does not have a switch to pipe the output to a log/text file. Is there a way that I can collect the results of all these screens and dump them into a log/text file?

[KaFu]

I'm not sure, but can`t you pipe the result in DOS with addition of something like

FileWriteLine($output,'ping \\' & $aRecords[$x] & ' > ' & $aRecords[$x] & '_result.txt')

?

Edited April 9, 2009 by KaFu

[coraxal]

I'm not sure, but can`t you pipe the result with addition to the DOS command like e.g. run('ping \\' & $aRecords[$x] & ' > ' & $aRecords[$x] & '_result.txt') ?

I know what you mean, but unfortunately I'm using PSEXEC and I can't seem to find a way to pipe the results to a text file....hence the individual batch files for each computer system...at least running it individually I can see the results on the screen....but it would be much nicer/cleaner/HELPFUL if the results were piped to a text file that I could look at! Thanks for trying though and all of your help.