Excel
#2
Posted 01 February 2007 - 08:45 PM
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
#3
Posted 02 February 2007 - 10:27 AM
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
#6
Posted 09 February 2007 - 10:58 PM
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
#7
Posted 10 February 2007 - 01:14 AM
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
#8
Posted 11 February 2007 - 01:59 PM
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
#9
Posted 12 February 2007 - 06:57 PM
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
#10
Posted 12 February 2007 - 08:16 PM
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
#12
Posted 15 February 2007 - 05:00 PM
Then just reference the renamed sheet from that point forward.
#13
Posted 15 February 2007 - 06:45 PM
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
#14
Posted 15 March 2007 - 05:16 AM
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.
#15
Posted 15 March 2007 - 07:48 PM
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.
Reply to this topic
1 user(s) are reading this topic
0 members, 1 guests, 0 anonymous users