Code won't stop looping through imported spreadsheet

[Mike72688]

Hi,

I'm running 9.5, testing an SAP transaction. I'm importing a data table which is working fine. It has three rows of data. I selected the "run on all rows" option. The problem I'm having is that is doesn't seem to know when to stop running.

Here is a portion of my code:

Code:

1: DataTable.Import "C:\Temp\CreateVendor2.xls"
2: Do While DataTable.GetCurrentRow <> ""

' rows of SAP code which are working fine

Code:

33: Datatable.SetNextRow
34: Loop

I have very little experience with the syntax of the loop statements. I want the code to realize that once it gets to row 5, that there is no more data. I only want it to import the speadsheet once. And the number of rows with data will change so I don't want to hard code it or have to change the run options. Thanks in advance for your help.

Mike

[Ankur]

.GetCurrentRow will get the row number from the datatable, since the number can never be blank it won't stop looping. I would suggest you to take .GetRowCount first. This will give you the total number of active rows in the longest column of your sheet. Then loop through row count value.

[Mike72688]

Thank you for your reply. I changed the code to this:

Code:

DataTable.Import "C:\Temp\CreateVendor2.xls"
r = DataTable.GetRowCount
Do While r >= 1

' lines of code

DataTable.SetNextRow
r = r-1
Loop

It seems to do the same thing. After the final row of data (row 3) it goes all the way to the first line of code (importing the spreadsheet) and starts over. Am I supposed to be putting this in a subroutine? I am unclear on what the code should look like. Thank you in advance for your help.

Mike

[Ankur]

The whole action must be falling in some other loop, check the place from where you have called this action...
.... for a quick test you can just copy-paste it your code in a new action and run it ...it should work.

[sreekanth chilam]

Hi Mike,

Here there are two ways has to be noticed by all of us.

1 st way :
In file-Settings-->Run tab, by default "Run on All rows" will be there.
That means, if there are 'n' number of rows are stored in datatable's Global Sheet then entire script will be running "n' number of times.

2 nd way :
If at all we wont have any data in datatable's Global sheet & we are importing the data from an external spreadsheet.

Then initially we should select "Run one iteration only" option in file-Settings-->Run tab.,

datatable.import "../../../.xls" ' ---> import the data into current global sheet

rc= datatable.getrowcount ' ----> retrieve the rowcount of global sheet (this much number of times we are running the test script by using the loop)

for i=1 to rc ' ----> start of loop
datatable.setcurrentrow(i)
...............
..............
next ' ----> end of loop

we should either follow 1st or 2nd way but not mixing the both ways ......

@ Mike : I hope your query has been solved .......

[Ankur]

I selected the "run on all rows" option.

Yes, as Sreekanth rightly pointed out, this is the the loop which is causing problem. Set it to run one iteration only.

[Mike72688]

Sreekanth,

I changed the run settings to run one iteration. Now it is working as I intended. Thank you very much for your help Sreekanth and Ankur.

I must say that it is confusing to me. The help file shows that running one iteration will run on the first row in the global data table. When I import, the file replaces (or is imported into) the global data table. I guess the code overrides the run settings?

Thanks again,

Mike