Solved

Copy Data from Excel 2003 and paste into SAP

Posted on 2010-09-07
35
4,039 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:tilsant
  • 15
  • 9
  • 4
  • +4
35 Comments
 
LVL 6

Assisted Solution

by:dax_bad
dax_bad earned 50 total points
ID: 33616796
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 33616840
0
 
LVL 12

Author Comment

by:tilsant
ID: 33617723
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 33620855
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
 
LVL 12

Author Comment

by:tilsant
ID: 33623970
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
 
LVL 6

Expert Comment

by:dax_bad
ID: 33624326
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
 
LVL 33

Expert Comment

by:Norie
ID: 33629677
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 33632504
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
 
LVL 12

Author Comment

by:tilsant
ID: 33633548
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
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 50 total points
ID: 33633706
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
 
LVL 9

Expert Comment

by:chwong67
ID: 33659505
What transaction code are you using? May be you can consider using simple ABAP program to upload to SAP.
0
 
LVL 12

Author Comment

by:tilsant
ID: 33659771
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
 
LVL 9

Expert Comment

by:chwong67
ID: 33660246
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
 
LVL 7

Expert Comment

by:gnurl
ID: 33689217
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
 
LVL 12

Author Comment

by:tilsant
ID: 33689280
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
 
LVL 1

Expert Comment

by:Murhaf_khaled
ID: 33713453
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
 
LVL 12

Author Comment

by:tilsant
ID: 33713993
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 7

Expert Comment

by:gnurl
ID: 33715407
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
 
LVL 12

Author Comment

by:tilsant
ID: 33715450
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
 
LVL 9

Expert Comment

by:chwong67
ID: 33721396
Could you please show me your input screen dump?
May be I can help you with simple ABAP Batch upload program...
0
 
LVL 12

Author Comment

by:tilsant
ID: 33722274
@chwong67: This explains my current procedure.
Excel-to-SAP.xls
0
 
LVL 9

Expert Comment

by:chwong67
ID: 33722593
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
 
LVL 12

Author Comment

by:tilsant
ID: 33722920
Here is the "system status" screen.
Tell me for which all fields do you need the "technical info"?


Thanks
Tils.
0
 
LVL 12

Author Comment

by:tilsant
ID: 33722925
Forgot to attach the file.
Here it is!
System-Status.xls
0
 
LVL 9

Expert Comment

by:chwong67
ID: 33723075
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
 
LVL 12

Author Comment

by:tilsant
ID: 33723420
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
 
LVL 7

Assisted Solution

by:gnurl
gnurl earned 150 total points
ID: 33724796
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
 
LVL 9

Expert Comment

by:chwong67
ID: 33730484
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
 
LVL 9

Assisted Solution

by:chwong67
chwong67 earned 250 total points
ID: 33730674
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
 
LVL 12

Author Comment

by:tilsant
ID: 33751244
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
 
LVL 9

Accepted Solution

by:
chwong67 earned 250 total points
ID: 33758833
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
 
LVL 12

Author Comment

by:tilsant
ID: 33786962
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
 
LVL 9

Expert Comment

by:chwong67
ID: 33787183
It's my pleasure to help you... If you do not have any other issue, please close this message. Thanks...
0
 
LVL 12

Author Comment

by:tilsant
ID: 33787372
will do it soon!
0
 
LVL 12

Author Closing Comment

by:tilsant
ID: 33829576
Thank you all for your efforts and solutions.
Really appreciate.


Regards,
Tils.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now