Excel Question

[Bert]

I know this up Randallc's ally, but I know he is busy, and I hope someone may know the answer somewhat easy.

I used this code to get the locations of the cells I need.

#include<ExcelCOM.au3>
$ReadXLPath = @ScriptDir&"\bin\USD_Configuration_Items.xls"   
$valu = "BACK UP UNIT"
$s_FoundList=_XLSearch($ReadXLPath,1,$valu,0)

if $s_FoundList<> "Nothing" then
    $a_ArrayAnswer=StringSplit($s_FoundList,"|")
    _ArrayDisplay($a_ArrayAnswer,"test window")
;   run("Notepad.exe")
;   Sleep(2000)
;   send($s_FoundList) ;this  will give cell locations
    _XLExit($ReadXLPath);, "NOSave") 
Else
    msgbox(0,"","$s_FoundList="&$s_FoundList)
EndIf

What I need to do next is to use the collected information to get the information I need off the spread sheet.

When I run the code above, I get the following results:

[0]= 2

[1] = $B$3

[2] = $B$4

[3] = $B$5

From this information, I know I now need to read C3, C4, and C5. Items listed in Column B help me find what I need in column C.

Reading from arrays is not my strong suit, so I do not know how to write the code to get what I need from Column C. Depending on what $valu is, the items listed could be 5000 items long. I need the return to be one string, with a "|" separating the entries. Example: "item1|item2|item3"

If I could be pointed in the right direction, or someone could lend a snippit of code, that would be great. I've been trying to solve this problem for a while now, and I'm somewhat lost.

[Bert]

bump

[randallc]

Hi, Sorry I am busy;

similar thread;

http://www.autoitscript.com/forum/index.ph...ndpost&p=166695

would that help?

someone also posted your problem previously, but it should be easy enough to do the array anyway.

best, Randall

[Bert]

I looked at the post, and honestly I'm somewhat confused by it. Do I review what Smoke did, or what Strate did?

I assume I would use _XLColumnToArray, but what I'm not sure on is how to get the information I gathered into this line correctly. Is it possible I could get a example?

[tonedeaf]

I guess you're trying to access the value from the next cell to the right. So, if the value in $a_ArrayAnswer is "$B$3" then you want to read "$C$3". This code snippet will advance the cell column by one. Rest of the job is to simply read the value.

#include<ExcelCOM.au3>
$ReadXLPath = @ScriptDir&"\bin\USD_Configuration_Items.xls"   
$valu = "BACK UP UNIT"
$s_FoundList=_XLSearch($ReadXLPath,1,$valu,0)

if $s_FoundList<> "Nothing" then
    $a_ArrayAnswer=StringSplit($s_FoundList,"|")
    _ArrayDisplay($a_ArrayAnswer,"test window")
    
    For $i = 0 to UBound($a_ArrayAnswer) - 1
        $sNextCellToTheRight = "$" & Chr(Asc(StringMid($a_ArrayAnswer[$i], 2, 1)) + 1) & "$" & StringRight($a_ArrayAnswer[$i], 1)
    ; Read the $sNextCellToTheRight from the excel sheet
    ;---------<Your code goes here>---------------
    Next
    
;   run("Notepad.exe")
;   Sleep(2000)
;   send($s_FoundList);this  will give cell locations
    _XLExit($ReadXLPath);, "NOSave") 
Else
    msgbox(0,"","$s_FoundList="&$s_FoundList)
EndIf

[Bert]

Ok, I think I understand how the first part works, but the other part I'm lost on. How do I make it so it will read each cell and post that information in a string like the following example: "item1|item2|item3"

This would be the script finding 3 cells that match the search criteria.

I know how to do one cell, but how do I get it to put all of them I find in 1 string?

[randallc]

OK, try;[assuming only <=1000 rows?; else change range line]

PS Add ExcelExit if you don't need Excel open

;_XLArrayHelp.au3

#include"ExcelCom.au3"

$s_FilePath="c:\winword\Excel\exampleTree.xls"

$XLArray=_XLArrayRead($s_FilePath,1,"B1:C1000");&$i_LastRow-1)

;_XLshow($s_FilePath,1)

;MsgBox(0,"","$s_FilePath="&$s_FilePath)

_XLClose($s_FilePath,1)

local $_MAINFRAME_CONTROL_UNIT, $MAINFRAME_FRONT_END_PROCESSOR,$MAINFRAME_TIMER_DIRECTORS,$MICROFILM_VIEWER

local $MOBILE_TECHNOLOGY_AIR_CARD,$MOBILE_TECHNOLOGY_BLACKBERRY,$MOBILE_TECHNOLOGY_PDA

;==============================================================================

local $s_StringOfSingleLine

for $i=0 to ubound ($XLArray,2)-1

if $XLArray[0][$i]<>"" then

if $XLArray[0][$i]="MAINFRAME.CONTROL UNIT" then $_MAINFRAME_CONTROL_UNIT&="|"&$XLArray[1][$i]

if $XLArray[0][$i]="MAINFRAME.FRONT END PROCESSOR" then $MAINFRAME_FRONT_END_PROCESSOR&="|"&$XLArray[1][$i]

if $XLArray[0][$i]="MAINFRAME.TIMER/DIRECTORS" then $MAINFRAME_TIMER_DIRECTORS&="|"&$XLArray[1][$i]

if $XLArray[0][$i]="MICROFILM VIEWER" then $MICROFILM_VIEWER&="|"&$XLArray[1][$i]

if $XLArray[0][$i]="MOBILE TECHNOLOGY.AIR CARD" then $MOBILE_TECHNOLOGY_AIR_CARD&="|"&$XLArray[1][$i]

if $XLArray[0][$i]="MOBILE TECHNOLOGY.BLACKBERRY" then $MOBILE_TECHNOLOGY_BLACKBERRY&="|"&$XLArray[1][$i]

if $XLArray[0][$i]="MOBILE TECHNOLOGY.PDA" then $MOBILE_TECHNOLOGY_PDA&="|"&$XLArray[1][$i]

EndIf

Next

$_MAINFRAME_CONTROL_UNIT=StringTrimLeft($_MAINFRAME_CONTROL_UNIT,1)

$MAINFRAME_FRONT_END_PROCESSOR=StringTrimLeft($MAINFRAME_FRONT_END_PROCESSOR,1)

$MAINFRAME_TIMER_DIRECTORS=StringTrimLeft($MAINFRAME_TIMER_DIRECTORS,1)

$MICROFILM_VIEWER=StringTrimLeft($MICROFILM_VIEWER,1)

$MOBILE_TECHNOLOGY_AIR_CARD=StringTrimLeft($MOBILE_TECHNOLOGY_AIR_CARD,1)

$MOBILE_TECHNOLOGY_BLACKBERRY=StringTrimLeft($MOBILE_TECHNOLOGY_BLACKBERRY,1)

$MOBILE_TECHNOLOGY_PDA=StringTrimLeft($MOBILE_TECHNOLOGY_PDA,1)

MsgBox(0,"","$_MAINFRAME_CONTROL_UNIT="&$_MAINFRAME_CONTROL_UNIT)

MsgBox(0,"","$MAINFRAME_FRONT_END_PROCESSOR="&$MAINFRAME_FRONT_END_PROCESSOR)

MsgBox(0,"","$MAINFRAME_TIMER_DIRECTORS="&$MAINFRAME_TIMER_DIRECTORS)

MsgBox(0,"","$MICROFILM_VIEWER="&$MICROFILM_VIEWER)

MsgBox(0,"","$MOBILE_TECHNOLOGY_AIR_CARD="&$MOBILE_TECHNOLOGY_AIR_CARD)

MsgBox(0,"","$MOBILE_TECHNOLOGY_BLACKBERRY="&$MOBILE_TECHNOLOGY_BLACKBERRY)

MsgBox(0,"","$MOBILE_TECHNOLOGY_PDA="&$MOBILE_TECHNOLOGY_PDA)

best, Randall Edited April 5, 2006 by randallc

[Bert]

I did this with Randellc example to get what I needed. THANK YOU Randellc! I owe you big!

#include"ExcelCom.au3"
<A href="'mailto:$s_FilePath=@ScriptDir&"\bin\USD_Configuration_Items.xls'">$s_FilePath=@ScriptDir&"\bin\USD_Configuration_Items.xls" 
$XLArray=_XLArrayRead($s_FilePath,1,"B1:C50000");&$i_LastRow-1)
$bcell = "MAINFRAME.CONTROL UNIT"
_XLClose($s_FilePath,1)
local $_dataget 
;==============================================================================
local $s_StringOfSingleLine
for $i=0 to ubound ($XLArray,2)-1
if $XLArray[0][$i]<>"" then
if $XLArray[0][$i]=$bcell then $_dataget&="|"&$XLArray[1][$i]
EndIf
Next
$_dataget=StringTrimLeft($_dataget,1)

MsgBox(0,"",$_dataget)