Exporting To Excel.

S

smart.Object

Member
Hello everyone. I have encountered yet another problem in converting my program to a later version of progress (8 --> 9). In 8 I used to be able to export data to excel with this procedure:

Code: 
&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE p_graph dialog-1 
PROCEDURE p_graph :
/*------------------------------------------------------------------------------
  Purpose:     
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/
  
  def var xlfile as char init "carreportPRINT.xls" no-undo.
   
  {excel\xlmove.i}
  
    
  DDE EXECUTE sys COMMAND "[new(carreport)]" TIME 15 NO-ERROR. 
  DDE INITIATE sheet FRAME FRAME MainFrame:HANDLE APPLICATION "Excel" TOPIC "bestschedulereport" NO-ERROR.       
  rowi = 2.   
  
    
  
   for each car where
   car.car-no = hold-car-no
     use-index order-no no-lock
    break by car.car-no: 
    

    

 

find customer where customer.company = "01" and customer.cust-id = car.cust-id no-lock no-error.
find job-hdr where job-hdr.company = "01" and job-hdr.order-no = car.order-no no-lock no-error.
                           
                   DDE SEND sheet SOURCE string(car.init-by)
                         ITEM ("R" + STRING(rowi) + "C2") 
                         TIME 15 NO-ERROR.                 
                

            
            
            DDE SEND sheet SOURCE string(car.car-no)
                         ITEM ("R" + STRING(rowi) + "C1") 
                         TIME 15 NO-ERROR.                 
            DDE SEND sheet SOURCE car.order-no
                         ITEM ("R" + STRING(rowi) + "C3")
                         TIME 15 NO-ERROR.      
            DDE SEND sheet SOURCE customer.name
                         ITEM ("R" + STRING(rowi) + "C4")
                         TIME 15 NO-ERROR.                          
            DDE SEND sheet SOURCE job-hdr.slsprsn
                         ITEM ("R" + STRING(rowi) + "C5")
                         TIME 15 NO-ERROR.                                     
            DDE SEND sheet SOURCE car.description 
                         ITEM ("R" + STRING(rowi) + "C6") 
                         TIME 15 NO-ERROR.
            DDE SEND sheet SOURCE string(CAR.init-date)
                         ITEM ("R" + STRING(rowi) + "C7") 
                         TIME 15 NO-ERROR. 
            DDE SEND sheet SOURCE car.dept-resp
                         ITEM ("R" + STRING(rowi) + "C8")
                         TIME 15 NO-ERROR. 
            DDE SEND sheet SOURCE CAR.wrk-center
                         ITEM ("R" + STRING(rowi) + "C9")
                         TIME 15 NO-ERROR.         
            DDE SEND sheet SOURCE CAR.X-CHAR[4]
                         ITEM ("R" + STRING(rowi) + "C10")
                         TIME 15 NO-ERROR. 
                                     
                         rowi = rowi + 1.
                                                       
            DDE SEND sheet SOURCE car.pro-desc  
                         ITEM ("R" + STRING(rowi) + "C2")
                         TIME 15 NO-ERROR.
            DDE SEND sheet SOURCE "Problem:"  
                         ITEM ("R" + STRING(rowi) + "C1")
                         TIME 15 NO-ERROR.

                       
                         
            DDE SEND sheet SOURCE string(car.npb-no)
                         ITEM ("R" + STRING(rowi) + "C3") 
                         TIME 15 NO-ERROR. 
            DDE SEND sheet SOURCE CAR.cont-action
                         ITEM ("R" + STRING(rowi) + "C5")
                         TIME 15 NO-ERROR. 
            DDE SEND sheet SOURCE "Contain:"  
                         ITEM ("R" + STRING(rowi) + "C4")
                         TIME 15 NO-ERROR.

                       
                         
            DDE SEND sheet SOURCE car.preven-act
                         ITEM ("R" + STRING(rowi) + "C7")
                         TIME 15 NO-ERROR.
            DDE SEND sheet SOURCE "Cause:" 
                         ITEM ("R" + STRING(rowi) + "C6")
                         TIME 15 NO-ERROR.


                         
                         
            DDE SEND sheet SOURCE car.root-cause
                         ITEM ("R" + STRING(rowi) + "C9")
                         TIME 15 NO-ERROR.
            DDE SEND sheet SOURCE "Prevent:" 
                         ITEM ("R" + STRING(rowi) + "C8")
                         TIME 15 NO-ERROR.
                                                   
                     
                                                          
          ASSIGN
              rowi = rowi + 1.       
                   
         
        end.        
            
    hide frame f-menu no-pause.
    hide message no-pause.
   
/* End Query Section */
END PROCEDURE.
and this xlmove.i file:
Code: 
/* xlmove.i 

   jao - 02/25/98

   - copies the excel file into local temp dir so that any changes made
     in the spreadsheet will not be written to the master copy
   - also sets up the DDE link using a unique file name 

  Modifications:
    08/07/98 - jao - updated for 32-bit

*/

  DEF VAR v-path AS CHAR NO-UNDO.
  DEF VAR vReportsPath AS CHAR NO-UNDO.
  DEF VAR vString     AS CHAR NO-UNDO.
  DEF VAR vExcelPath  AS CHAR NO-UNDO.
  DEF VAR vExcelFiles AS CHAR NO-UNDO.      
  DEF VAR xltemp as char no-undo.
  def var hProcess as int no-undo.        
  def var nTask as int no-undo.
  
  IF excelon = TRUE THEN RETURN.
  BtnGraph:sensitive in frame {&FRAME-NAME}= NO.

  GET-KEY-VALUE SECTION "Shoppro Parameters" KEY "ExcelPath" VALUE vExcelPath.
  GET-KEY-VALUE SECTION "Shoppro Parameters" KEY "ExcelFiles" VALUE vExcelFiles.
   
  /* Get the starting directory for report files */
  get-key-value section "Shoppro Parameters" key "ReportsPath" value vReportsPath.
  ASSIGN v-path = IF vReportsPath = ? THEN 'temp\' ELSE vReportsPath.
  IF SUBSTRING(v-path,LENGTH(v-path),1) NE "\" THEN v-path = v-path + "\".

  do-block:
    DO WHILE TRUE:

        ASSIGN xltemp= 
             v-path  
             + STRING((TIME),'99999999') 
             + '.xls'. 
    
        IF SEARCH(xltemp) = ? THEN LEAVE do-block.

    END.
     
    os-copy value(vExcelFiles + xlfile) value(xltemp). 
  
    xltemp = search(xltemp).
       
  ASSIGN 
    vString = vExcelPath + "excel /e " + xltemp
    excelon = TRUE.
   
   RUN WinExec in hpApi(INPUT vString, INPUT 1, OUTPUT nTask).  /* 1=normal 2=minimized */ 

   DDE INITIATE sys FRAME FRAME MainFrame:HANDLE APPLICATION "Excel" TOPIC "System" NO-ERROR. 
   IF sys = 0 THEN DO:
      MESSAGE "Excel not available" VIEW-AS ALERT-BOX ERROR.
      excelon = FALSE.
      RETURN.
   END.
However in progress 9 it compiles fine but doesn't seem to INITIATE sys, setting it to 0 and returning "Excel not available" message.
I have been trying to get this to work for 4 days now and quite frankly dumbfounded by the situation.
I appreciate any help from you guys
Thanks -Greg.

P.S. both of those files work in progress 8.
 
S

sdjensen

Member
Hi,

try removing the no-error statements to see if the DDE-commands are triggering any error.

Also try using the full path to excel.
 
S

smart.Object

Member
Casper said:
what happens if you put a pause 2 (or 1 or 3 :)) before the dde initiate?

Casper.
Click to expand...
when I do that, the progress dialog pops up. On the bottom it said pause for 2 seconds, press space bar. Then "Excel not available" pops up again. What was the purpose of it?

sdjensen said:
Hi,

try removing the no-error statements to see if the DDE-commands are triggering any error.

Also try using the full path to excel.
Click to expand...
when I try removing the no-error from DDE initiate it gives me an error "DDE initiate failed (3153)", so you think its the DDE is responsible for it? Also I don't think there is an actual path to Excel, it somehow just pulls up Application server called "Excel" with that initiate statement. The other path is for the temp file that will be created according to the template, named carreportPRINT.xls.
Thanks...
 
G

ggabr

New Member
Hello,

I'm unfamilliar with Progress 8, but if you are willing to change your code logic, you could use Component Objects by declaring the statement

CREATE "Excel.Application"

and dealing with its internal procedures using progress sintax.

Hope I could help some way.
Cheers.
 
jongpau

jongpau

Member
ggabr said:
Hello,

I'm unfamilliar with Progress 8, but if you are willing to change your code logic, you could use Component Objects by declaring the statement

CREATE "Excel.Application"

and dealing with its internal procedures using progress sintax.

Hope I could help some way.
Cheers.
Click to expand...
Yes you can use COM, but there is also the option to create your Excel output in an xml file and then have Excel open the file for you using a Windows API call - I find this works a lot faster than using COM. It does have the drawback that you get some additional output files on your system that you may want to manage/control (garbage collection). Also, being xml, the files are a bit bigger than when you would save a native Excel xls file - but hey, life's never perfect :cool:

If you want to see what the Excel xml files look like, just make a little worksheet in Excel and save it as an xml file and open it in notepad or whatever other text editor you like - you can use that as a guideline to how to create the files (it's not that hard really).

Paul
 
S

sdjensen

Member
smart.Object said:
when I do that, the progress dialog pops up. On the bottom it said pause for 2 seconds, press space bar. Then "Excel not available" pops up again. What was the purpose of it?


when I try removing the no-error from DDE initiate it gives me an error "DDE initiate failed (3153)", so you think its the DDE is responsible for it? Also I don't think there is an actual path to Excel, it somehow just pulls up Application server called "Excel" with that initiate statement. The other path is for the temp file that will be created according to the template, named carreportPRINT.xls.
Thanks...
Click to expand...
in xlmove.i you are setting the excelpath, but if that variable is not set or the path is wrong then it could explain why you receive the "excel is not avail".

Code: 
GET-KEY-VALUE SECTION "Shoppro Parameters" KEY "ExcelPath" VALUE vExcelPath.

ASSIGN 
    vString = vExcelPath + "excel /e " + xltemp
    excelon = TRUE.

What version if excel are you using?
My excel.exe is placed in "c:\program files\microsoft office\office11\excel.exe".

Try adding the full path to vExcelPath.
 
S

smart.Object

Member
sdjensen said:
in xlmove.i you are setting the excelpath, but if that variable is not set or the path is wrong then it could explain why you receive the "excel is not avail".

What version if excel are you using?
My excel.exe is placed in "c:\program files\microsoft office\office11\excel.exe".

Try adding the full path to vExcelPath.
Click to expand...

Thanks so much sdjensen. You set me on the right path to finding an answer. I started pocking around and found out that in the new version of the software the program gets installed into "C:\program files..." directory where in old one it goes straight into "C:\..." and the thing it didn't like was the space between "program files" and also it was giving me that message b/c it couldn't find the excel exe file. Here is my hardcoded solution:
Code: 
        ASSIGN xltemp= 
             "C:\Encompix\Temp\"  
             + STRING((TIME),'99999999') 
             + '.xls'. 
    
        IF SEARCH(xltemp) = ? THEN LEAVE do-block.

    END.
     
    os-copy value(vExcelFiles + xlfile) value(xltemp). 
  
    xltemp = search(xltemp).
       
  ASSIGN  
    vString = vExcelPath + " " + xltemp 
    excelon = TRUE.
as you can see I also had to get rid of some of the garbage inside vString as it was pointing straight to .exe file already for some reason.
Thanks Again. :blush:
 
You must log in or register to reply here.

Similar threads

W
  • Locked
[Stackoverflow] [Progress OpenEdge ABL] Progress OpenEdge 12.2.7 Windows server 2019 Standard Microsoft Office 365 received message 3153 DDE initiate
Replies
0
Views
576
Wanda
W
A
  • Locked
[Stackoverflow] [Progress OpenEdge ABL] Send message to cloudAMQP over TLS?
Replies
0
Views
1K
AXMIM
A
P
  • Locked
[Progress Communities] [Progress OpenEdge ABL] Forum Post: RE: SELECTION-LIST using LIST-ITEM-PAIRS
Replies
0
Views
2K
Patrick Tingen
P
N
  • Locked
[Progress Communities] [Progress OpenEdge ABL] Forum Post: RE: Corticon crashes
Replies
0
Views
702
nicolav
N
V
  • Locked
[Progress Communities] [Progress OpenEdge ABL] Forum Post: RE: Call to REST API Endpoint Generates Error 9318 (Using OpenEdge.Net.pl) Code Supplied in
Replies
0
Views
1K
v205
V
Top