Jump to content


Photo
- - - - -

Excel


19 replies to this topic

#1 SteveHoughton

SteveHoughton

    Member

  • Members
  • PipPip
  • 42 posts
  • Gender:Male
  • Location:Kent UK

Posted 01 February 2007 - 07:21 PM

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

#2 andykay

andykay

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 204 posts
  • Gender:Male
  • Location:Cyberspace...looking for work

Posted 01 February 2007 - 08:45 PM

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
THE LIGHT AT THE END OF THE TUNNEL IS THE HEADLAMP OF THE TRAIN THAT'S ABOUT TO HIT YOU!!!

#3 SteveHoughton

SteveHoughton

    Member

  • Members
  • PipPip
  • 42 posts
  • Gender:Male
  • Location:Kent UK

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



#4 SteveHoughton

SteveHoughton

    Member

  • Members
  • PipPip
  • 42 posts
  • Gender:Male
  • Location:Kent UK

Posted 02 February 2007 - 10:28 AM

Andy

Works a dream, thanks very much.

Steve

#5 Ajaym

Ajaym

    Newbie

  • Members
  • Pip
  • 6 posts
  • Gender:Male
  • Location:California, USA

Posted 09 February 2007 - 09:21 PM

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 ?
Ajay Mathur

#6 SteveHoughton

SteveHoughton

    Member

  • Members
  • PipPip
  • 42 posts
  • Gender:Male
  • Location:Kent UK

Posted 09 February 2007 - 10:58 PM

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

#7 andykay

andykay

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 204 posts
  • Gender:Male
  • Location:Cyberspace...looking for work

Posted 10 February 2007 - 01:14 AM

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 (w00t)
THE LIGHT AT THE END OF THE TUNNEL IS THE HEADLAMP OF THE TRAIN THAT'S ABOUT TO HIT YOU!!!

#8 Vol Yip

Vol Yip

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 393 posts
  • Gender:Male
  • Location:Hong Kong

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 (w00t)


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 andykay

andykay

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 204 posts
  • Gender:Male
  • Location:Cyberspace...looking for work

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
THE LIGHT AT THE END OF THE TUNNEL IS THE HEADLAMP OF THE TRAIN THAT'S ABOUT TO HIT YOU!!!

#10 SteveHoughton

SteveHoughton

    Member

  • Members
  • PipPip
  • 42 posts
  • Gender:Male
  • Location:Kent UK

Posted 12 February 2007 - 08:16 PM

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

#11 Mike Schoen

Mike Schoen

    Expert

  • Members
  • PipPipPipPip
  • 198 posts
  • Gender:Male
  • Location:Guelph, Canada

Posted 15 February 2007 - 04:52 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.

Mike

#12 DARREN

DARREN

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 396 posts
  • Gender:Male
  • Location:Florida,USA

Posted 15 February 2007 - 05:00 PM

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.
Things should be made as simple as possible, but not simpler

#13 andykay

andykay

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 204 posts
  • Gender:Male
  • Location:Cyberspace...looking for work

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. (w00t)

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
THE LIGHT AT THE END OF THE TUNNEL IS THE HEADLAMP OF THE TRAIN THAT'S ABOUT TO HIT YOU!!!

#14 Vol Yip

Vol Yip

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 393 posts
  • Gender:Male
  • Location:Hong Kong

Posted 15 March 2007 - 05:16 AM

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.

#15 andykay

andykay

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 204 posts
  • Gender:Male
  • Location:Cyberspace...looking for work

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.

THE LIGHT AT THE END OF THE TUNNEL IS THE HEADLAMP OF THE TRAIN THAT'S ABOUT TO HIT YOU!!!



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users