11 replies to this topic

[tkv89]

I know that most forum members don't really use DDE much, but we tend to use it extensively here in my company.

However, I have an issue regarding using EXCEL at the same time that the DDE PROGRAM is running.

For example, the Workbook Name is Book1
When I execute #S = DDE_EXECUTE('EXCEL', 'Book1', '[WORKBOOK.INSERT(1)]'), it should be creating a new worksheet in the Book1.

If for example, the focus of my screen is on another EXCEL workbook (e.g. ANOTHER), the new worksheet will be inserted in the open EXCEL workbook instead, even though I have specified that the new worksheet should be created in Book1.

Does anyone know if there's a way around this? Or if this a DDE bug?

Thanks for all the help.

[tkv89]

Yeah, I kinda figured that out. The problem is I can't quite figure out WHY it doesn't work, seeing as we have to be detailed enough to name the workbook we are sending the command to.

[DARREN]

Are you on Version 6.2? If so, you can negate using DDE altogether with the use of the XMLFO transform feature. The demo functions that accompany 6.2 use it to create PDF's and it is also used when printing the source code from Developer, but it can be used for other types of documents like word and excel.

The physical documents are created on the server and can then be directed to the screen (the mime type associated with the document opens the relevant application) or they can be e-mailed to a recipient via an SSO.

I have attached an xsl style sheet and Developer export file that demonstrates this. This was created by Wim Soutendijk at Airtrade as is passed on with his permission.

The xsl style sheet has the attachement extension of .txt so just rename it after you have downloaded it.

We switched to this method for two reasons. Firstly, our patient ran out with the temperamental behavior of DDE and secondly to remove any client side dependency when creating documents with the latter a pre-requisite to moving to a browser based application.

What we also discovered when implementing this is that properties/values for a desired spreadsheet can be obtained by creating/amending a spreadsheet and then saving it in XML format. This provides the exact attribute mappings that can then be defined in the xsl.

This is not the only method for achieving this and I am aware that the guys at ResourceLink have used a SSO to achieve the same result. I am hoping that if I prod them enough they may "share the love" on their implementation method.

I have also included a snippet of code from our app using the email SSO that comes bundled with 6.2. In the example I am using it to e-mail an xml document, but it would be the same for any file that resides on the server.

From Wim:

Hello Darren,

I created a report based on the PRO-IV demo system. You should have the file defs for them.

Copy the xsl document in to C:\Program Files\Northgate\PROIV Version 6\Virtual Machine\xsl (or whereever your PROIV_XSL points to) and import the function in the demo environment.

A lot more formating is possible - hope this gives you a basic idea on how it works.

Wim.

The syntax via the use of the SET_RPTOPT command is
// transformation method
//
SET_RPTOPT('/cms_xlrout/' + CDATE(TODAY(),'DDMMYY') + @TIME + '.xls:OUTFMT=X','13','C_XLROUT.xsl')

[codebox]//
//Disable rollback if the SSO method(s) fail.
//
SSOManager.ExceptionsRollBack(FALSE)
//
//HTTP POST request - all the parameters are in the content not the URL.
//
//Set up the URL.
//
$URL = &$ISOCLAIMINSERTURL
//
CALL 1
//
$BODY = 'xml=' + $BODY
//
HttpSSO.writeContent(WriteContent)
//
CALL 2 //Now display output.
//
//Close connection.
HttpSSO.closeConnection()
//
:1
//Create the URL for the SSO.
HttpSSO.createURLFromString(URL)
//Open the connection.
HttpSSO.openConnection()
RETURN
:2
//Get response code from the SSO
#STATUS = HttpSSO.getResponseCode()
//
IF (#STATUS <> 200)
$MESSAGE = SSOManager.GetExceptionMessage()
$STATUS = HttpSSO.getResponseMessage()
$EMAIL_MESSAGE = "Unable to call ISO resource. HTTP response code: " + CONV(#STATUS)
+ " response text: " + $STATUS + ' Exception Message ' + $MESSAGE
//
#ISO_STATUS = -1
CALL 4
ELSE
//Read content.
$CONTENT = HttpSSO.readContent(ReadContent)
CALL 3
//Error check
#E = INDEX($CONTENT,"")
// An error was returned in the content
IF (#E # 0) THEN
#EE = INDEX($CONTENT,"")
IF (#EE # 0) THEN
$EMAIL_MESSAGE = "Error returned: " + $CONTENT(#E + LEN(""),#EE -1)
#ISO_STATUS = -1
CALL 4
ENDIF
ENDIF
ENDIF
RETURN
:3
//Check for error.
$EMAIL_MESSAGE = SSOManager.GetExceptionMessage()
IF $EMAIL_MESSAGE # '' THEN
#ISO_STATUS = -1
CALL 4
ENDIF
RETURN
:4
//
// Send the e-mail, setting up all the data first.
//
// Set up the SSO properties from the entered values
EmailSSO.smtpServername = RM_SMTP_SERVER_NAME
EmailSSO.smtpUsername = RM_SMTP_USER_NAME
EmailSSO.smtpPassword = RM_SMTP_USER_PASSWORD
EmailSSO.subject = 'ISO Claims Post Error Notification'
EmailSSO.smtpPort = CONV(RM_SMTP_PORT)
//
// Use a method to set the sender
//
EmailSSO.setFrom(From)
//
// and the recipient
//
EmailSSO.appendTo(Recipient)
//
// Append the message - this allows long messages
//
EmailSSO.appendMessage(Message)
//
// Attach the xml document to the e-mail
//
EmailSSO.addAttachment(Attachement)
//
// Send the mail
//
EmailSSO.send()
//
//Clear the 'To', 'Cc','Message' and 'Attachements' buffers
//
EmailSSO.clearToRecipients()
EmailSSO.clearCcRecipients()
EmailSSO.clearMessage()
EmailSSO.clearAttachments()
RETURN[/codebox]

Attached Files

•  excelreport.out   6.22KB   12 downloads
•  ExcelReport.txt   2.59KB   23 downloads

Edited by DARREN, 23 April 2010 - 12:50 PM.

[Vol Yip]

Hi all,

Just a follow up question on the DDE_EXECUTE.

I am using PROIV 4.6. I have been using DDE_EXECUTE on Excel for a while an so far it works ok. Recently I tried to do a Worksheet rename by using the following codes:

$OLD = 'Sheet1'
$NEW = 'Main1'
$$CMD = '[WORKBOOK.NAME("$OLD","$NEW")]'
#STAT = DDE_EXECUTE('EXCEL','SYSTEM',$$CMD)

It just doesn't work at all.
I tried also

$OLD = 'Sheet1'
$NEW = 'Main1'
$$CMD = '[WORKBOOK.NAME("Sheet1", "Main1"]'
#STAT = DDE_EXECUTE('EXCEL','SYSTEM',$$CMD)

but also in vain.

Does anyone has the worksheet rename working before?

Regards,

Vol

[tkv89]

This is a bit off the forum topic

Anyway, to rename the worksheet, you have to use the full name. So instead of

'[WORKBOOK.NAME("Sheet1", "Main1"]'

you should be using

'[WORKBOOK.NAME("[Book1]Sheet1", "[Book1]Main1"]'

The name of the workbook is dynamic (based on the number of open workbooks). You'll have to get the name from a DDE_REQUEST, to play safe.....

[tkv89]

Argh, forgot to put this in my previous post. There's also a slight bug in the DDE commands. The renaming will not work properly. To get around this, you'll have to hide the tabs of the worksheets, rename them, then show the tabs of the worksheets. It's one of the options in the Excel workbook (don't have the Macro help file with me at the moment). You can search around this forum and find a link to download it.


Also
#STAT = DDE_EXECUTE('EXCEL','SYSTEM',$$CMD)

I'm not sure whether this will have any impact, but I typically replace the 'SYSTEM' with the object I want to manipulate. For example, I would use
#STAT = DDE_EXECUTE('EXCEL',$WORKBOOK,$$CMD)
where $WORKBOOK contains my Workbook name. It works fine so far.

Actually, this brings me back to the core of this topic. Most of the time, if you don't specify it correctly (Which workbook, which sheet or which CHART), it fails to work. 'SYSTEM' is a catch-all that basically says "active object" (as far as I know). Why is it that it even though I specify the exact workbook, it still opens in a different workbook?....ah well......

[tkv89]

...I really, really have to get out of the habit of answering fast without checking in detail.

ANYWAY, found a copy of it on my house computer.

The syntax is OPTIONS.VIEW(formula, status, notes, show_info, object_num, page_breaks, formulas, gridlines, color_num, headers, outline, zeros, hor_scroll, vert_scroll, sheet_tabs)

You're interested in the last one so your command should be [OPTIONS.VIEW(,, , , , , , , , , , , , , FALSE)] <- don't copy this exactly, may have missed a comma or two

So Hide it, rename, then show it (set to TRUE). This should work (if not, try putting the FALSE as "FALSE")

[Vol Yip]

Thanks, but it still doesn't work for me

I'll try to do more experiments and see if I can have any luck.

[tkv89]

Thanks, but it still doesn't work for me

I'll try to do more experiments and see if I can have any luck.

Sorry I took so long to update this, was at a customer's site. Anyway, check with the following snippet. I'm using it now and there's no problem with renaming

#STAT = DDE_REQUEST('EXCEL','SYSTEM','SELECTION', $SHEET)
#POS = INDEX($SHEET,'!')
$SHEET = $SHEET(1, (#POS - 1))
$RAW_COTTON_SHEET = $SHEET

#POS2 = INDEX($SHEET,']')
$WORKBOOK = $SHEET(1, (#POS2))
$WORKBOOK_REF = $SHEET(2, (#POS2 - 1))
$RAW_COTTON_NAME = $WORKBOOK + 'Raw Cotton'

$$HIDE_TABS = '[OPTIONS.VIEW(,,,,,,,,,,,,,,FALSE)]'
$$SHOW_TABS = '[OPTIONS.VIEW(,,,,,,,,,,,,,,TRUE)]'
$$RENAME_COTTON = '[WORKBOOK.NAME("' + $RAW_COTTON_SHEET + '","' + $RAW_COTTON_NAME + '")]'

#S = DDE_EXECUTE('EXCEL', $WORKBOOK_REF, $$HIDE_TABS)
#S = DDE_EXECUTE('EXCEL', $WORKBOOK_REF, $$RENAME_COTTON)
#S = DDE_EXECUTE('EXCEL', $WORKBOOK_REF, $$SHOW_TABS)

[Vol Yip]

Wow, it works. Thanks very much.

But unfortunately I could not convince my customer to use my "multiple sheet" approach so I have to go with the hard way....... putting everything into one single sheet

[Saurabh Goyal]

Hi All,

Just to let you know if you will upgrade to PROIV Version 6.2 and use SSO (Service Side Objects), PROIV has the Excel SSO Package (contact US Sales for pricing) which will allow you to build Excel files without calling DDE commands. Even you can email those Excel files directly from PROIV again using the power of Email SSO (Server Side Objects). We have serveral customer who in the past use the DDE command to built Excel and migrate to SSO. Here is the list of the features supported by SSO at this time:

* - Set Excel File Name
* - Set Excel Worksheet Name
* - Set Font Name (Tahoma, Arial, Times, Courier)
* - Set Font Size & Color (limited color options)
* - Set Font Style (Italic or Regular)
* - Set Font Weight (Bold or Normal)
* - Merge Multiple Excel Row & Cell
* - Set Cell Background Color (limited color options)
* - Set Cell Alignment (Left, Right, Fill, Center, General, Justify)
* - Set Cell Width
* - Write Excel Cell as General
* - Write Excel Cell as Number (With Or Without 2 Decimals)
* - Write Excel Cell As Currency (With $ Prefix and With 2 Decimals)
* - Write Excel Cell As Date (MM/DD/YYYY [US Format])
* - Write Excel Cell As Image (very limited, PNG image types only)
* - Write Excel Cell As Hyperlink
* - Add Box To Excel Cell
* - Vertical Align Excel Cell
* - Add Page Break at Specified Row
* - Write Excel Cell As Date (DD/MM/YYYY [UK Format])
* - Set Worksheet To Readonly with or without Password Protection
* - Write Excel Cell As Currency (With $ Prefix and Without 2 Decimals)
* - Write Excel Cell As Number (No Formatting, With Or Without 2 Decimals)
* - Add Multiple Worksheets To Single Spreadsheet
* - Excel Formula Support
* - Read Excel Cell Content
* - Write Excel Cell As Custom Number Format
* - Write Excel Cell As Custom Date Format
* - Set Worksheet Page Orientation - "P"ortrait (Default) or "L"andscape
* - Set Worksheet Horizontal Freeze
* - Set Worksheet Vertical Freeze
* - Set Worksheet Default Column Width
* - Set Worksheet Default Row Height
* - Set Worksheet Hidden in Excel File
* - Set Worksheet Vertically To Fit In Pages - (Print or Print Preview)
* - Set Worksheet Widthwise To Fit In Pages - (Print or Print Preview)
* - Set Worksheet Header - (Print or Print Preview)
* - Set Worksheet Footer - (Print or Print Preview)
* - Set Worksheet Top Margin - (Print or Print Preview)
* - Set Worksheet Bottom Margin - (Print or Print Preview)
* - Set Worksheet Left Margin - (Print or Print Preview)
* - Set Worksheet Right Margin - (Print or Print Preview)
* - Set Worksheet Vertical Center - (Print or Print Preview)
* - Set Worksheet Horizontal Center - (Print or Print Preview)
* - Set Worksheet To Scale - (Print or Print Preview)
* - Set Worksheet Default Number Of Copies - (Print Only)
* - Hide Worksheet Row or Column

For you reference here also attached the Simple Excel file generated from PROIV Version 6.2 using Excel SSO.
 EntertainmentCatalog.xls   20.5KB   139 downloads

If you have any question regarding the SSO or Version 6.2 upgrade, please contact PROIV US Sales Office.

Thanks,
Saurabh

Edited by Saurabh Goyal, 21 June 2011 - 11:59 PM.