Jump to content


Photo
- - - - -

HOW TO EXPORT DATA FROM TABLE TO EXCEL FILE?


3 replies to this topic

#1 fsbtglovia

fsbtglovia

    Newbie

  • Members
  • Pip
  • 1 posts
  • Gender:Female
  • Location:THAILAND

Posted 15 May 2007 - 10:10 AM

My customer want to export data to excel file and they want to Separate Field by Column and Row. How can i do? and I test send some data to Excel by:

#STAT = SYSTEM('GUI /C START EXCEL')
#STAT = DDE_POKE('EXCEL','Sheet1','R1C1', 'HELLO1','R1C2', 'HELLO2')

At frist time it's OK. Can send data to excel but second time i want to test again then show error message "010-FUNCTION EXCEEDS WORKSPACE" and no have data in Excel file.

* help me please. :unsure:

#2 Joseph Bove

Joseph Bove

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 756 posts
  • Gender:Male
  • Location:Ramsey, United States

Posted 15 May 2007 - 01:12 PM

fsbtglovia,

I've typically seen this issue when a function is involved in an infinite LSCALL loop.

It's possible that the error has nothing to do with the export to Excel

hth,

Joseph

#3 andykay

andykay

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 204 posts
  • Gender:Male
  • Location:Cyberspace...looking for work

Posted 19 May 2007 - 01:33 AM

#STAT = SYSTEM('GUI /C START EXCEL')
#STAT = DDE_POKE('EXCEL','Sheet1','R1C1', 'HELLO1','R1C2', 'HELLO2')



The good news fsbtglovia, is that there is nothing wrong with your POKE statement. I ran it here and it worked fine.

Are you saying that this is just a test function and these are the only 2 lines of code in the entire function, or did you add these lines to an existing report and are only stating these two lines in your posting because these are the lines that have to do with the excel functionality?

If it's the latter, could you post a documentation of your function?

If it's not, and these are the only 2 lines of code...All I can say is that your code, as is, works here.


AK
THE LIGHT AT THE END OF THE TUNNEL IS THE HEADLAMP OF THE TRAIN THAT'S ABOUT TO HIT YOU!!!

#4 Ahmed36

Ahmed36

    Newbie

  • Members
  • Pip
  • 3 posts
  • Gender:Male

Posted 01 June 2007 - 11:16 AM

Hi All,

You need to create 3 global logics

1. to start xl 'startxl' type P

*****************syntax***********************
EXTERN($$SYSITEMS,$SHEET,$BOOK)
#X = DDE_EXECUTE('EXCEL','','[New(1)]')
#X = DDE_REQUEST('EXCEL','SYSTEM','SELECTION',$$SYSITEMS)
IF $$SYSITEMS = '' THEN
#X = SYSTEM-CRT('GUI WINDOWS ASYNC ' +
'C:\Program Files\Microsoft Office\Office\EXCEL.EXE')
$$SYSITEMS = '[Book1]Sheet1!R1C1'
ENDIF
$SHEET = $$SYSITEMS(1,INDEX($$SYSITEMS,'!') - 1)
$BOOK = $$SYSITEMS(1,INDEX($$SYSITEMS,']'))
UMSG('',2)

2. to create the headings in xl , 'headxl' type P

*****************syntax***********************
EXTERN(#END,$TITLE(),$SHEET,$CELL,#ROW)
FOR #X = 1 TO #END
$CELL = 'R1C' + CONV(#X)
#STATUS = DDE_POKE('EXCEL',$SHEET,$CELL,$TITLE(#X))
ENDFOR
#ROW = 2

3. to populate the xl sheet , popxl , type p

*****************syntax***********************
EXTERN(#END,$VARIABLE(),$SHEET,$CELL,#ROW)
FOR #X = 1 TO #END
$CELL = 'R' + CONV(#ROW) + 'C' + CONV(#X)
#STATUS = DDE_POKE('EXCEL',$SHEET,$CELL,$VARIABLE(#X))
ENDFOR
#ROW += 1

NOW YOU HAVE CR5EATED THESE GLOBAL LOGIC, HOW DO WE USE THEM

1. Define in logic 0

$TITLE(x,y)
$VARIABLE(x,y)

where x is the nomber of fields, and Y the length.

2. a. in the default logic of an LS

*start xl sheet (call first GL) -

startxl()

* define the headings as,

$TITLE(1) =
$TITLE(2) =
.
.
.
$TITLE(x) =
#END = x

* Then call the 2nd GL, to populate the xl

headxl()

b. within the LS

read a dummy file (last file read within the LS), place a logic in the BW as,

$VARIABLE(1) = fieldname
$VARIABLE(2) = fieldname
.
.
.
.
$VARIABLE(x) = fieldname
to avoid lossing the leading zeros on a field, use

$VARIABLE(x) = '=T("' + fieldname+ '")'

* write detail line to excel spreadsheet

popxl()


I hope the above assist you in your work.



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users