Copy Data from Excel 2003 and paste into SAP

Intro: I have an Excel Sheet with around 20k+ rows. The data needs to be transported to SAP (Copy - Paste).

Problem: The data can be pasted only 22 rows at a time. No other option possible with my current authorization in SAP.

Possible/Expected Solution: VBA-Based

Applications: Excel 2003, SAP ECC6

Priority: Urgent


Current Working:
1. Go to Excel
2. Select 22 rows
3. Copy
4. Go to SAP
5. Paste
6. Go to Excel and select next 22 rows and repeat the above steps.

Any help would be really appreciated.



Regards,
Tils.
sample.xls
LVL 12
tilsantAsked:
Who is Participating?
 
chwong67Commented:
Since you are limited by SAP authorization for run new program, I can't really help you.
You can set window screen resolution to 'Maximum' for example 1440 x 900 pixels so that you can paste more rows, in this example 28 rows.
0
 
dax_badCommented:
0
 
Saqib Husain, SyedEngineerCommented:
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
tilsantAuthor Commented:
Thanks guys, for the response!

@Daniel - Yes, the solution need not be VBA based. I just said VBA coz i know little bit of VBA, so i can tweak if any similar solution already exists. I will try the utilities you have mentioned above tomorrow at my office (im not very familiar with SAP, so i'll ask someone else to help me out on that).


@ssaqibh - I had already seen that link (from google), but couldn't understand the code given at the bottom of that page. Could you explain? And can it be modified to paste the data and save in SAP?


Regards,
Vikrant.
0
 
Saqib Husain, SyedEngineerCommented:
I dont have SAP. If you have SAP then see if it has an option to record a macro. If yes then maybe I can guide you there.

Saqib
0
 
tilsantAuthor Commented:
Saqib, SAP has something similar to Macro (i dont know what it is called) but the end user (here, me) doesn't have the authorization to create one. And, it is a long process to ask the IT people to create a similar thing in SAP and requires lot of approvals from the top management.
That is why i was thinking for any possibility of controlling few SAP commands from VBA.
0
 
dax_badCommented:
I think your best of using some of the import tools rather than a script. Check them out first, if they're useless to you we can see if we can script our way out of it.
0
 
NorieVBA ExpertCommented:
If you don't have authorization to create scripts in SAP itself you should step carefully if you want to use VBA.

They might not like that too much, it might even breach security.

If you can use VBA then you might want to check if you can automate SAP from it in someway.

I don't know if you can with SAP but you can with plenty of other applications.
0
 
Saqib Husain, SyedEngineerCommented:
From the link I provided above it is evident that VBA can access SAP. So now we somehow have to find the command which would either post a value or paste a value in SAP for which I was hoping a macro recorder could do the trick.
0
 
tilsantAuthor Commented:
Hello all,

<<They might not like that too much, it might even breach security.>>
That's what i was told by few of my colleagues here, followed by the suggestion of dropping the plan!


<<If you can use VBA then you might want to check if you can automate SAP from it in someway.>>
Automate SAP.... This is the precise thing that i want to do but couldn't find good literature of examples on the net.


And Saqib, in the link you have provided, (i'm just guessing) the code at the bottom checks if SAPLogon is installed or not and may be opens the SAP GUI if its installed. I couldn't find any more such examples on google where SAP is involved with VBA.

One more thing i found in VB Editor is, when we go to Tools >> References, there are lots of SAP related references available which hints me that there should be an option to control SAP from VBA. But the question is HOW?



Tils.
0
 
Saqib Husain, SyedEngineerCommented:
Here is a link to a thread on a forum and it contains an example which posts excel data to SAP. This forum has more threads which refer to similar discussions. You can go through them and then try them on SAP and see if they suit your needs.

http://forums.sdn.sap.com/thread.jspa?messageID=9374975#9374975
0
 
chwong67Commented:
What transaction code are you using? May be you can consider using simple ABAP program to upload to SAP.
0
 
tilsantAuthor Commented:
Hello all.

Sorry for leaving the question idle, had a long 3-day weekend :)


@ssaqibh: The link that you gave me, contains some SAP Script. I could understand only few lines! I'm a novice in SAP and don't have a programming background, so have no clue on how to use it :(


@chwong67: I'm using VK11.



Tils.
0
 
chwong67Commented:
If you have ABAP programmer, it is easier to upload data from excel in prn format.
Please refer to the link below for more information:
https://forums.sme.sap.com/message.jspa?messageID=3031758
0
 
gnurlCommented:
Hi,

are you sure that the file contains the data you need?

There is a report (Transaktion SA38 -> Report RV14BTCI) for mass input ...

Please check the [i] (Info Button for this report ...

Best regards
gnurl
0
 
tilsantAuthor Commented:
Hi all,

@Chowng67: I dont have ABAP Programmer in my office :(

@gnurl: I'm on a Business Trip now, so won't have access to SAP. I'll get back to you on that, this coming monday.


Thanks,
Tils.
0
 
Murhaf_khaledCommented:
Hi,
I think that the following link will help you solve your problem:

http://help.sap.com/saphelp_46c/helpdata/en/70/93a417ecf411d296400000e82debf7/content.htm
0
 
tilsantAuthor Commented:
Hello all,

@gnurl: I just checked, I'm not authorized to use the Transaction SA38. Any other option possible?

@Murhaf Khaled: I'm not sure if that helps! Could you explain further?


Thanks,
Tils.
0
 
gnurlCommented:
Hi Tils,

could you explain the problem to your permissons guy (enter 20000 lines of data to sap is worth to change the permission even if temporarely)? Maybe you could get the permission ... an other way would be transaction SE38 but this permission should have been used more restrictively than SA38!

Good luck
gnurl
0
 
tilsantAuthor Commented:
Hi gnurl,

I was just tryin to play with the Recorder option of SAP and it again said im not authorised to do so.... :(
As of now, since i had no other option, i had to appoint a group of people to upload the data, which took them around 4.5 hours. But after a few days, we might have to do a similar exercise. So, we are going to go for either having enough authorisations or something called as "BDC" which allows mass data entry.
0
 
chwong67Commented:
Could you please show me your input screen dump?
May be I can help you with simple ABAP Batch upload program...
0
 
tilsantAuthor Commented:
@chwong67: This explains my current procedure.
Excel-to-SAP.xls
0
 
chwong67Commented:
I need the following informations from input screen:
1. System->Status: write down 'Transaction' information.
2. Place the cursor on respective field, Press F1, click 'Technical Info'  and write down 'screen field' information.
0
 
tilsantAuthor Commented:
Here is the "system status" screen.
Tell me for which all fields do you need the "technical info"?


Thanks
Tils.
0
 
tilsantAuthor Commented:
Forgot to attach the file.
Here it is!
System-Status.xls
0
 
chwong67Commented:
Transaction ZJOB is customized Transaction Code.
The steps to follow to get the field information:
1. Place the cursor on respective field, i.e. Group Code,
2. Press F1, and then click 'Technical Info'
3.Write down 'screen field' information or Print Screen.
4. Repeat Step 1-3 for Job Code, Description, ManPower.
I believe this table is customized table...
0
 
tilsantAuthor Commented:
Yes, ZJOB is a customized T-code and the table is also a customized one.

Below are the screen field details for Group Code, Job Code, Material Description, Man hours (in the same order):
ZSD_JOBCODE_MAST-GROUP_CODE
ZSD_JOBCODE_MAST-JOB_CODE
ZSD_JOBCODE_MAST-JOB_DESC
ZSD_JOBCODE_MAST-MAN_HOURS



Tils.
0
 
gnurlCommented:
Hi Tils,

default for get the missing authorisations is to
a) have enough permissions (SAP_ALL) to execute the actions neccesary for the job and trace them with transaction st01 to create a special role for this action.
    This should know your permission guy.
b) try to exceute the action you want to do and if there is a permission error... call SU53 to get the error ... the permission guy has to add this to a role ...
0
 
chwong67Commented:
Since the table is customized table, you can write directly to the table ZSD_JOBCODE_MAST...
Before that, the program need to check valid Group Code, Job Code?
Please provide also table structure:
SE11->Database name: ZSD_JOBCODE_MAST->Click Display->print screen
0
 
chwong67Commented:
Steps to create ABAP program ZSD_JOBCODE_MAST at your Development Server:
SE38->Program: ZSD_JOBCODE_MAST->Create->Type: Executable program->Local Object->Save->Paste the attached code->Activate
->Test
->Enter: Upload file name with full path(as per attachment sample.txt), Check on 'Test' Mode->Execute->Format OK
->Enter: Upload file name with full path, Check off 'Test' Mode->Execute->Save to Table->Format OK
You can also refer to your developer as well(the person who created table ZSD_JOBCODE_MAST)
REPORT zsd_jobcode_mast .

TABLES: zsd_jobcode_mast.

DATA: BEGIN OF it_infile OCCURS 0,
       group_code(04),
       job_code(08),
       man_hours(10),
       job_desc(50),
      END OF it_infile.
DATA: t_zsd_jobcode_mast LIKE zsd_jobcode_mast
        OCCURS 0 WITH HEADER LINE.

PARAMETERS: p_txtfl LIKE rlgrap-filename.
PARAMETERS: cb_test AS CHECKBOX DEFAULT 'X'."Test Run
*PARAMETERS: cb_clear AS CHECKBOX."Clear Master record

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_txtfl.
  PERFORM fetch_filename USING 'Get Text File Name'
                         CHANGING p_txtfl.

START-OF-SELECTION.
  PERFORM open_file TABLES it_infile
                    USING  p_txtfl.
  PERFORM read_data.
  IF cb_test EQ space.
    PERFORM save_data.
  ENDIF.
  PERFORM write_data.
*&---------------------------------------------------------------------*
*&      Form  fetch_filename
*&---------------------------------------------------------------------*
FORM fetch_filename USING    p_text
                    CHANGING p_txtfl.
* get source filename
  CALL FUNCTION 'WS_FILENAME_GET'
       EXPORTING
            def_filename     = ' '
            def_path         = 'c:\'
            mask             = ',*.*,*.*.'
            mode             = 'O'
            title            = p_text
       IMPORTING
            filename         = p_txtfl
       EXCEPTIONS
            inv_winsys       = 01
            no_batch         = 02
            selection_cancel = 03
            selection_error  = 04.
ENDFORM.                    " fetch_filename

*---------------------------------------------------------------------*
*       FORM open_file                                                *
*---------------------------------------------------------------------*
FORM open_file TABLES lit_recrd
               USING  lp_file LIKE rlgrap-filename.

  CALL FUNCTION 'WS_UPLOAD'
       EXPORTING
            filename                = lp_file
            filetype                = 'ASC'
       TABLES
            data_tab                = lit_recrd
       EXCEPTIONS
            conversion_error        = 1
            file_open_error         = 2
            file_read_error         = 3
            invalid_table_width     = 4
            invalid_type            = 5
            no_batch                = 6
            unknown_error           = 7
            gui_refuse_filetransfer = 8
            OTHERS                  = 9.
  IF sy-subrc NE 0.
    MESSAGE e511(vk) WITH lp_file.
  ENDIF.

ENDFORM.                    " read_file
*&---------------------------------------------------------------------*
*&      Form  read_data
*&---------------------------------------------------------------------*
FORM read_data.
  LOOP AT it_infile WHERE group_code NE space.
    MOVE-CORRESPONDING it_infile TO t_zsd_jobcode_mast.
    APPEND t_zsd_jobcode_mast.
  ENDLOOP.
ENDFORM.                    " read_data
*&---------------------------------------------------------------------*
*&      Form  save_data
*&---------------------------------------------------------------------*
FORM save_data.
*  IF cb_clear NE space.
*    DELETE FROM zsd_jobcode_mast WHERE group_code NE space.
*  ENDIF.
  MODIFY zsd_jobcode_mast FROM TABLE t_zsd_jobcode_mast.
ENDFORM.                    " save_data
*&---------------------------------------------------------------------*
*&      Form  write_data
*&---------------------------------------------------------------------*
FORM write_data.
  LOOP AT t_zsd_jobcode_mast.
    WRITE: / t_zsd_jobcode_mast-group_code,
             t_zsd_jobcode_mast-job_code,
             t_zsd_jobcode_mast-job_desc,
             t_zsd_jobcode_mast-man_hours.
  ENDLOOP.

ENDFORM.                    " write_data

Open in new window

sample.txt
0
 
tilsantAuthor Commented:
Hi gnurl & Chwong67,

Thanks a lot for that code Chwong67. BUT, I do not have all required authorizations!! :(
whenever I try doing something in SAP, it stops me from doing so.

On further discussion with my colleagues, I found out that in my organization, they have very strict policies about SAP accounts/authorizations. Each account to be created requires lot of permissions and for the existing accounts, adding more transactions require further approvals.


Tils.
0
 
tilsantAuthor Commented:
Hi Chowng67,


Changing the screen resolution, this seems to be the only way out for now. It will reduce my data entry time by more than 20%!


Thanks,
Tils.
0
 
chwong67Commented:
It's my pleasure to help you... If you do not have any other issue, please close this message. Thanks...
0
 
tilsantAuthor Commented:
will do it soon!
0
 
tilsantAuthor Commented:
Thank you all for your efforts and solutions.
Really appreciate.


Regards,
Tils.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.