Welcome, Guest. Please Login
vTask Homepage
 
 
Home Help Search Login


Page Index Toggle Pages: 1
Send Topic Print
Loop thru cells in an Excel spreadsheet (Read 31798 times)
vTask Support
Moderator
*
Offline



Posts: 3747
Tucson, AZ
Loop thru cells in an Excel spreadsheet
Jan 8th, 2009 at 5:37pm
 
You can iterate through each cell in a spreadsheet with a
loop
.  This loop can move vertically, horizontally, in reverse, or any way that you need to parse Excel data.  This has many useful purposes, such as updating monthly data, or performing a data-driven test script.

For our example, we will use the following spreadsheet:

...


To loop through the "A1-A3" cells (vertically), the following script will work:

...


...



To loop through the "A1-C1" cells (horizontally), it takes a little more work, since we want to increment the Cell Columns (letters), not the row numbers.  The following script will work:

...


...


Be sure to update for the correct location of "Book1.xls" on your PC.

SCRIPT #1:

VXM Script:   (Copy and Paste into vTask)
<step>
<action>Open Workbook</action>
<options>Hidden</options>
<value1>Book1.xls</value1>
</step>
<step>
<action>Start 'For' Loop</action>
<text>3</text>
<value1>1</value1>
<value2>1</value2>
<output value="%loop">Variable</output>
</step>
<step>
<action>Get Cell</action>
<options>Get Data</options>
<value1>Book1.xls</value1>
<value2>A%loop</value2>
<indents>1</indents>
<output value="%data">Variable</output>
</step>
<step>
<action>Display Message</action>
<text>Currently on cell "A%loop"

Data:  "%data"</text>
<value1>Looping in Excel</value1>
<indents>1</indents>
</step>
<step>
<action>NEXT LOOP</action>
</step>
<step>
<action>Close Workbook</action>
<value1>Book1.xls</value1>
</step>



SCRIPT #2:

VXM Script:   (Copy and Paste into vTask)
<step>
<action>Open Workbook</action>
<options>Hidden</options>
<value1>Book1.xls</value1>
</step>
<step>
<action>Start 'For' Loop</action>
<text>3</text>
<value1>1</value1>
<value2>1</value2>
<output value="%loop">Variable</output>
</step>
<step>
<action>Set Variable</action>
<text>=CHR(64+%loop)  &amp; "1"</text>
<indents>1</indents>
<output value="%cell">Variable</output>
<comment>save here for reuse</comment>
</step>
<step>
<action>Get Cell</action>
<options>Get Data</options>
<value1>Book1.xls</value1>
<value2>%cell</value2>
<indents>1</indents>
<output value="%data">Variable</output>
<comment>A1, B1, C1, etc</comment>
</step>
<step>
<action>Display Message</action>
<text>Currently on cell "%cell"

Data:  "%data"</text>
<value1>Looping in Excel</value1>
<indents>1</indents>
</step>
<step>
<action>NEXT LOOP</action>
</step>
<step>
<action>Close Workbook</action>
<value1>Book1.xls</value1>
</step>
Back to top
 

Thank you for using vTask Studio!
WWW  
IP Logged
 
vTask Support
Moderator
*
Offline



Posts: 3747
Tucson, AZ
Re: Loop thru cells in an Excel spreadsheet
Reply #1 - Aug 18th, 2009 at 8:30am
 
Additional note: To determine the total number of cells or rows, use the "
Get Cell Format
" command under the Excel category, and use the Total Rows or Total Columns option.

Below is a short demo which returns the number of columns.  To return the Row count, simply change the option to "Total Rows."

...


...


When running the sample below, be sure to update for the appropriate location for "Book1.xls".

...


VXM Script:   (Copy and Paste into vTask)
<step>
<action>Open Workbook</action>
<options>Hidden</options>
<value1>Book1.xls</value1>
</step>
<step>
<action>Get Cell Format</action>
<options>Total Columns</options>
<value1>Book1.xls</value1>
<indents>1</indents>
<output>Display in Message</output>
</step>
<step>
<action>Close Workbook</action>
<value1>Book1.xls</value1>
</step>
Back to top
 

Thank you for using vTask Studio!
WWW  
IP Logged
 
Page Index Toggle Pages: 1
Send Topic Print