Excel: Copy to clipboard or to array?

[tuffgong]

Good Morning, this is my first foray into the world of spreadsheets and arrays (at least with AutoIT) and some help would be appreciated. What I need to do seems fairly simple. A user will scan a long (length will vary) list of numbers into a spreadsheet (no column headers, no range, just a list of numbers on a previously blank worksheet).

When they execute, the script should take the list of numbers, add a prefix to each number, paste the list into notepad, and save the list to a network location with a .bch extension.

So the user will capture a series of 10 digit letters and numbers in Excel:

10digitdatastring

The script will copy every data string in every cell, add a prefix to each, and paste into notepad as a list in column form:

<!prefix>, 10digitdatastring

My first question is this: will this require an array or can the _Excel_RangeCopyPaste function be modified to paste into Notepad with a standard, unchanging prefix in this way?

 

Thank You!

Edited April 8, 2016 by tuffgong

[water]

_Excel_RangeCopyPaste will not work with notepad, it was designd for Excel to copy/paste data from/to Excel.
But why do you need to use Notepad at all? Why not use FileWrite/FileWriteLine to directly write the data to disk?

[tuffgong]

Good question, and thanks for the response. I should include more details. I am trying to automate the printing of bar codes. The manual process is this:

1. Scan bar codes into Column B of a spreadsheet.

2. Column C of the spreadsheet has a formula that combines a standard text string from Column A with the number scanned into Column B ( Col C = text from Col A + number from Col B )

3. Copy the new, concatenated text from Column C into notepad, and save as a .bch file

4. When this .bch file is saved into a certain folder the bar code printer then prints individual bar codes on stickers

The text prefix from column A tells the bar code printer "I am sending you text to print", then it prints the text from column B.

I want to do it without the current spreadsheet and formula. I would like to have the user just scan their numbers into the spreadsheet and hit an .exe which will do the following: take their numbers from the spreadsheet, add the small text string as a prefix and paste to notepad, save the notepad file as a .bch in the appropriate folder.

My lack of knowledge of Arrays is slowing me down. Here is what I have:

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

Local $stcArray = _Excel_RangeRead ("STCBarcode", "Sheet1", 3)

$notePad = Run("notepad.exe")
ProcessWait($notePad)

Send("{!}StaticShelving1x3, ", $stcArray[1])

Obviously it does not work and I will need to construct a loop based on the number of cells the user has filled on the sheet.

Do I have to declare the size of the array in line 4? How do I know how many cells the user has filled in on the sheet? Do I just create an array with 100 elements and tell the user that is the limit?

What is the proper function to get cell contents into the array? My statement above does not seem to work.

Edited April 8, 2016 by tuffgong

[water]

I would try somthing like this:

#include <Array.au3>
#include <Excel.au3>
Global $sFilename = "Test.bch"
Global $oWorkbook = _Excel_BookAttach(...) ; Connect to the already open workbook. Parameters depend on the way you want to attach
Local $sSTCArray = _Excel_RangeRead($oWorkbook, "Sheet1", "3:3")
For $i = 0 to UBound($sSTCArray, 1) - 1
    FileWriteLine($sFilename, "{!}StaticShelving1x3, ", $sSTCArray[$i])
Next