19 replies to this topic

[SteveHoughton]

I have many functions that write to Excel which all work fine but now I want to read from Excel into Pro-IV. I am running 5.5 using VIP under Windows XP.

Has anybody got any working samples??

Many thanks

[andykay]

Steve,

There are 2 ways of doing this that depends upon whether or not your Spreadsheet is open or not.

a. If the Spreadsheet is saved as a .txt file, in some directory, you can parse out the line data by aliasing the file to a ProIV flat file.

b. If you wish to read an open Spreadsheet into ProIV then you must do something like the following:

* Select the particular Sheet you're interested in
$$T = '[WORKBOOK.SELECT("Sheet2")]'
#STAT = DDE_EXECUTE('EXCEL','SYSTEM',$$T)
* Read each row and each cell, one at a time
FOR #I = #START_SEARCH TO #MAX2
* this will only select the 4th column...For example, say you're looking for a particular Acct#
$ROW = 'R' + CONV(#I) + 'C4'
* place the value of the cell into scratch variable ($A)
#A = DDE_REQUEST('EXCEL','Sheet2',$ROW,$A)

IF INDEX($A,$ACCT_NUM) # 0 THEN
FOR #J = 5 TO 10
$ROW_ID = 'R' + CONV(#I) + 'C' + CONV(#J)
* ProIV will now read columns 5-10, one at a time and place the column value into $B for the ROW
* that you found in the first FOR loop.
#B = DDE_REQUEST('EXCEL','Sheet2',$ROW_ID,$
ENDFOR
ENDIF
ENDFOR


HTH,

AK

[SteveHoughton]

Steve,

There are 2 ways of doing this that depends upon whether or not your Spreadsheet is open or not.

a. If the Spreadsheet is saved as a .txt file, in some directory, you can parse out the line data by aliasing the file to a ProIV flat file.

b. If you wish to read an open Spreadsheet into ProIV then you must do something like the following:

* Select the particular Sheet you're interested in
$T = '[WORKBOOK.SELECT("Sheet2")]'
#STAT = DDE_EXECUTE('EXCEL','SYSTEM',$T)
* Read each row and each cell, one at a time
FOR #I = #START_SEARCH TO #MAX2
* this will only select the 4th column...For example, say you're looking for a particular Acct#
$ROW = 'R' + CONV(#I) + 'C4'
* place the value of the cell into scratch variable ($A)
#A = DDE_REQUEST('EXCEL','Sheet2',$ROW,$A)

IF INDEX($A,$ACCT_NUM) # 0 THEN
FOR #J = 5 TO 10
$ROW_ID = 'R' + CONV(#I) + 'C' + CONV(#J)
* ProIV will now read columns 5-10, one at a time and place the column value into $B for the ROW
* that you found in the first FOR loop.
#B = DDE_REQUEST('EXCEL','Sheet2',$ROW_ID,$
ENDFOR
ENDIF
ENDFOR


HTH,

AK

[SteveHoughton]

Andy

Works a dream, thanks very much.

Steve

[Ajaym]

Andy

Works a dream, thanks very much.

Steve

Option a) is relatively straight forward but option b)sounds intersting ? Did you get the option 2 to work ?

[SteveHoughton]

Yes I do have global updates working exactly as Andy suggested above..

Reading from Excel is quite fast whereas writing to Excel is much slower. I am using Pro-IV version 5.5 with windows XP and have used Excel 2000 and 2003.

Try it - it does work and thanks again Andy..

Steve

[andykay]

Steve,

I was reviewing the code that I posted for you and saw a way that you might be able to speed up whatever you are doing by a few nano-seconds.

As I didn't know what you were going to do with the data you're retrieving from Excel, I wrote it very basic.

If you used the example I gave (of trying to retrieve the data from columns 5-10 after finding a match for column 4), you'd have to do some sort of LSCALL after each retrieval of Excel columns 5-10 or you'd lose the value that resided in $B. But, if you make $B an array, you'd be able to retrieve all the columns your interested in before LSCALLing off to process those values (See following).

IF INDEX($A,$ACCT_NUM) # 0 THEN
FOR #J = 5 TO 10
$ROW_ID = 'R' + CONV(#I) + 'C' + CONV(#J)
* ProIV will now read columns 5-10, one at a time and place the column value into an array $B for the ROW
* that you found in the first FOR loop.
#B = DDE_REQUEST('EXCEL','Sheet2',$ROW_ID,$B(#J - 4))
ENDFOR
* Process the values you just retieved
LSCALL $GOTTHEM
ENDIF


I'm glad you were able to make use of it. We use the code on a report that compares the values of 3 different sheets in a workbook and highlights the differences so the user can quick identify the areas that need their immediate attention. Much faster than printing out 3 reports and then relying on the user to be accurate while performing this tedious arduous task.

AK

[Vol Yip]

Steve,

I was reviewing the code that I posted for you and saw a way that you might be able to speed up whatever you are doing by a few nano-seconds.

As I didn't know what you were going to do with the data you're retrieving from Excel, I wrote it very basic.

If you used the example I gave (of trying to retrieve the data from columns 5-10 after finding a match for column 4), you'd have to do some sort of LSCALL after each retrieval of Excel columns 5-10 or you'd lose the value that resided in $B. But, if you make $B an array, you'd be able to retrieve all the columns your interested in before LSCALLing off to process those values (See following).

IF INDEX($A,$ACCT_NUM) # 0 THEN
FOR #J = 5 TO 10
$ROW_ID = 'R' + CONV(#I) + 'C' + CONV(#J)
* ProIV will now read columns 5-10, one at a time and place the column value into an array $B for the ROW
* that you found in the first FOR loop.
#B = DDE_REQUEST('EXCEL','Sheet2',$ROW_ID,$B(#J - 4))
ENDFOR
* Process the values you just retieved
LSCALL $GOTTHEM
ENDIF


I'm glad you were able to make use of it. We use the code on a report that compares the values of 3 different sheets in a workbook and highlights the differences so the user can quick identify the areas that need their immediate attention. Much faster than printing out 3 reports and then relying on the user to be accurate while performing this tedious arduous task.

AK

Hi AK,

I am going to write a SL function which needs to generate an Excel with 2 worksheet. I am using PROIV 4.6.

Could you please give me some guidelines on creating an Excel? (DDE to Open, Write and Save the spreadsheet etc.)

Thanks in advance.

Regards,

Vol

[andykay]

Could you please give me some guidelines on creating an Excel? (DDE to Open, Write and Save the spreadsheet etc.)

Open Excel: #STAT = SYSTEM('GUI /C START EXCEL')

Write Line To Excel:
#STAT = DDE_POKE('EXCEL',$SHEET,$CELLMAPPING(#NUMB), $$VALUE)

$SHEET example: "Sheet1"
$CELLMAPPING(#NUMB) example: "R1C3" **Row 1, Column 3 **
$$VALUE example: Whatever you want to populate the cell with

** We use GL's to call our Excel routines. This way you only have to write the logic once instead of writing it in every report. The WRITE_LINE GL will be the most difficult one to write as you write an entire line of data to Excel in 1 call. Therefore, you must allow for X number of possible cell combinations.

ie. The following would fill columns 1-5 of a sheet.

#STAT = DDE_POKE('EXCEL',$SHEET,$CM(1),$$V1,$CM(2),$$V2,
$CM(3),$$V3,$CM(4),$$V4,$CM(5),$$V5)

** You should encode for the maximum number of possibilities when you write you GL now so you don't have to go back and modify your code later.


Save Excel Workbook:

** At the moment, we force the user to save their own Excel reports. If we encode for a report that is to be saved to a DIR, we just use a normal comma delimited file. This does not allow you to have all the bells and whistles that you can do if you are writing to an active Excel workbook, though.


HTW,

AK

[SteveHoughton]

And then when you have almost finished add these lines once all data has been written to Excel

001 #A = DDE_EXECUTE('excel','system','"[column.width(1,"C1:C15",,3,1)]"')
002 #A = DDE_EXECUTE('excel','system','"[select("C6:c111")]')
003 #A = DDE_EXECUTE('excel','system','"[format.number("###,###,##0.00")]"')
004 #A = DDE_EXECUTE('excel','system','"[select("C1:C15")]"')
005 #A= DDE_EXECUTE('excel','system','"[format.auto(12)]"')

Line 1 selects the columns (15 in this case) and adds format column autofit (set the width depending on the data contained in each column)
Line 2 selects columns 6 to 11
Line 3 formats these columns
Line 4 Selects all columns
Line 5 Adds a stylesheet (There are loads supplied with Excel - number 12 is quite simple.

None of this is essentail but it does make it look better.

Regards

Steve

[Mike Schoen]

And when writing to excel, always warn the user not to open any other spreadsheets while they wait.
We have had a few users try this, only to discover that pro-iv happily starts poking into the currently open spreadsheet and overwriting data.

Mike

[DARREN]

The solution to this issue is to rename your sheet to something unqiue before sending/receiving data to it. The command is WORKBOOK.NAME(oldname_text, newname_text)

Then just reference the renamed sheet from that point forward.

[andykay]

And when writing to excel, always warn the user not to open any other spreadsheets while they wait.
We have had a few users try this, only to discover that pro-iv happily starts poking into the currently open spreadsheet and overwriting data.

I'm glad to see it's not just my code doing this.

Funny thing though...It overwrites cells on other open workbooks on other peoples computers 95% of the time, but I can have 3-4 workbooks open and have nothing happen. After conceding to the problem, we too had to issue a warning message to have people close any open workbooks.

AK

[Vol Yip]

Hi,

If I want to write to a specific name of the worksheet instead of "Sheet1", how can I do that?

#STAT = DDE_POKE('Excel','t_Schema', $CELLREF, $VAR)

doesn't work

I have to write to

#STAT = DDE_POKE('Excel','Sheet1', $CELLREF, $VAR)

Then, if I try

$OLDNAME = 'Sheet1'
$NEWNAME = 't_Schema'
$$C = '[WORKBOOK.NAME($OLDNAME, $NEWNAME)]'
#ERROR = DDE_EXECUTE('Excel','SYSTEM',$$C)

It won't work either

Please help.

[andykay]

Then, if I try

$OLDNAME = 'Sheet1'
$NEWNAME = 't_Schema'
$$C = '[WORKBOOK.NAME($OLDNAME, $NEWNAME)]'
#ERROR = DDE_EXECUTE('Excel','SYSTEM',$$C)

Vol,

There is nothing wrong with this part of your code...it's correct. You just have to first SELECT the Sheet# before you attempt to change its attributes.


HTH,

AK

Edited by andykay, 15 March 2007 - 07:49 PM.