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.
Click the link below to see the new game I'm developing!
DDE Commands for Excel
Started by
Guest_Lars_*
, Oct 15 2005 01:29 AM
9 replies to this topic
#3
DARREN
-
- Members
-
- 396 posts
ProIV Guru
- 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
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.
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.
#6
Kevin Bruss
-
- Members
-
- 113 posts
Expert
- 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)
#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.
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
-
- Members
-
- 204 posts
ProIV Guru
- 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.
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
Anyone know of the function to change column chart color?$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)
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)
Reply to this topic
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users
Click the link below to see the new game I'm developing!
