Hi,
to follow on from this, many years ago Excel (Business Intelligence) and
Word were very powerful ways to produce reports without the license needed
for Crystal.
Excel is great for this as you can use Macro's and VB Scripting to format
reports and execute these via DDE.
Here is an old DDE.ini file that I used to trigger Macro's, you can update
the "Normal" Template with your Macro code and call this from within the
DDE.ini file by specifying the Macro when calling ... these will copy the
ARS information to a new Excel Worksheet, Word Document, etc where you can
insert you code to format the report, etc.
[Excel - No Formatting]
Path=c:\Program Files\Microsoft Office\Office11\excel.exe
Application=excel
Topic=system
Format=TAB
XFRDATA=Clipboard
Command1=[NEW(1)][PASTE()][SAVE.AS(,0)]
[Excel - Row and Column Sizing]
Path=c:\Program Files\Microsoft Office\Office11\excel.exe
Application=excel
Topic=system
Format=TAB
XFRDATA=Clipboard
Command1=[NEW(1)][PASTE()][SAVE.AS(,0)]
Command2=[column.width(30)][alignment(1,1,1)]
Command3=[column.width(,,,3)][row.height(64)][format.font(,9)]
Command4=[PAGE.SETUP(,,,,,,0,1,,,2,9,75)]
Command5=[select("R1")][patterns(1,,15)][format.font(,,1)][row.height(26)][b
order(,,,,2)]
[Excel BTS WIN Daily Report]
Path=c:\Program Files\Microsoft Office\Office11\excel.exe
Application=excel
Topic=system
Format=TAB
XFRDATA=Clipboard
Command1=[NEW(1)][PASTE()][SAVE.AS(,0)]
Command2=[RUN("Telco.XLS!BTSDailyFormatWIN")]
[Excel BTS DDA Daily Report]
Path=c:\Program Files\Microsoft Office\Office11\excel.exe
Application=excel
Topic=system
Format=TAB
XFRDATA=Clipboard
Command1=[NEW(1)][PASTE()][SAVE.AS(,0)]
Command2=[RUN("Telco.XLS!BTSDailyFormatDDA")]
[Excel BTS ETA Report]
Path=c:\Program Files\Microsoft Office\Office11\excel.exe
Application=excel
Topic=system
Format=TAB
XFRDATA=Clipboard
Command1=[NEW(1)][PASTE()][SAVE.AS(,0)]
Command2=[RUN("Telco.XLS!BTSETAFormat")]
[Excel BTS Weekly]
Path=c:\Program Files\Microsoft Office\Office11\excel.exe
Application=excel
Topic=system
Format=TAB
XFRDATA=Clipboard
Command1=[NEW(1)][PASTE()][SAVE.AS(,0)]
Command2=[RUN("Telco.XLS!BTSWeeklyFormat")]
[Excel BTS Weekly Report]
Path=c:\Program Files\Microsoft Office\Office11\excel.exe
Application=excel
Topic=system
Format=TAB
XFRDATA=Clipboard
Command1=[NEW(1)][PASTE()][SAVE.AS(,0)]
Command2=[RUN("Telco.XLS!BTSWeeklyReportFormat")]
[Excel BTS Monthly]
Path=c:\Program Files\Microsoft Office\Office11\excel.exe
Application=excel
Topic=system
Format=TAB
XFRDATA=Clipboard
Command1=[NEW(1)][PASTE()][SAVE.AS(,0)]
Command2=[RUN("Telco.XLS!BTSMonthlyFormat")]
[Excel BTS Monthly WIN]
Path=c:\Program Files\Microsoft Office\Office11\excel.exe
Application=excel
Topic=system
Format=TAB
XFRDATA=Clipboard
Command1=[NEW(1)][PASTE()][SAVE.AS(,0)]
Command2=[RUN("Telco.XLS!BTSMonthlyWINFormat")]
[Excel Telco Daily]
Path=c:\Program Files\Microsoft Office\Office11\excel.exe
Application=excel
Topic=system
Format=TAB
XFRDATA=Clipboard
Command1=[NEW(1)][PASTE()][SAVE.AS(,0)]
Command2=[RUN("Telco.XLS!TelcoDailyFormat")]
[Excel Telco Daily SAF]
Path=c:\Program Files\Microsoft Office\Office11\excel.exe
Application=excel
Topic=system
Format=TAB
XFRDATA=Clipboard
Command1=[NEW(1)][PASTE()][SAVE.AS(,0)]
Command2=[RUN("Telco.XLS!TelcoDailySAFormat")]
[Excel Telco Daily CHG]
Path=c:\Program Files\Microsoft Office\Office11\excel.exe
Application=excel
Topic=system
Format=CSV
XFRDATA=Clipboard
Command1=[NEW(1)][PASTE()][SAVE.AS(,0)]
Command2=[RUN("Telco.XLS!TelcoDailyCHGFormat")]
[Excel Telco Weekly]
Path=c:\Program Files\Microsoft Office\Office11\excel.exe
Application=excel
Topic=system
Format=TAB
XFRDATA=Clipboard
Command1=[NEW(1)][PASTE()][SAVE.AS(,0)]
Command2=[RUN("Telco.XLS!TelcoWeeklyFormat")]
[Excel Telco Summary]
Path=c:\Program Files\Microsoft Office\Office11\excel.exe
Application=excel
Topic=system
Format=TAB
XFRDATA=Clipboard
Command1=[NEW(1)][PASTE()][SAVE.AS(,0)]
Command2=[RUN("Telco.XLS!TelcoSummaryFormat")]
[BTsWIN Weekly Finance]
Path=c:\Program Files\Microsoft Office\Office11\excel.exe
Application=excel
Topic=system
Format=TAB
XFRDATA=Clipboard
Command1=[NEW(1)][PASTE()][SAVE.AS(,0)]
Command2=[RUN("Telco.XLS!BTSWINWeeklyFinance")]
[Outlook]
Path=C:\Program Files\Microsoft Office\Office11\outlook.exe /c ipm.note
Application=outlook
Topic=system
Format=Record
CharsPerLine=100
XFRDATA=Clipboard
[Word]
Path=c:\Program Files\Microsoft Office\Office11\winword.exe
Application=winword
Topic=system
Format=CurrentFormat
XFRDATA=Clipboard
Command1=[FileNew .Template="Normal", .NewTemplate = 0]
Command2=[Editpaste]
[Word - Change Request]
Path=C:\Program Files\Microsoft Office\Office11\winword.exe
Application=winword
Topic=system
Format=CurrentFormat
XFRDATA=Clipboard
Command1=[FileNew .Template="Normal", .NewTemplate = 0]
Command2=[call Format_Change_Request]
Let me know if you need a Template with Macros that you can update, I also
have this available that did some funky things back in the day.
Cheers
Carl
http://www.missingpiecessoftware.com/
From: Action Request System discussion list(ARSList)
[mailto:[email protected]] On Behalf Of Stroud, Natalie K
Sent: 31 July 2012 22:20
To: [email protected]
Subject: Re: [EXTERNAL] DDE Commands
**
Andy:
You might have better luck doing an initial dump via DDE, then having an
Excel macro manipulate the data and copy/paste it into a new spreadsheet.
We had a pretty fancy ticket metric report where the data would be dumped,
then a macro would manipulate (including creating several tabs) the data and
create a bunch of graphs. I was not the one who did all of that work, but
it was pretty amazing.
Here is a generalized section from our DDE.INI that ran the macro:
___________________________
[ReportName]
Path=C:\Program Files (x86)\Microsoft Office\Office12\excel.exe
Application=excel
Topic=system
Format=tab
XFRDATA=File
Command1=[OPEN("%f")]
Command2=[OPEN("\\server\share\other folders as needed to define
path\MacroName.xlsm")]
Command3=[RUN("MacroName.xlsm'!ReportName")
____________________________________
. Command1 does the dump from Remedy to Excel. - Note that this path
is for a 64-bit OS.
. Command2 runs the macro - include path the macro as well as the
macro name. Because several people were interested in this report, we kept
our macro out on a network share.
. Command3 generates the report using the macro.
Note that for a 32-bit operating system, you will probably need C:\Program
Files\Microsoft Office\Office12 instead for the Excel path. Additionally
(and whether your OS is 32 or 64 bit doesn't matter here), depending on what
version of Excel you are using, you may need a different number there in the
path than 12. I believe 12 corresponds to Office 2007 and 14 corresponds to
Office 2010. I couldn't tell you for older versions, but you just need to
make sure the path matches where your excel.exe file is located.
Good luck!
Natalie
From: Action Request System discussion list(ARSList)
[mailto:[email protected]] On Behalf Of Mayfield, Andy L.
Sent: Tuesday, July 31, 2012 2:56 PM
To: [email protected]
Subject: Re: [EXTERNAL] DDE Commands
**
I use it now to dump results in excel.
I would like to try and also use it to dump results from Remedy into a
spreadsheet as well as open another spreadsheet, copy it's data and paste it
into another.
I'm not sure if it can do that or not, but I'm trying it out.
Andy L. Mayfield
Alabama Power Company
Protection & Control Technician Sr.
Linc # 10*19140
Cell # 205-288-9140
Office # 205-257-1556
From: Action Request System discussion list(ARSList)
[mailto:[email protected]] On Behalf Of Stroud, Natalie K
Sent: Tuesday, July 31, 2012 3:31 PM
To: [email protected]
Subject: Re: [EXTERNAL] DDE Commands
**
Andy:
What are you wanting to do with DDE? If you need something basic like
dumping a Remedy report to Excel, I can give you a section from the DDE.INI
file we used to enable Excel in Remedy, but if you need to do something
fancier than that, I probably can't help you.
Natalie Stroud
SAIC @ Sandia National Laboratories
ITSM Tester
Albuquerque, NM USA
(505)844-7983
[email protected]
From: Action Request System discussion list(ARSList)
[mailto:[email protected]] On Behalf Of Mayfield, Andy L.
Sent: Tuesday, July 31, 2012 2:23 PM
To: [email protected]
Subject: [EXTERNAL] DDE Commands
**
Does anyone have, or know where I can get, a complete list of DDE commands
for Excel?
I'll settle for whatever information I can get.
Thanks,
Andy L. Mayfield
Alabama Power Company
Protection & Control Technician Sr.
Linc # 10*19140
Cell # 205-288-9140
Office # 205-257-1556
_attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_
_attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_
_attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_
_attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
