topic Re: excel problem in Application Development and Automation Discussions

excel problem

Former Member — Tue, 24 Nov 2009 16:39:02 GMT

hi all.

first problem :

in the selection screen ,the user wants to choose the path (write it down as parameter field) that the excel is going to be saved.

second prob: i want the cells of hte file to be autofitted,

here is a part of code:

Moderator message - Please respect the 2,500 character maximum when posting. Post only the relevant portions of code. And please - one question per thread.

Edited by: Rob Burbank on Nov 24, 2009 11:51 AM

Re: excel problem

former_member191735 — Tue, 24 Nov 2009 16:51:20 GMT

There you go.... One more like this

First learn how to post code in SDN then ask a question or post question.

I am sure, you wont get any answers for this as it is not readable.

My question to you..... Can you read your own post?

This kind of post kills the interest of people who are ready to answer yout question

Re: excel problem

Former Member — Tue, 24 Nov 2009 17:03:51 GMT

ok you are right.

parameters: pa_file like rlgrap-filename
 default 'C:\name.xls'.

DATA: w_lines TYPE p DECIMALS 0.
  DESCRIBE TABLE itab LINES w_lines.
  IF w_lines > 0.
    CREATE OBJECT application 'excel.application'.
    SET PROPERTY OF application 'visible' = 1.
    CALL METHOD OF application 'Workbooks' = workbook.
CALL METHOD OF workbook 'Add'.

    CALL METHOD OF application 'Worksheets' = sheet
      EXPORTING
        #1 = 1.
        SET PROPERTY OF sheet 'Name' = 'Damages'.
    CALL METHOD OF sheet 'Activate'.

 call method of application 'RANGE' = gs_range
  exporting #1 = 'A1:Z1'.
  set property of gs_range 'HORIZONTALALIGNMENT' = 3.

  call method of gs_range 'FONT' = gs_font.
  set property of gs_font 'BOLD' = 1.
*-> Find 1st empty cell of column 1
  data w_content(10) type c.
***
  w_lin = 2. 
  do 10000 times.
    CALL METHOD OF application 'Cells' = cells
      EXPORTING
        #1 = w_lin      " line
        #2 = 1.          " column
    clear w_content.
    GET PROPERTY OF cells 'Value' = w_content .
    if w_content = ' '.
      exit.
    else.
      w_lin = w_lin + 1.
    endif.
  enddo.
    CLEAR: w_index.
    w_lin = w_lin - 1.

    LOOP AT itab.


      CALL METHOD OF application 'Cells' = cells
        EXPORTING
          #1 = w_index      " line
          #2 = 1.           " column
      SET PROPERTY OF cells 'Value' = itab-vertran .


 CALL METHOD OF APPLICATION 'Save'
    EXPORTING
*    #1 = p_file

        #1 =  pa_file.
*        #2 = pa_file."filename
    "fileFormat

*  Closes excel window, data is lost if not saved
  SET PROPERTY OF application 'visible' = 1.

Re: excel problem

former_member189059 — Wed, 25 Nov 2009 03:46:09 GMT

Hello,

This is some code to save the file


          get property of application 'ActiveSheet' = sheet.
          call method of sheet 'SaveAs'
            exporting
              #1 = pa_file        "filename
              #2 = 1.                     "fileFormat

Re: excel problem

former_member189059 — Wed, 25 Nov 2009 03:48:38 GMT

Hello,

This is some code for autofit

Note that LV_AUTOFIT contains the column names

eg: B:D will apply the autofit on columns B, C and D



* autofit - applying autofit
    call method of excel 'Columns' = h_columns
      exporting
      #1 = lv_autofit.

    get property of h_columns 'EntireColumn' = h_entirecol.
    set property of h_entirecol 'Autofit' = 1.

Re: excel problem

Former Member — Wed, 25 Nov 2009 08:42:28 GMT

thank you . now the file is saved. but i cannot fix the autofit problem.

Re: excel problem

former_member189059 — Wed, 25 Nov 2009 08:50:11 GMT

Hello,

The word 'excel' in the above code refers to the excel.application object

I guess the variable used in your case is 'application'

So change that accordingly

Re: excel problem

Former Member — Wed, 25 Nov 2009 09:05:44 GMT

i had changed it.

call method of application 'Columns' = h_columns
      exporting
      #1 = lv_autofit.

    get property of h_columns 'EntireColumn' = h_entirecol.
    set property of h_entirecol 'Autofit' = 1.

i dont understand the lv_autofit. where and how i declare the columns?

i put this code above the "save" code.

thank you again.

ok i tried this and is ok

code}call method of application 'Columns' = h_columns

exporting

#1 = 'A:Z'.

get property of h_columns 'EntireColumn' = h_entirecol.

set property of h_entirecol 'Autofit' = 1.

Edited by: antonis nezos on Nov 25, 2009 10:14 AM