reagrding Excel download

[Former Member]

Hi,

I have a query reagrding Excel download. Is it possible to download an Excel from SAP GUI in background using GUI_DOWNLOAD/WS_DOWNLOAD/DOWNLOAD?.

Please let me know any alternatives for my situation.

Thanks in advance

Ravi.

[Former Member]

This is the code taken from the forum to download data to excel...

data: begin of itab occurs 0,
      vbeln like vbak-vbeln,
      posnr like vbap-posnr,
      end of itab.
select vbeln
       posnr
       from vbap
       up to 20 rows
       into table itab.

* EXCEL sheet using OLE automation.
INCLUDE OLE2INCL.
* handles for OLE objects
DATA: H_EXCEL TYPE OLE2_OBJECT,        " Excel object
      H_WORK  TYPE OLE2_OBJECT,
      H_SHEET TYPE OLE2_OBJECT,
      H_CELL  TYPE OLE2_OBJECT,
      V_COL   LIKE SY-TABIX.     " column number of the cell
DATA:
  V_STEP(30),
  V_FILE LIKE RLGRAP-FILENAME.


* tell user what is going on
  CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
     EXPORTING
*           PERCENTAGE = 0
           TEXT       = 'Creating Excel...'
       EXCEPTIONS
            OTHERS     = 1.
* start Excel
  V_STEP = 'Starting Excel'.
  CREATE OBJECT H_EXCEL 'EXCEL.APPLICATION'.
  PERFORM ERR_HDL.

  SET PROPERTY OF H_EXCEL  'Visible' = 1.

*  CALL METHOD OF H_EXCEL 'OPEN' EXPORTING  #1 = 'C:DMC_REC.XLS'.

*  PERFORM ERR_HDL.
* tell user what is going on
  CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
     EXPORTING
*           PERCENTAGE = 0
           TEXT       = 'Adding Data to Excel...'
       EXCEPTIONS
            OTHERS     = 1.
* Get the list of workbooks
  V_STEP = 'Preaparing Excel'.
  CALL METHOD OF H_EXCEL 'WORKBOOKS' = H_WORK.
  PERFORM ERR_HDL.

** Add new workbook (create a file)
  CALL METHOD OF H_WORK 'ADD'.
  PERFORM ERR_HDL.
* Get the created worksheet

************************Sheet Number

  CALL METHOD OF H_EXCEL 'WORKSHEETS' = H_SHEET EXPORTING #1 = 3.

************************Sheet Number
  PERFORM ERR_HDL.
* Activate (select) the first sheet
  CALL METHOD OF H_SHEET 'ACTIVATE'.
  PERFORM ERR_HDL.


* tell user what is going on
  CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
     EXPORTING
*           PERCENTAGE = 0
           TEXT       = 'Adding Data to Excel...'
       EXCEPTIONS
            OTHERS     = 1.
* output column headings to active Excel sheet
  V_STEP = 'Adding data to Excel'.
  LOOP AT ITAB.
    V_COL = SY-TABIX.
    PERFORM FILL_CELL USING 1 V_COL ITAB-vbeln.
    PERFORM FILL_CELL USING 2 V_COL ITAB-posnr.
  ENDLOOP.
  V_STEP = 'Releasing Excel'.
  FREE OBJECT H_EXCEL.
  PERFORM ERR_HDL.
  H_EXCEL-HANDLE = -1.

*&---------------------------------------------------------------------*
*&      Form  ERR_HDL
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*----------------------------------------------------------------------*
FORM ERR_HDL.

  IF SY-SUBRC <> 0.
    WRITE: / 'Error in processing Excel File:', V_STEP.
    STOP.
  ENDIF.

ENDFORM.                    " ERR_HDL
*&---------------------------------------------------------------------*
*&      Form  FILL_CELL
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_1      text
*      -->P_1      text
*      -->P_1      text
*----------------------------------------------------------------------*
FORM FILL_CELL USING  ROW COL VAL.
  CALL METHOD OF H_EXCEL 'Cells' = H_CELL
                 EXPORTING #1 = ROW #2 = COL.
  PERFORM ERR_HDL.
  SET PROPERTY OF H_CELL 'Value' = VAL .
  PERFORM ERR_HDL.
ENDFORM.                    " FILL_CELL

[hymavathi_oruganti]

S, gui_download IS ENOUGH

[former_member188685]

hi,

it is not possible in background.., you can down load it to application server and later you can download it to presentation server if you want.

Regards

vijay

[Former Member]

Check demo programs

GREXCEL0

GREXCEL1

[hymavathi_oruganti]

TO DOWNLOAD TO APPLICATION SERVER, USE command

<b>

TRANSFER</b>

[Former Member]

hi,

<b>reward if useful</b>

<b>GUI_* and WS_* function modules do not work in background</b>

When scheduling a job in the background the appropriate statement to read in your file is OPEN DATASET, and the file must be on the file system that the SAP server can see.

At anytime, a user can switch of the Personal Computers even though the job is still running in the background. Therefore GUI_* and WS_* function modules are not designed to work in that way, as they need to access your personal computer file.

Download in Background in Excel Format

How to download the data in excel format directly while executing in background mode?

If you will execute it in bacground with ws_download or download, it will be aoutomatically cancel. so what is the procedure to do this. How is can directly read the spool from program?

<b>Download from background is possible, if you could setup the environment</b>

1. create a custom table first

Table : Y001

Displayed fields: 4 of 4 Fixed columns:

MANDT BNAME Y_SITE Y_PATH

010 <userid> <site> cd <novell_path>

2. rewrite ws_download to z_download (light modification required, see attachment)

3. ask your basis team to make a copy of command FTP and CHMOD to ZFTP and ZCHMOD resp., make the setting according to your environment.

1 *----

---

2 * Changed By :

3 * Changed On :

4 * Changed : NOVELL directory path based on SAP login id.

5 *----

---

6 TABLES: Y001.

7

8 CONSTANTS: C_PATH(14) VALUE '/home/ftpuser/'.

9

10 DATA: BEGIN OF C_TAB,

11 X(1) TYPE X VALUE '09',

12 END OF C_TAB.

13

14 DATA: BUFFER(8000),

15 FIELDNAME_OFFSET TYPE I,

16 * FULLPATH(128),

17 FULLPATH LIKE SXPGCOLIST-PARAMETERS,

18 * CMDFULLPATH(128),

19 CMDFULLPATH LIKE SXPGCOLIST-PARAMETERS,

20 CMD(40),

21 IBTCXPM LIKE BTCXPM OCCURS 0.

22

23 FUNCTION Z_DOWNLOAD.

24 *"----

---

25 ""Local interface:

26 *" IMPORTING

27 *" VALUE(FILENAME)

28 *" VALUE(LOCATION)

29 *" TABLES

30 *" DATA_TAB

31 *" FIELDNAMES OPTIONAL

32 *"----

---

33

34 DATA: WS_LINE TYPE I.

35

36 FIELD-SYMBOLS: <F>.

37

38 CHECK NOT FILENAME IS INITIAL.

39

40 CONCATENATE C_PATH FILENAME INTO FULLPATH.

41 OPEN DATASET FULLPATH IN TEXT MODE FOR OUTPUT.

42

43 DESCRIBE TABLE FIELDNAMES LINES WS_LINE.

44 IF WS_LINE NE 0.

45 PERFORM FIELDNAMES_2_BUFFER TABLES FIELDNAMES CHANGING BUFFER.

46 FIELDNAME_OFFSET = STRLEN( BUFFER ).

47 TRANSFER BUFFER TO FULLPATH LENGTH FIELDNAME_OFFSET.

48 ENDIF.

49

50 LOOP AT DATA_TAB.

51 CLEAR BUFFER.

52 CLEAR FIELDNAME_OFFSET.

53 DO.

54 ASSIGN COMPONENT SY-INDEX OF STRUCTURE DATA_TAB TO <F>.

55 IF SY-SUBRC NE 0. EXIT. ENDIF.

56 WRITE <F> TO BUFFER+FIELDNAME_OFFSET.

57 CONDENSE BUFFER.

58 FIELDNAME_OFFSET = STRLEN( BUFFER ).

59 WRITE C_TAB TO BUFFER+FIELDNAME_OFFSET(1).

60 ADD 1 TO FIELDNAME_OFFSET.

61 ENDDO.

62 TRANSFER BUFFER TO FULLPATH LENGTH FIELDNAME_OFFSET.

63 ENDLOOP.

64

65 CLOSE DATASET FULLPATH.

66

67 CALL FUNCTION 'SXPG_COMMAND_EXECUTE'

68 EXPORTING

69 COMMANDNAME = 'ZCHMOD'

70 ADDITIONAL_PARAMETERS = FULLPATH

71 TABLES

72 EXEC_PROTOCOL = IBTCXPM

73 EXCEPTIONS

74 NO_PERMISSION = 1

75 COMMAND_NOT_FOUND = 2

76 PARAMETERS_TOO_LONG = 3

77 SECURITY_RISK = 4

78 WRONG_CHECK_CALL_INTERFACE = 5

79 PROGRAM_START_ERROR = 6

80 PROGRAM_TERMINATION_ERROR = 7

81 X_ERROR = 8

82 PARAMETER_EXPECTED = 9

83 TOO_MANY_PARAMETERS = 10

84 ILLEGAL_COMMAND = 11

85 WRONG_ASYNCHRONOUS_PARAMETERS = 12

86 CANT_ENQ_TBTCO_ENTRY = 13

87 JOBCOUNT_GENERATION_ERROR = 14

88 OTHERS = 15.

89

90 CONCATENATE C_PATH FILENAME '_cmd' INTO CMDFULLPATH.

91 OPEN DATASET CMDFULLPATH IN TEXT MODE FOR OUTPUT.

92 CASE LOCATION. "location A, B, C, D on a network

93 WHEN 'A '.

94

95 TRANSFER 'open xx.xxx.xx.xx' TO CMDFULLPATH.

96 TRANSFER 'user sapftp <pwd>' TO CMDFULLPATH.

97 WHEN 'B '.

98 TRANSFER 'open xx.xxx.xx.xx' TO CMDFULLPATH.

99 TRANSFER 'user sapftp <pwd>' TO CMDFULLPATH.

100 when 'C '.

101 TRANSFER 'open xx.xxx.xx.xx' TO CMDFULLPATH.

102 TRANSFER 'user sapftp <pwd>' TO CMDFULLPATH.

103 when 'D '.

104 TRANSFER 'open xx.xxx.xx.xx' TO CMDFULLPATH.

105 TRANSFER 'user sapftp <pwd>' TO CMDFULLPATH.

106 WHEN OTHERS.

107 ENDCASE.

108

109

110

111

112 *start>

113 CLEAR Y001.

114 SELECT SINGLE Y_PATH INTO Y001-Y_PATH

115 FROM Y001 WHERE BNAME = SY-UNAME

116 AND Y_SITE = LOCATION.

117 TRANSFER Y001-Y_PATH TO CMDFULLPATH.

118 *<end

119 CONCATENATE 'lcd' C_PATH INTO CMD SEPARATED BY SPACE.

120 TRANSFER CMD TO CMDFULLPATH.

121 CLEAR CMD.

122 CONCATENATE 'put' FILENAME INTO CMD SEPARATED BY SPACE.

123 TRANSFER CMD TO CMDFULLPATH.

124 TRANSFER 'bye' TO CMDFULLPATH.

125 CLOSE DATASET CMDFULLPATH.

126

127 CALL FUNCTION 'SXPG_COMMAND_EXECUTE'

128 EXPORTING

129 COMMANDNAME = 'ZCHMOD'

130 ADDITIONAL_PARAMETERS = CMDFULLPATH

131 TABLES

132 EXEC_PROTOCOL = IBTCXPM

133 EXCEPTIONS

134 NO_PERMISSION = 1

135 COMMAND_NOT_FOUND = 2

136 PARAMETERS_TOO_LONG = 3

137 SECURITY_RISK = 4

138 WRONG_CHECK_CALL_INTERFACE = 5

139 PROGRAM_START_ERROR = 6

140 PROGRAM_TERMINATION_ERROR = 7

141 X_ERROR = 8

142 PARAMETER_EXPECTED = 9

143 TOO_MANY_PARAMETERS = 10

144 ILLEGAL_COMMAND = 11

145 WRONG_ASYNCHRONOUS_PARAMETERS = 12

146 CANT_ENQ_TBTCO_ENTRY = 13

147 JOBCOUNT_GENERATION_ERROR = 14

148 OTHERS = 15.

149

150 CALL FUNCTION 'SXPG_COMMAND_EXECUTE'

151 EXPORTING

152 COMMANDNAME = 'ZFTP'

153 ** commandname = 'ZFTP'

154 ADDITIONAL_PARAMETERS = CMDFULLPATH

155 TABLES

156 EXEC_PROTOCOL = IBTCXPM

157 EXCEPTIONS

158 NO_PERMISSION = 1

159 COMMAND_NOT_FOUND = 2

160 PARAMETERS_TOO_LONG = 3

161 SECURITY_RISK = 4

162 WRONG_CHECK_CALL_INTERFACE = 5

163 PROGRAM_START_ERROR = 6

164 PROGRAM_TERMINATION_ERROR = 7

165 X_ERROR = 8

166 PARAMETER_EXPECTED = 9

167 TOO_MANY_PARAMETERS = 10

168 ILLEGAL_COMMAND = 11

169 WRONG_ASYNCHRONOUS_PARAMETERS = 12

170 CANT_ENQ_TBTCO_ENTRY = 13

171 JOBCOUNT_GENERATION_ERROR = 14

172 OTHERS = 15.

173

174 ENDFUNCTION.

175

176 ----

---

177 * FORM FIELDNAMES_2_BUFFER *

178 ----

---

179 * ........ *

180 ----

---

181 * --> FIELDNAMES *

182 * --> BUFFER *

183 ----

---

184 FORM FIELDNAMES_2_BUFFER TABLES FIELDNAMES CHANGING BUFFER.

185 CLEAR BUFFER.

186 CLEAR FIELDNAME_OFFSET.

187 LOOP AT FIELDNAMES.

188 WRITE FIELDNAMES TO BUFFER+FIELDNAME_OFFSET.

189 CONDENSE BUFFER.

190 FIELDNAME_OFFSET = STRLEN( BUFFER ).

191 WRITE C_TAB TO BUFFER+FIELDNAME_OFFSET(1).

192 ADD 1 TO FIELDNAME_OFFSET.

193 ENDLOOP.

194 FIELDNAME_OFFSET = FIELDNAME_OFFSET - 1.

195 IF FIELDNAME_OFFSET >= 0.

196 WRITE SPACE TO BUFFER+FIELDNAME_OFFSET(1).

197 ENDIF.

198 ENDFORM.

Message was edited by: Ashok Kumar Prithiviraj

[former_member188685]

Hi,

There are so many topics covered on this topic, check it...

Regards

vijay

[Former Member]

Hi,

WS and GUI FMs will not work in background..

instead use FM MS_EXCEL_OLE_STANDARD_DAT.

it will directly create an Excel file..

Hope this helps..

Let me know if you need anything more regarding this FM.

Regards,

Shashank

[Former Member]

Hi Shashank,

Thank You for the response.

But it is not working for me when i am running in background.

I need the excel in my local pc where i run the program.

Thank You,

Ravi.

[Former Member]

hi ravi ,

yeah u can do one thing in this situation.

first of all save the condition to a variant for ur report where u want to do the excel download.

2.now goto transaction SM36 and define ur background job.

in here give a job name this is for ur reference,

jobclass -- for setting up the priority

and target server .

3.now press start condition button from the menubar, this will giv u a subscreen , in that choose date/time and save.

specify the date/time when u want to do the background .

this is just like u r scheduling.

4.now take teh next button step job teh second one and in here give the program name and teh variant which u hav given for that particular program to execute.

here u need to specify the variant cause there can be many variants .

an save .

once saved and done this will start the processing in background .

regards,

vijay.

[Former Member]

Hi,

You cannot download Excel file when you are running a job on the background.

Instead you can do an alternate arrangement. Write the full file onto the application server while you are running the program in background.

After the job has successfully generated the file on to the applicatiom server use the T-code CG3Y, to download the file from application server onto presentation.

Reward point if post is helpful.

Thanks

Mayank

[Former Member]

Hi All,

Thank You for the responses.

But i need to download into presentation server directly, when user enters file path on selection screen.

Thanks & Regards,

Ravi.

[hymavathi_oruganti]

then u can save the path as a variant and use ws_download

and run in sa38 or sm36 in background with variant.

[Former Member]

hi ravi,

i gave u the comment on ur query in regard to the download of excel onto presentation server itself.

did u tried the option in my previous reply of this query.

like as i said to create the variant

ur variant will have the path where it is to be downloaded in to ur system(presentation server) once after running in the back ground.

like ur program declaration for download path file will be like this .

parameters: p_pcfile like rlgrap-filename default 'C:\compvij.xls'

modif

id

d1.

ur execution screen will be

like

_______________________________________________

select-options : frm-val to to-val

select-options : frm-val to to-val

select-options : frm-val to to-val

select-options : frm-val to to-val

parameters :'C:\compvij.xls'

__________________________________________________

now from selection screen when u see like this

fill the options to ur values on which u want to download the excel (presentation server )

after this is over,

and now 'save'

it asks to create a variant .

complete it and take the variant name.

from here follow the steps as i gave u in my previous mail.

regards,

vijay.

[Former Member]

Hi Vijay,

still facing a problem..........

it is raising an exception

[Former Member]

CAN U BRIEF THAT EXCEPTION PLZ ,

regards,

vijay.

[Former Member]

RAVI ,

i think we cant run this as the same problem im facing .

i ve just checked it now.

the better option would be to transfer the file to application server and then retrieve .

my apology to u .

vijay.