Create Excel file from bpm

system · April 25, 2013, 9:03am

True enough on the languages. I will setup some tests today.

Thanks for the suggestion.

Greg

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of brenda mohr
Sent: Thursday, April 25, 2013 8:31 AM
To: vantage@yahoogroups.com
Subject: [Vantage] RE: Create Excel file from bpm

Seems to me yes.... It can be gotten around by using the old ASCII Characters. (ie CHR(13) + CHR(10) for CRLF) Same Ol Same Ol You might have to experiment. Maybe delimit w/ some "safe" character (PartNum + CHR(59) + PartDesc) OR (PartNum + " ; " + PartDesc). I may even have used the INDEX / REPLACE to make ' ''' so it does parse correct depending on syntax. After Â½ dozen languages or so you forget what is required by each ~ alas...

Brenda

From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>] On Behalf Of Greg Payne
Sent: Thursday, April 25, 2013 8:03 AM
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>
Subject: [Vantage] RE: Create Excel file from bpm

it is a list of parts for a vendor to quote and return the spreadsheet to us. This would be simpler and not involve using Excel on the server.

are there any issues to watch for in building the comma delimited string? Like a part or description with a comma or single quote.

Greg

From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>] On Behalf Of brenda mohr
Sent: Thursday, April 25, 2013 7:19 AM
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>
Subject: [Vantage] RE: Create Excel file from bpm

You say "create generic" How generic? I use the following code and my users just open it in excel all is good...

DEF VAR Outfile AS Char format 'X(1000)' no-undo.
DEF VAR OString as Char format 'X(1000)' no-undo.

Outfile = "\\HUMEPICOR\SCWork\BrendasJOBTEST.csv<file:///<file:///\\><file:///<file:///\\>\\>\\HUMEPICOR\SCWork\BrendasJOBTEST.csv>".
OUTPUT TO VALUE(Outfile).

OString = "Available".
PUT UNFORMATTED Ostring SKIP(0).

Brenda

From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>] On Behalf Of Greg Payne
Sent: Wednesday, April 24, 2013 4:44 PM
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Create Excel file from bpm

I am having a few issues with a bpm I am writing to create an excel file. I started with some sample code and got most of it working. Excel is on a Server 2008R2 64bit machine.

the saveas and the workbook:close are throwing errors.

Invalid component-handle referenced while processing method/statement: CLOSE. is from theWorkBook

On the saveas, I want to overwrite the file. How would I set the XLSaveConflictResulution to 2, for the local user's changes are always accepted.

I have tried a lot of variations that I have found when searching, but not solved it yet.

Thanks for any suggestions.

Greg Payne

/* create generic excel file */

DEFINE VARIABLE vchExcelAppl AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE vchWorkBook AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE vchWorkSheet AS COM-HANDLE NO-UNDO.

DEFINE VARIABLE MyVariable AS CHARACTER NO-UNDO.

define variable InfoMsg as character init ''.

CREATE "Excel.Application" vchExcelAppl.
vchExcelAppl:VISIBLE = false.
vchWorkBook = vchExcelAppl:WorkBooks:Open("E:\EpicorOUT\RFQTemplate.xlsx").

vchWorkSheet = vchExcelAppl:Sheets:Item(1).

/* Set a Value */

vchWorkSheet:Range("A2"):Value = "My Value".

/* Get A Value */

MyVariable = vchWorkSheet:Range("A2"):Text.

message "Value is " + string(MyVariable).

/* Save File */

vchWorkBook = vchWorkBook:SaveAs("E:\EpicorOUT\RFQ_Vendor.xlsx",,,,,,,2).

vchWorkBook:CLOSE().
RELEASE OBJECT vchWorkSheet.
RELEASE OBJECT vchWorkBook.

/*
vchExcelAppl:Quit.
RELEASE OBJECT vchExcelAppl.

ASSIGN vchWorkSheet = ?
vchWorkBook = ?
vchExcelAppl = ?.

*/

________________________________
CONFIDENTIALITY NOTICE

The information contained in this communication, including attachments, is privileged and confidential. It is intended only for the exclusive use of the addressee. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us at 727-578-6280 and immediately delete the communication.

"This (document/presentation) may contain technical data as defined in the International Traffic In Arms Regulations (ITAR) 22 CFR 120.10. Export of this material is restricted by the Arms Export Control Act (22 U.S.C. 2751 et seq.) and may not be exported to foreign persons without prior approval form the U.S. Department of State."

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

________________________________
CONFIDENTIALITY NOTICE

The information contained in this communication, including attachments, is privileged and confidential. It is intended only for the exclusive use of the addressee. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us at 727-578-6280 and immediately delete the communication.

"This (document/presentation) may contain technical data as defined in the International Traffic In Arms Regulations (ITAR) 22 CFR 120.10. Export of this material is restricted by the Arms Export Control Act (22 U.S.C. 2751 et seq.) and may not be exported to foreign persons without prior approval form the U.S. Department of State."

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

________________________________
CONFIDENTIALITY NOTICE

The information contained in this communication, including attachments, is privileged and confidential. It is intended only for the exclusive use of the addressee. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us at 727-578-6280 and immediately delete the communication.

"This (document/presentation) may contain technical data as defined in the International Traffic In Arms Regulations (ITAR) 22 CFR 120.10. Export of this material is restricted by the Arms Export Control Act (22 U.S.C. 2751 et seq.) and may not be exported to foreign persons without prior approval form the U.S. Department of State."

[Non-text portions of this message have been removed]

system · April 24, 2013, 4:43pm

I am having a few issues with a bpm I am writing to create an excel file. I started with some sample code and got most of it working. Excel is on a Server 2008R2 64bit machine.

the saveas and the workbook:close are throwing errors.

Invalid component-handle referenced while processing method/statement: CLOSE. is from theWorkBook

On the saveas, I want to overwrite the file. How would I set the XLSaveConflictResulution to 2, for the local user's changes are always accepted.

I have tried a lot of variations that I have found when searching, but not solved it yet.

Thanks for any suggestions.

Greg Payne

/* create generic excel file */

DEFINE VARIABLE vchExcelAppl AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE vchWorkBook AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE vchWorkSheet AS COM-HANDLE NO-UNDO.

DEFINE VARIABLE MyVariable AS CHARACTER NO-UNDO.

define variable InfoMsg as character init ''.

CREATE "Excel.Application" vchExcelAppl.
vchExcelAppl:VISIBLE = false.
vchWorkBook = vchExcelAppl:WorkBooks:Open("E:\EpicorOUT\RFQTemplate.xlsx").

vchWorkSheet = vchExcelAppl:Sheets:Item(1).

/* Set a Value */

vchWorkSheet:Range("A2"):Value = "My Value".

/* Get A Value */

MyVariable = vchWorkSheet:Range("A2"):Text.

message "Value is " + string(MyVariable).

/* Save File */

vchWorkBook = vchWorkBook:SaveAs("E:\EpicorOUT\RFQ_Vendor.xlsx",,,,,,,2).

vchWorkBook:CLOSE().
RELEASE OBJECT vchWorkSheet.
RELEASE OBJECT vchWorkBook.

/*
vchExcelAppl:Quit.
RELEASE OBJECT vchExcelAppl.

ASSIGN vchWorkSheet = ?
vchWorkBook = ?
vchExcelAppl = ?.

*/

________________________________
CONFIDENTIALITY NOTICE

The information contained in this communication, including attachments, is privileged and confidential. It is intended only for the exclusive use of the addressee. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us at 727-578-6280 and immediately delete the communication.

"This (document/presentation) may contain technical data as defined in the International Traffic In Arms Regulations (ITAR) 22 CFR 120.10. Export of this material is restricted by the Arms Export Control Act (22 U.S.C. 2751 et seq.) and may not be exported to foreign persons without prior approval form the U.S. Department of State."

[Non-text portions of this message have been removed]

system · April 25, 2013, 7:18am

You say "create generic" How generic? I use the following code and my users just open it in excel all is good...

DEF VAR Outfile AS Char format 'X(1000)' no-undo.
DEF VAR OString as Char format 'X(1000)' no-undo.

Outfile = "\\HUMEPICOR\SCWork\BrendasJOBTEST.csv".
OUTPUT TO VALUE(Outfile).

OString = "Available".
PUT UNFORMATTED Ostring SKIP(0).

Brenda

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Greg Payne
Sent: Wednesday, April 24, 2013 4:44 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Create Excel file from bpm

I am having a few issues with a bpm I am writing to create an excel file. I started with some sample code and got most of it working. Excel is on a Server 2008R2 64bit machine.

the saveas and the workbook:close are throwing errors.

Invalid component-handle referenced while processing method/statement: CLOSE. is from theWorkBook

On the saveas, I want to overwrite the file. How would I set the XLSaveConflictResulution to 2, for the local user's changes are always accepted.

I have tried a lot of variations that I have found when searching, but not solved it yet.

Thanks for any suggestions.

Greg Payne

/* create generic excel file */

DEFINE VARIABLE vchExcelAppl AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE vchWorkBook AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE vchWorkSheet AS COM-HANDLE NO-UNDO.

DEFINE VARIABLE MyVariable AS CHARACTER NO-UNDO.

define variable InfoMsg as character init ''.

CREATE "Excel.Application" vchExcelAppl.
vchExcelAppl:VISIBLE = false.
vchWorkBook = vchExcelAppl:WorkBooks:Open("E:\EpicorOUT\RFQTemplate.xlsx").

vchWorkSheet = vchExcelAppl:Sheets:Item(1).

/* Set a Value */

vchWorkSheet:Range("A2"):Value = "My Value".

/* Get A Value */

MyVariable = vchWorkSheet:Range("A2"):Text.

message "Value is " + string(MyVariable).

/* Save File */

vchWorkBook = vchWorkBook:SaveAs("E:\EpicorOUT\RFQ_Vendor.xlsx",,,,,,,2).

vchWorkBook:CLOSE().
RELEASE OBJECT vchWorkSheet.
RELEASE OBJECT vchWorkBook.

/*
vchExcelAppl:Quit.
RELEASE OBJECT vchExcelAppl.

ASSIGN vchWorkSheet = ?
vchWorkBook = ?
vchExcelAppl = ?.

*/

________________________________
CONFIDENTIALITY NOTICE

The information contained in this communication, including attachments, is privileged and confidential. It is intended only for the exclusive use of the addressee. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us at 727-578-6280 and immediately delete the communication.

"This (document/presentation) may contain technical data as defined in the International Traffic In Arms Regulations (ITAR) 22 CFR 120.10. Export of this material is restricted by the Arms Export Control Act (22 U.S.C. 2751 et seq.) and may not be exported to foreign persons without prior approval form the U.S. Department of State."

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

system · April 25, 2013, 8:03am

it is a list of parts for a vendor to quote and return the spreadsheet to us. This would be simpler and not involve using Excel on the server.

are there any issues to watch for in building the comma delimited string? Like a part or description with a comma or single quote.

Greg

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of brenda mohr
Sent: Thursday, April 25, 2013 7:19 AM
To: vantage@yahoogroups.com
Subject: [Vantage] RE: Create Excel file from bpm

You say "create generic" How generic? I use the following code and my users just open it in excel all is good...

DEF VAR Outfile AS Char format 'X(1000)' no-undo.
DEF VAR OString as Char format 'X(1000)' no-undo.

Outfile = "\\HUMEPICOR\SCWork\BrendasJOBTEST.csv<file:///\\HUMEPICOR\SCWork\BrendasJOBTEST.csv>".
OUTPUT TO VALUE(Outfile).

OString = "Available".
PUT UNFORMATTED Ostring SKIP(0).

Brenda

From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>] On Behalf Of Greg Payne
Sent: Wednesday, April 24, 2013 4:44 PM
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Create Excel file from bpm

I am having a few issues with a bpm I am writing to create an excel file. I started with some sample code and got most of it working. Excel is on a Server 2008R2 64bit machine.

the saveas and the workbook:close are throwing errors.

Invalid component-handle referenced while processing method/statement: CLOSE. is from theWorkBook

On the saveas, I want to overwrite the file. How would I set the XLSaveConflictResulution to 2, for the local user's changes are always accepted.

I have tried a lot of variations that I have found when searching, but not solved it yet.

Thanks for any suggestions.

Greg Payne

/* create generic excel file */

DEFINE VARIABLE vchExcelAppl AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE vchWorkBook AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE vchWorkSheet AS COM-HANDLE NO-UNDO.

DEFINE VARIABLE MyVariable AS CHARACTER NO-UNDO.

define variable InfoMsg as character init ''.

CREATE "Excel.Application" vchExcelAppl.
vchExcelAppl:VISIBLE = false.
vchWorkBook = vchExcelAppl:WorkBooks:Open("E:\EpicorOUT\RFQTemplate.xlsx").

vchWorkSheet = vchExcelAppl:Sheets:Item(1).

/* Set a Value */

vchWorkSheet:Range("A2"):Value = "My Value".

/* Get A Value */

MyVariable = vchWorkSheet:Range("A2"):Text.

message "Value is " + string(MyVariable).

/* Save File */

vchWorkBook = vchWorkBook:SaveAs("E:\EpicorOUT\RFQ_Vendor.xlsx",,,,,,,2).

vchWorkBook:CLOSE().
RELEASE OBJECT vchWorkSheet.
RELEASE OBJECT vchWorkBook.

/*
vchExcelAppl:Quit.
RELEASE OBJECT vchExcelAppl.

ASSIGN vchWorkSheet = ?
vchWorkBook = ?
vchExcelAppl = ?.

*/

________________________________
CONFIDENTIALITY NOTICE

The information contained in this communication, including attachments, is privileged and confidential. It is intended only for the exclusive use of the addressee. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us at 727-578-6280 and immediately delete the communication.

"This (document/presentation) may contain technical data as defined in the International Traffic In Arms Regulations (ITAR) 22 CFR 120.10. Export of this material is restricted by the Arms Export Control Act (22 U.S.C. 2751 et seq.) and may not be exported to foreign persons without prior approval form the U.S. Department of State."

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

________________________________
CONFIDENTIALITY NOTICE

The information contained in this communication, including attachments, is privileged and confidential. It is intended only for the exclusive use of the addressee. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us at 727-578-6280 and immediately delete the communication.

"This (document/presentation) may contain technical data as defined in the International Traffic In Arms Regulations (ITAR) 22 CFR 120.10. Export of this material is restricted by the Arms Export Control Act (22 U.S.C. 2751 et seq.) and may not be exported to foreign persons without prior approval form the U.S. Department of State."

[Non-text portions of this message have been removed]

system · April 25, 2013, 8:30am

Seems to me yes.... It can be gotten around by using the old ASCII Characters. (ie CHR(13) + CHR(10) for CRLF) Same Ol Same Ol You might have to experiment. Maybe delimit w/ some "safe" character (PartNum + CHR(59) + PartDesc) OR (PartNum + " ; " + PartDesc). I may even have used the INDEX / REPLACE to make ' ''' so it does parse correct depending on syntax. After Â½ dozen languages or so you forget what is required by each ~ alas...

Brenda

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Greg Payne
Sent: Thursday, April 25, 2013 8:03 AM
To: vantage@yahoogroups.com
Subject: [Vantage] RE: Create Excel file from bpm

it is a list of parts for a vendor to quote and return the spreadsheet to us. This would be simpler and not involve using Excel on the server.

are there any issues to watch for in building the comma delimited string? Like a part or description with a comma or single quote.

Greg

From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>] On Behalf Of brenda mohr
Sent: Thursday, April 25, 2013 7:19 AM
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>
Subject: [Vantage] RE: Create Excel file from bpm

You say "create generic" How generic? I use the following code and my users just open it in excel all is good...

DEF VAR Outfile AS Char format 'X(1000)' no-undo.
DEF VAR OString as Char format 'X(1000)' no-undo.

Outfile = "\\HUMEPICOR\SCWork\BrendasJOBTEST.csv<file:///<file:///\\>\\HUMEPICOR\SCWork\BrendasJOBTEST.csv>".
OUTPUT TO VALUE(Outfile).

OString = "Available".
PUT UNFORMATTED Ostring SKIP(0).

Brenda

From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>] On Behalf Of Greg Payne
Sent: Wednesday, April 24, 2013 4:44 PM
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Create Excel file from bpm

I am having a few issues with a bpm I am writing to create an excel file. I started with some sample code and got most of it working. Excel is on a Server 2008R2 64bit machine.

the saveas and the workbook:close are throwing errors.

Invalid component-handle referenced while processing method/statement: CLOSE. is from theWorkBook

On the saveas, I want to overwrite the file. How would I set the XLSaveConflictResulution to 2, for the local user's changes are always accepted.

I have tried a lot of variations that I have found when searching, but not solved it yet.

Thanks for any suggestions.

Greg Payne

/* create generic excel file */

DEFINE VARIABLE vchExcelAppl AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE vchWorkBook AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE vchWorkSheet AS COM-HANDLE NO-UNDO.

DEFINE VARIABLE MyVariable AS CHARACTER NO-UNDO.

define variable InfoMsg as character init ''.

CREATE "Excel.Application" vchExcelAppl.
vchExcelAppl:VISIBLE = false.
vchWorkBook = vchExcelAppl:WorkBooks:Open("E:\EpicorOUT\RFQTemplate.xlsx").

vchWorkSheet = vchExcelAppl:Sheets:Item(1).

/* Set a Value */

vchWorkSheet:Range("A2"):Value = "My Value".

/* Get A Value */

MyVariable = vchWorkSheet:Range("A2"):Text.

message "Value is " + string(MyVariable).

/* Save File */

vchWorkBook = vchWorkBook:SaveAs("E:\EpicorOUT\RFQ_Vendor.xlsx",,,,,,,2).

vchWorkBook:CLOSE().
RELEASE OBJECT vchWorkSheet.
RELEASE OBJECT vchWorkBook.

/*
vchExcelAppl:Quit.
RELEASE OBJECT vchExcelAppl.

ASSIGN vchWorkSheet = ?
vchWorkBook = ?
vchExcelAppl = ?.

*/

________________________________
CONFIDENTIALITY NOTICE

The information contained in this communication, including attachments, is privileged and confidential. It is intended only for the exclusive use of the addressee. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us at 727-578-6280 and immediately delete the communication.

"This (document/presentation) may contain technical data as defined in the International Traffic In Arms Regulations (ITAR) 22 CFR 120.10. Export of this material is restricted by the Arms Export Control Act (22 U.S.C. 2751 et seq.) and may not be exported to foreign persons without prior approval form the U.S. Department of State."

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

________________________________
CONFIDENTIALITY NOTICE

The information contained in this communication, including attachments, is privileged and confidential. It is intended only for the exclusive use of the addressee. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us at 727-578-6280 and immediately delete the communication.

"This (document/presentation) may contain technical data as defined in the International Traffic In Arms Regulations (ITAR) 22 CFR 120.10. Export of this material is restricted by the Arms Export Control Act (22 U.S.C. 2751 et seq.) and may not be exported to foreign persons without prior approval form the U.S. Department of State."

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]