Array from Excel Column

[JailDoctor]

I need to get an array from column F of "MY Test List.xls" file.

I need all the numbers available in the column. Not just up to cell F100. ( I used 100 as a limit because usually there are less than 100 entries).

There are several blank rows between the numbers. I only need the cells that have numbers not the blanks.

This is the code I have so far.

#include"ExcelCom.au3"

#include"Array.au3"

$XLFilePath=@DesktopDir & ("\My Test List.xls")

$XLArray=_XLColumnToArray( $XLFilePath,1, "F1:F100")

I haven't found a way to eliminate all the "empty" entries in the array or to select the entire row.

Any help will be welcomed.

[PsaltyDS]

I need to get an array from column F of "MY Test List.xls" file.

I need all the numbers available in the column. Not just up to cell F100. ( I used 100 as a limit because usually there are less than 100 entries).

There are several blank rows between the numbers. I only need the cells that have numbers not the blanks.

This is the code I have so far.

#include"ExcelCom.au3"

#include"Array.au3"

$XLFilePath=@DesktopDir & ("\My Test List.xls")

$XLArray=_XLColumnToArray( $XLFilePath,1, "F1:F100")

I haven't found a way to eliminate all the "empty" entries in the array or to select the entire row.

Any help will be welcomed.

Walk through the array with a For/Next loop in reverse order, and you can use _ArrayDelete() or any other technique to remove blank items.

You could also just walk it forward if you are copying the non-blank items to somewhere else.

Keep in mind that detecting "non-blank" means no spaces, etc., so I like to hit it with StringStripWS() to be sure.

[JailDoctor]

Walk through the array with a For/Next loop in reverse order, and you can use _ArrayDelete() or any other technique to remove blank items.

You could also just walk it forward if you are copying the non-blank items to somewhere else.

Keep in mind that detecting "non-blank" means no spaces, etc., so I like to hit it with StringStripWS() to be sure.

[JailDoctor]

I tried the following with no success:

CODE

$aStringFromArray = _ArrayToString ($XLArray,@CR)

StringStripWS($aStringFromArray,8)

_ArrayDisplay( $aStringFromArray, "No Blanks" )

Then I tried without success:

CODE

If @error Then Exit

$Pos = _ArraySearch ($XLArray, "", 0, 0, 0, True)

Select

Case $Pos = -1

MsgBox(0, "Not Found", '"' & "Blank" & '" was not found in the array.')

Case Else

MsgBox(0, "Found", '"' & "Blank" & '" was found in the array at pos ' & $Pos & ".")

EndSelect

Then I though perhaps a binary search would do because after all they are numbers. No cigar.

CODE

$iKeyIndex = _ArrayBinarySearch ( $XLArray,[blank],1 )

If Not @error Then

Msgbox(0,'Entry found',' Index:' & $iKeyIndex)

Else

Msgbox(0,'Entry Not found',' Error:' & @error)

EndIf

_ArrayDelete( $XLArray,$iKeyIndex)

WEnd

When I entered the Array in MS Word, it showed me that all the entries are separated by paragraph marks. Two paragraphs between each entry. Just like it is in the spreadsheet. Then paragraph marks continue 78 times more to complete 93 entries.

I couldn't figure out how to use the "Blank" keyword that appeared when doing the binary search.

Is there a way to obtain the "value" of each cell in the column and delete the empty cell?

Please include some sample code.

Thanks.

[PsaltyDS]

I was thinking more along these lines - Two ways to remove blank elements in an array:

#include <array.au3>

; Assuming a valid 1D $XLArray already exists

; Remove empty elements from the array
For $n = UBound($XLArray) - 1 To 0 Step -1
    If StringStripWS($XLArray[$n], 8) = "" Then _ArrayDelete($XLArray, $n)
Next
_ArrayDisplay($XLArray, "$XLArray: Blank elements removed")

; Copy non-blank elements somewhere else
Global $avNewArray[1] ; element [0] not used
For $n = 0 To UBound($XLArray) - 1
    If StringStripWS($XLArray[$n], 8) <> "" Then _ArrayAdd($avNewArray, $XLArray[$n])
Next
_ArrayDisplay($avNewArray, "$avNewArray: Blank elements removed")

Edit: Forgot to put "Global" in front of array declaration for $avNewArray.

Edited March 21, 2008 by PsaltyDS

[JailDoctor]

$avNewArray[1] ; element [0] not used

For $n = 0 To UBound($XLArray) - 1

Worked like a charm to remove the blanks. Could not use the second part of your code due to an error in line 12

$avNewArray[1]

Subscript used with non-Array Variable.

[PsaltyDS]

Worked like a charm to remove the blanks. Could not use the second part of your code due to an error in line 12

$avNewArray[1]

Subscript used with non-Array Variable.

Oops. Forgot to put a declaration statement in front of the array declaration for $avNewArray. It's fixed now. Either technique should work for you. The choice between them is if you want to modify the original array (first method) or create a new array (second method).

[JailDoctor]

Once I get the "blank free" array I write it to a txt file and get first a CR and then the list of numbers.

CODE

$sFile = @DesktopDir & ("\My Test List.txt")

; Write first array to file by string file name

_FileWriteFromArray($sFile, $XLArray, 1)

; Display results

Run("notepad.exe " & $sFile)

Any ideas why this is happening?

Edited March 26, 2008 by JailDoctor

[JailDoctor]

I added the following and it removes that first empty line, but still, it doesn't feel rigth.

#include"ExcelCom.au3"
#include"Array.au3"
#include"File.au3"
$XLFilePath=@DesktopDir & ("\My Test List.xls")
$XLArray=_XLColumnToArray( $XLFilePath,1, "F8:F500")
_ArrayDelete( $XLArray,0)
; Remove blank elements
For $n = UBound($XLArray) - 1 To 0 Step -1
    If StringStripWS($XLArray[$n], 8) = "" Then _ArrayDelete($XLArray, $n)
Next
_XLExit($XLFilePath)
$sFile = @DesktopDir & "\My Test List.txt"
; Write first array to file by string file name
_FileWriteFromArray($sFile, $XLArray, 0)

Run("notepad.exe " & @DesktopDir & ("\My Test List.txt"))
WinWait("My Test List - Notepad","")
If Not WinActive("My Test List - Notepad","") Then WinActivate("My Test List - Notepad","")
WinWaitActive("My Test List - Notepad","")
Send ("{Delete}")
Send ("{CTRLDOWN}s{CTRLUP}")
Send ("{ALTDOWN}{F4}{ALTUP}")

There must be something I'm missing when sending the array to a txt file resulting in a double CR at the first line. Edited March 27, 2008 by JailDoctor

[JailDoctor]

Once the array is fixed, there seems to be an instance of Excel running in the background. How do I terminate it?

[PsaltyDS]

Once I get the "blank free" array I write it to a txt file and get first a CR and then the list of numbers.

CODE

$sFile = @DesktopDir & ("\My Test List.txt")

; Write first array to file by string file name

_FileWriteFromArray($sFile, $XLArray, 1)

; Display results

Run("notepad.exe " & $sFile)

Any ideas why this is happening?

My fault.

That is a bug in the _FileWriteFromArray() function in 3.2.10.0 Production. I updated the _FileWriteFromArray() UDF during the 3.2.9.x Betas to use file handles as an option. This was adopted by the Devs, but it introduced this bug, which was missed in testing. It has since been fixed and if you run the 3.2.11.x Beta version there is no stray blank line.

Edit: Edit for clarity.

Edited March 27, 2008 by PsaltyDS

[JailDoctor]

Thank you.

How do I end the Excel.exe that remains running in the background after cleaning out the blank cells in the spreadsheet?

[PsaltyDS]

Thank you.

How do I end the Excel.exe that remains running in the background after cleaning out the blank cells in the spreadsheet?

Not sure. I don't use ExcelCOM.au3 (I use ExcelCOM_UDF.au3 by Locodarwin), but I'm sure there's something in there like _XLQuit() or some such...

[JailDoctor]

Not sure. I don't use ExcelCOM.au3 (I use ExcelCOM_UDF.au3 by Locodarwin), but I'm sure there's something in there like _XLQuit() or some such...

I downloaded I guess a newer version of ExcelCOM.au3 by Locodarwin and it works fine. But if you open the task manager, Execl.exe is still running and I need to click the End Process button.

If I don't do this, an instance of My Test List.xls is opened somewhere and everything slows down to crawl, even the mouse is locked into a dragging setting.

[PsaltyDS]

I downloaded I guess a newer version of ExcelCOM.au3 by Locodarwin and it works fine. But if you open the task manager, Execl.exe is still running and I need to click the End Process button.

If I don't do this, an instance of My Test List.xls is opened somewhere and everything slows down to crawl, even the mouse is locked into a dragging setting.

I'm not experiencing that, and you still don't have the name right, the one by Locodarwin is ExcelCOM_UDF.au3, not ExcelCOM.au3.

You seem to be talking about randallc's ExcelCOM.au3, where randallc himself says this:

** I am recommending people consider LocoDarwin's (Yet Another) ExcelCOM UDF becuse i think it will ultimately do all these functions and get to standard UDF; Not all there yet, but some features are better already.

That UDF has not been updated since October, 2006.

[JailDoctor]

You seem to be talking about randallc's ExcelCOM.au3, where randallc himself says this:That UDF has not been :(updated since October, 2006.

Ops, typo, I meant ExcelCOM_UDF.au3, the " yet another..."

[everseeker]

Ops, typo, I meant ExcelCOM_UDF.au3, the " yet another..."

in that case, _excelBookClose($oExcel, 1) should do the trick

[JailDoctor]

#include"ExcelCom_UDF.au3"
#include"Array.au3"
$XLFilePath=@DesktopDir & ("\My Test List.xls")
$XLArray=_XLColumnToArray( $XLFilePath,1, "F8:F500"); This is the function from ExcelCom.au3 to get the
                                ; F column values into $XLArray.
_ArrayDelete( $XLArray,0) ; this removes an unknown number that appears in element number 0 
                                    ;something related to the number of columns
For $n = UBound($XLArray) - 1 To 0 Step -1
    If StringStripWS($XLArray[$n], 8) = "" Then _ArrayDelete($XLArray, $n)
Next
_excelBookClose($oExcel, 1); Thanks, this closed Excel.
_ArrayDisplay($XLArray, "$XLArray: Blank elements removed")

I get an error: Unknown Function name $XLArray=_XLColumnToArray($XLFilePath,1, "F8:F500")

What is the equivalent command to convert an Excel colum into an array in ExcelCom_UDF.au3?

Edited March 28, 2008 by JailDoctor

[PsaltyDS]

#include"ExcelCom_UDF.au3"
#include"Array.au3"
$XLFilePath=@DesktopDir & ("\My Test List.xls")
$XLArray=_XLColumnToArray( $XLFilePath,1, "F8:F500"); This is the function from ExcelCom.au3 to get the
                            ; F column values into $XLArray.
_ArrayDelete( $XLArray,0); this removes an unknown number that appears in element number 0 
                                ;something related to the number of columns
For $n = UBound($XLArray) - 1 To 0 Step -1
    If StringStripWS($XLArray[$n], 8) = "" Then _ArrayDelete($XLArray, $n)
Next
_excelBookClose($oExcel, 1); Thanks, this closed Excel.
_ArrayDisplay($XLArray, "$XLArray: Blank elements removed")

I get an error: Unknown Function name $XLArray=_XLColumnToArray($XLFilePath,1, "F8:F500")

What is the equivalent command to convert an Excel colum into an array in ExcelCom_UDF.au3?

Try this:

#include <ExcelCOM_UDF.au3>
#include <array.au3> ; Only for _ArrayDisplay()

Global $XLFilePath = @DesktopDir & "\My Test List.xls"
Global $oExcel, $XLArray

$oExcel = _ExcelBookOpen($XLFilePath)
; $XLArray=_XLColumnToArray($XLFilePath,1, "F8:F500")
; Read from Row 8, Col 6 (F), for 493 cells (to 500), direction = down (vice right)
$XLArray = _ExcelReadArray($oExcel, 8, 6, 493, 1)
_ArrayDisplay($XLArray, "Debug: $XLArray")
_ExcelBookClose($oExcel)

[JailDoctor]

PsaltyDS,

I'm sorry for being a pest but now that I upgraded to the beta version I get these two errors:

#include <ExcelCOM_UDF.au3>

#include <array.au3>

#include <adfunctions.au3>

The above are included:

Next are the errors.

H:\My Documents\IDXStuf.au3(610,10) : ERROR: can't open include file <adfunctions.au3>

#include <adfunctions.au3>

~~~~~~~~~^

H:\My Documents\IDXStuf.au3(626,39) : ERROR: _FileWriteFromArray(): undefined function.

_FileWriteFromArray($sFile, $XLArray,1)

Edited April 1, 2008 by JailDoctor