Extract from Excel

[hatkeeper]

Hello,

I am trying to extract data from excel and transfer them into another program. I am using the "Extract from Excel" sample in the Scheduler 9.0 as a starting point. Instead of typing out the DDERequest and StringReplace function for each column, I decided to use a loop because there are a lot of columns in my dataset. Unfortunately I cannot get the StringReplace function to eliminate the line break.

Here is my code so far,

------------------------------------------------------------------------
//Specify the path of the Excel file here
Let>filename=c:\example1.xls

IfFileExists>filename
//Start Excel
ExecuteFile>filename
WaitWindowOpen>Microsoft Excel -*

Let>r=2
Repeat>r

//get the appropriate fields
Let>i=1
Repeat>i
DDERequest>Excel,filename,R%r%C%i%,field[%i%],60
Let>i=i+1
Until>i=20

//remove the CRLF that Excel adds
let>i=1
Repeat>i
StringReplace>field[%i%],CRLF,,field[%i%]
Let>i=i+1
Until>i=20

Let>i=i-1
MessageModal>%i%
MessageModal>field[%i%]

Run>Notepad.exe
WaitWindowOpen>Untitled - Notepad

Let>p=1
wait>2
Repeat>p
Send>field[%p%]
wait>1
press Tab
wait>1
Let>p=p+1
Until>p=20

Until>r=2

endif
---------------------------------------------------------------------------
Another question is about using a variable repeatedly, in the middle portion of the code,

Let>i=i-1
MessageModal>%i%
MessageModal>field[%i%]

2nd line returns the number correctly, but when i use it as the array element in line 3, the result is not the right string/value.

Please help.

- ken

[JRL]

Just a thought, not tested. Try putting percents around the CRLF in the line

StringReplace>field[%i%],CRLF,,field[%i%]

therefore:

StringReplace>field[%i%],%CRLF%,,field[%i%]


For the array value issue. I always have problems with that too. I always assign the array to a benign variable then display the variable.

Like this:

Let>i=i-1
MessageModal>%i%
Let>value=field[%i%]
MessageModal>%value%

[jpuziano]

For the array value issue. I always have problems with that too. I always assign the array to a benign variable then display the variable.

Like this:

Let>i=i-1
MessageModal>%i%
Let>value=field[%i%]
MessageModal>%value%

Hi Marcus,

I use the same trick as JRL above. Is there some special syntax that will allow us to use the arrayed variable directly in the MessageModal> command (and other commands)? If not, could that be added to the wish list?

Thanks

[Me_again]

Coincidentally I ran into this problem recently and posted the issue here.

My experience though is that MDL> will display one instance of an array variable but not two, and per JRL it won't display the first if there are any characters following the array variable - I assume that's correct but haven't tested it yet.

I'll do more searching later and, depending what I find, will probably put this in the bugtracker.

[hatkeeper]

I have tried using %CRLF% but it does not work. Right now I have a stringreplace syntax for each excel column which is cumbersome but it gets the job done. I would like to use a loop in the future because the code is much cleaner.

Thanks for the tips on the array variable.

- Ken