vTask Forum
http://www.vtaskstudio.com/forum/YaBB.pl
vTask Boards >> Sample vTask Scripts (Basic) >> Loop thru cells in an Excel spreadsheet
http://www.vtaskstudio.com/forum/YaBB.pl?num=1231461459

Message started by vTask Support on Jan 8th, 2009 at 5:37pm

Title: Loop thru cells in an Excel spreadsheet
Post by vTask Support on 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:


Code:
<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:


Code:
<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>

Title: Re: Loop thru cells in an Excel spreadsheet
Post by vTask Support on 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".




Code:
<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>

vTask Forum » Powered by YaBB 2.4!
YaBB © 2000-2009. All Rights Reserved.