Open Excel From 4GL

This looks like a similar method to what I used. You have a few more
commands in yours that may come in handy for me later.

Thanks,

Mark

--- In vantage@yahoogroups.com, "Jeff Lewis" <jeff@...> wrote:
>
> One way to do this is to open a xls file from 4GL like this --
>
> OS-COMMAND /*SILENT*/ NO-WAIT
T:\DATA\Excel\Sales\MustShipList.xls. /*
> just a blank workbook with some code to format the csv */
>
> In that file you have this code --
>
> Private Sub Workbook_Open()
> ImportFormatPrint
> End Sub
>
> Sub ImportFormatPrint()
> '
> ' ImportFormatPrint Macro
> ' Macro written 10/22/2001 by Jeff
> '
> Workbooks.Open Filename:="T:\Data\Excel\SALES\ShopLoad.csv"
>
> Columns("O:O").ColumnWidth = 90
> Columns("O:O").Select
> With Selection
> .HorizontalAlignment = xlGeneral
> .VerticalAlignment = xlBottom
> .WrapText = True
> ................. general formating code (make that csv look
> pretty!)
>
> Workbooks("MustShipList.xls").Close SaveChanges:=False '(auto-
shut
> this workbook so someone else can use it!)
> End Sub
>
> Very simple to put together and easy to maintain.
> Jeff
>
> ________________________________
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On
Behalf
> Of markewhittaker
> Sent: Wednesday, April 11, 2007 1:25 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Open Excel From 4GL
>
>
>
> There is a link in the Files/Progress Code section of this forum
that
> is supposed to contain the code to export a table to .csv file and
open
> that file with the program related to .csv (i.e. Excel). The link
is
> no longer any good, and I tried to email the person who created it
and
> their email is no longer valid. I have searched half the day for an
> example of how to do this with no luck.
>
> Anyone know how to accomplish this? I created a program which
outputs
> to a .csv file and I would like to give the user the option of
having
> the file automatically load into Excel. Any help would be greatly
> appreciated.
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
There is a link in the Files/Progress Code section of this forum that
is supposed to contain the code to export a table to .csv file and open
that file with the program related to .csv (i.e. Excel). The link is
no longer any good, and I tried to email the person who created it and
their email is no longer valid. I have searched half the day for an
example of how to do this with no luck.

Anyone know how to accomplish this? I created a program which outputs
to a .csv file and I would like to give the user the option of having
the file automatically load into Excel. Any help would be greatly
appreciated.
One way to do this is to open a xls file from 4GL like this --

OS-COMMAND /*SILENT*/ NO-WAIT T:\DATA\Excel\Sales\MustShipList.xls. /*
just a blank workbook with some code to format the csv */

In that file you have this code --

Private Sub Workbook_Open()
ImportFormatPrint
End Sub

Sub ImportFormatPrint()
'
' ImportFormatPrint Macro
' Macro written 10/22/2001 by Jeff
'
Workbooks.Open Filename:="T:\Data\Excel\SALES\ShopLoad.csv"

Columns("O:O").ColumnWidth = 90
Columns("O:O").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
................. general formating code (make that csv look
pretty!)

Workbooks("MustShipList.xls").Close SaveChanges:=False '(auto-shut
this workbook so someone else can use it!)
End Sub

Very simple to put together and easy to maintain.
Jeff

________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of markewhittaker
Sent: Wednesday, April 11, 2007 1:25 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Open Excel From 4GL



There is a link in the Files/Progress Code section of this forum that
is supposed to contain the code to export a table to .csv file and open
that file with the program related to .csv (i.e. Excel). The link is
no longer any good, and I tried to email the person who created it and
their email is no longer valid. I have searched half the day for an
example of how to do this with no luck.

Anyone know how to accomplish this? I created a program which outputs
to a .csv file and I would like to give the user the option of having
the file automatically load into Excel. Any help would be greatly
appreciated.






[Non-text portions of this message have been removed]
If you are looking to do this from a .p file in 6.1 from a BAM, I am at
least partially responsible and can offer some code that might help.
In my case I cheated a bit. I gave up on loading excel directly and instead
created a shortcut to excel (copied from the Start Menu) and put it into the
c:\Epicor directory and used the following code to launch excel with it.

DOS SILENT 'c:\Epicor\excel.lnk C:\Epicor\POInfo.CSV'.

It works flawlessly and for the optionally loading part, if the shortcut is
in the Epicor directory, it launches Excel, if not, it ignores the code.
Good luck,
Hope this helps you or someone else.
Aaron Hoyt
Vantage Plastics

-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]On Behalf Of
markewhittaker
Sent: Wednesday, April 11, 2007 2:25 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Open Excel From 4GL


There is a link in the Files/Progress Code section of this forum that
is supposed to contain the code to export a table to .csv file and open
that file with the program related to .csv (i.e. Excel). The link is
no longer any good, and I tried to email the person who created it and
their email is no longer valid. I have searched half the day for an
example of how to do this with no luck.

Anyone know how to accomplish this? I created a program which outputs
to a .csv file and I would like to give the user the option of having
the file automatically load into Excel. Any help would be greatly
appreciated.






[Non-text portions of this message have been removed]
Thanks for the reply. I was able to find some code this morning that
works well and provides a lot of power to manipulate what goes into
the spreadsheet. Here is a copy below. Hope it doesn't look too
choppy.

DEFINE VARIABLE hExcel AS COM-HANDLE.
DEFINE VARIABLE hws AS COM-HANDLE.
DEFINE VARIABLE hwb AS COM-HANDLE.
DEFINE VARIABLE expxls AS LOGICAL LABEL "Open In Excel?" VIEW-AS
TOGGLE-BOX SIZE-PIXELS 125 BY 50 TOOLTIP "Select this box to see the
rejected records in Excel after import is done".

CREATE "Excel.Application" hExcel.
IF NOT VALID-HANDLE(hExcel) THEN RETURN.
hExcel:Visible = TRUE. /* Use FALSE if you don't want to see excel
work */
hwb = hExcel:WorkBooks:ADD.
hws = hwb:worksheets:ADD.
hws:COLUMNS("a"):columnwidth = 10.
hws:COLUMNS("b"):columnwidth = 30.
hws:COLUMNS("c"):columnwidth = 12.
hws:COLUMNS("d"):columnwidth = 10.
hws:COLUMNS("e"):columnwidth = 12.
hws:COLUMNS("f"):columnwidth = 12.
hws:COLUMNS("g"):columnwidth = 35.
hws:range("a1:g1"):FONT:bold = TRUE.
hws:range("a1"):value = "Plant".
hws:range("b1"):value = "Part".
hws:range("c1"):value = "Customer ID".
hws:range("d1"):value = "Date".
hws:range("e1"):value = "Forecast Qty".
hws:range("f1"):value = "Consumed Qty".
hws:range("g1"):value = "Error Message".
rownum = 2.
FOR EACH checkparts:
crownum = STRING(rownum).
crange = "a" + crownum.
hws:range(crange):VALUE = checkparts.plant.
crange = "b" + crownum.
hws:range(crange):VALUE = checkparts.partnum.
crange = "c" + crownum.
hws:range(crange):VALUE = checkparts.custid.
crange = "d" + crownum.
hws:range(crange):VALUE = checkparts.foredate.
crange = "e" + crownum.
hws:range(crange):VALUE = checkparts.foreqty.
crange = "f" + crownum.
hws:range(crange):VALUE = checkparts.consumedqty.
crange = "g" + crownum.
hws:range(crange):VALUE = checkparts.errormsg.
rownum = rownum + 1.
END. /*for each checkparts*/
RELEASE OBJECT hws.
RELEASE OBJECT hwb.
RELEASE OBJECT hExcel.

--- In vantage@yahoogroups.com, "Aaron Hoyt" <aaron.hoyt@...> wrote:
>
> If you are looking to do this from a .p file in 6.1 from a BAM, I
am at
> least partially responsible and can offer some code that might help.
> In my case I cheated a bit. I gave up on loading excel directly
and instead
> created a shortcut to excel (copied from the Start Menu) and put it
into the
> c:\Epicor directory and used the following code to launch excel
with it.
>
> DOS SILENT 'c:\Epicor\excel.lnk C:\Epicor\POInfo.CSV'.
>
> It works flawlessly and for the optionally loading part, if the
shortcut is
> in the Epicor directory, it launches Excel, if not, it ignores the
code.
> Good luck,
> Hope this helps you or someone else.
> Aaron Hoyt
> Vantage Plastics
>
> -----Original Message-----
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]On
Behalf Of
> markewhittaker
> Sent: Wednesday, April 11, 2007 2:25 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Open Excel From 4GL
>
>
> There is a link in the Files/Progress Code section of this forum
that
> is supposed to contain the code to export a table to .csv file
and open
> that file with the program related to .csv (i.e. Excel). The link
is
> no longer any good, and I tried to email the person who created
it and
> their email is no longer valid. I have searched half the day for
an
> example of how to do this with no luck.
>
> Anyone know how to accomplish this? I created a program which
outputs
> to a .csv file and I would like to give the user the option of
having
> the file automatically load into Excel. Any help would be greatly
> appreciated.
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>