Sign in to follow this  
Followers 0
Bert

Excel Question

8 posts in this topic

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. :)

Share this post


Link to post
Share on other sites



Hi, Sorry I am busy;

similar thread;

would that help?

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

best, Randall

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

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 by randallc

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0