Jump to content


- - - - -

DDE Commands for Excel


9 replies to this topic

#1 Guest_Lars_*

Guest_Lars_*
  • Guests

Posted 15 October 2005 - 01:29 AM

I am using the Default Excel template which has 3 sheet, Sheet1, Sheet2, and Sheet3.

If the report dictates more than 3 pages...the fuction hangs.

What are the DDE commands to 1) add a new sheet to the existing Excel report, and 2) how do I rename the Sheet names to something more appropriate.

Thanks in advance.

#2 Wim Soutendijk

Wim Soutendijk

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 211 posts
  • Gender:Male
  • Location:Netherlands

Posted 17 October 2005 - 07:48 AM

To insert a new sheet into an active workbook you can try this:
#S = DDE_EXECUTE('EXCEL','SYSTEM','[NEW(1,0,TRUE)]')

I never tried to rename a sheet...

#3 DARREN

DARREN

    ProIV Guru

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

Posted 17 October 2005 - 03:47 PM

View the thread

http://www.proivrc.c...hl=dde commands

for more info

Also, all suported Excel DDE commands can be found at

http://support.micro...s/Q128/1/85.asp
Things should be made as simple as possible, but not simpler

#4 Guest_Lars_*

Guest_Lars_*
  • Guests

Posted 09 November 2005 - 09:29 PM

Thanks Darren,

The links you provided proved to be an invaluable resource and I have been able to solve all but one situation.

I am currently working on a report that will be in the following XLS format.

Sheet 1 Summary Details
Sheet 2 Detail 1
Sheet 3 Detail 2


The summary details is the last sheet that gets printed. It will ONLY list differences between Sheets 2 & 3. I would like to change the font color of the cell of the differences on its respective sheet. This is what I have that is not working:

*** After the final LS (SUMMARY) has written a line to Excel, I then scan through the Detail lists of Sheet 2 & 3 looking for a match. If I find the item on either Sheet 2 or Sheet 3, I perform the following logic routine.

$COM IS DEFINED AS 2000

IF INDEX($A,BOM_COMP_ITEM) # 0 THEN // a match was found on the corresponding DETAIL sheet (2 or 3) that I switched to in logic above this line

$COM = '[SELECT(' + $ROW + ',' + $ROW + ')]'
#S = DDE_EXECUTE('EXCEL','SYSTEM',$COM) // this should select the cell specified in $ROW, with $ROW being in the R1C1 format

$COM= '[FONT.PROPERTIES(Arial,STYLE(bold),False,False,True,False,False,False,False,
6,,,,)]'
#S = DDE_EXECUTE('EXCEL','SYSTEM',$COM) // I WISH this would change the color of the active cell to "26" (in this case), but it does not

ENDIF


If anyone knows the correct DDE syntax for what I am trying to accomplish, I thank you in advance.

#5 Bill Loven

Bill Loven

    Expert

  • Members
  • PipPipPipPip
  • 147 posts
  • Gender:Male
  • Location:Coppell, United States

Posted 09 November 2005 - 10:45 PM

:D Look a [PATTERNS.

HTH

BILL

#6 Kevin Bruss

Kevin Bruss

    Expert

  • Members
  • PipPipPipPip
  • 113 posts
  • Gender:Male
  • Location:Oklahoma City, OK

Posted 10 November 2005 - 06:56 PM

$PATTERNS = '[PATTERNS(1,1,' + $COLOR + ')]'
#A = DDE_EXECUTE('Excel','SYSTEM',$PATTERNS)

Some colors are
1 Black
15 Gray
2 White
7 Magenta
3 Red
46 Orange
44 Gold
6 Yellow
8 Cyan
4 Green
5 Blue
24 Purple

You should probably use the select command prior to this

$SELECT = '[SELECT("R' + CONV(#BROW) + 'C' + CONV(#BCOL)
+ ':R' + CONV(#EROW) + 'C' + CONV(#ECOL) + '")]'
#A = DDE_EXECUTE('Excel','SYSTEM',$SELECT)

(B/E for Begin/End rows and columns)

#7 Guest_Lars_*

Guest_Lars_*
  • Guests

Posted 30 January 2006 - 11:09 PM

To all who provided the answers above...Thank You.

I am now trying to group several lines of data into a Group but can't get the DDE to work. I have a report that, if ran the way they want it, would output over 30,000 lines. We've convinced then that a summary would be much better but they would still like to ability to have the option for the details.

This is what I have so far. The first execute works fine...the lines/cells get highlighted. But then the second execute doesn't work. Any suggestions would be greatly appreciated.

$$CD = '[SELECT("R5C1:R10C14")]'
#S = DDE_EXECUTE('EXCEL','System',$$CD)

$$CD = '[GROUP("R5C1:R10C14")]'
#S = DDE_EXECUTE('EXCEL','System',$$CD)

I've also tried:
$$CD = '[GROUP()]'
$$CD = '[GROUP]'

But nothing works.

#8 andykay

andykay

    ProIV Guru

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

Posted 08 February 2006 - 12:58 AM

Lars,

Try the DEMOTE command. This command is used to tell Excel that the cells that you have selected are to be grouped with an expand/collapse button to help facilitate the display of the grouped rows.

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

#9 Guest_lightning_*

Guest_lightning_*
  • Guests

Posted 28 February 2006 - 06:33 AM

$PATTERNS = '[PATTERNS(1,1,' + $COLOR + ')]'
#A = DDE_EXECUTE('Excel','SYSTEM',$PATTERNS)

Some colors are
1 Black
15 Gray
2 White
7 Magenta
3 Red
46 Orange
44 Gold
6 Yellow
8 Cyan
4 Green
5 Blue
24 Purple

You should probably use the select command prior to this

$SELECT = '[SELECT("R' + CONV(#BROW) + 'C' + CONV(#BCOL)
+ ':R' + CONV(#EROW) + 'C' + CONV(#ECOL) + '")]'
#A = DDE_EXECUTE('Excel','SYSTEM',$SELECT)

(B/E for Begin/End rows and columns)

Anyone know of the function to change column chart color?
Rite now, i can use

..........SELECT("R1C1").........
$COLOR = '6' /*YELLOW*/
$$COMMAND5 = '[PATTERNS(1,1,' + $COLOR + ')]'
#STAT = DDE_EXECUTE('EXCEL',$SHEET,$$COMMAND5)

But what about column chart's column? The statement below does not work.... It can select series 1 point 5, but no color change....
$$COMMAND4 = '[SELECT("S1P5")]'
#STAT = DDE_EXECUTE('EXCEL',$CHART1,$$COMMAND4)
#STAT = DDE_EXECUTE('EXCEL',$SHEET,$$COMMAND5)

#10 hwyap

hwyap

    Newbie

  • Members
  • Pip
  • 3 posts

Posted 28 February 2006 - 08:22 AM

Problem solved.

$$COMMAND5 = '[PATTERNS(1,1,3,1,,0,3,3,3,FALSE,FALSE)]'

the zero means area(background color) is customize.
3 means red color



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users