Copy Excel Ranges and Paste into SAP Multiple Selection

Copy Excel Ranges and Paste into SAP Multiple Selection | SAP Scripts with Excel VBA

Рет қаралды 39,725

Күн бұрын

Get instant access to the eBook "SAP GUI Scripting: Understanding the Basics" and discover how you can transform your SAP work processes, reduce errors, and boost productivity!
joelting.com/sap-ebook/
I have always been tasked to run SAP report based on certain criteria/values that was usually sent to me via email in the form of Excel.
In this video, I'll share my way of automating the process of using an Excel Range as SAP multiple selection criteria to run the report.
Timestamps
00:00 Introduction
00:26 Record Process using SAP Script Recording Tool
01:46 Developing SAP Script in Excel VBA
02:50 Test it out!
02:53 Thank you!
#SAPScripting #ExcelSAPAutomation #SAPMultipleSelection #SAPVBA
DISCLAIMER:
SAP® and SAP GUI Scripting are registered trademarks of SAP AG.
I am NOT associated with SAP AG in Germany or any other country. I am NOT SAP Partners. I do NOT offer live SAP Training. I do NOT offer SAP Access. We do not recommend the use of SAP Access from any particular provider.
MY FAVOURITE GEAR
💻 My Personal Laptop, Legion 5 Pro - amzn.to/3nZrn1I
✈️ My Travel Companion, Macbook Air M1 - amzn.to/43jLLuE
📷 My Camera Gear, Sony ZV-E10 - amzn.to/40T7uIj
🤝 BE MY FRIEND:
🐦 Twitter - / joelting92
🏢 Linkedin - / joel-ting
🙎🏻‍♂️ WHO AM I:
I'm Joel, an analyst working in Singapore. I make videos about technology, automation and productivity.
📧 GET IN TOUCH:
If you'd like to talk, I would love to hear from you. Email me directly at joelting92@gmail.com would be the quickest way to get a response. I will try my best to reply to your email as soon as possible.

Пікірлер: 68

@JoelTing 18 күн бұрын

I just launched "SAP GUI Scripting: Understanding the Basics" ebook! If you are interested to learn more, check it out in the link below: joelting.com/sap-ebook

@jesseniatavares7926 2 жыл бұрын

Joel - thank you! this was great - I was able to do exactly what I needed.

@QijiaZang Жыл бұрын

Once again, many thanks to your great work! Big fan :)

@kyletaylor6849 2 жыл бұрын

This is fantastic! Thank you so much.

@kokitormmrj2320 Жыл бұрын

Beautiful! Thank you so much ❤🎉

@gaborborsi9634 Жыл бұрын

I don't know how many times read discussions and didn't find the solution yet. HUGE thanks for it!!!!!

@user-li7mq5hp9g 2 жыл бұрын

Great tip! Thank you very much!

@prasenjitmandal5486 2 жыл бұрын

You are truly Amazing 👍

@FaysalEasyExcel 2 жыл бұрын

Wonderful for SAP user.

@Crashdownfully 2 жыл бұрын

Nice. Love it!

@douhaamri1883 Жыл бұрын

Great job 👏

@yashwinkishore4675 Жыл бұрын

Thank you!

@munshirasimraja5713 Жыл бұрын

Thank you

@Clock1129 2 жыл бұрын

Hi , it was pretty useful.Thanks👍.

@JoelTing 2 жыл бұрын

Hi Ángel Bultrón, I'm glad you find it helpful. Nice to meet you!

@DGTransformer Жыл бұрын

you are amazing man

@JoelTing Жыл бұрын

Thanks man!

@puffykarl3818 Жыл бұрын

Hi Joel! I loved your channel I learned a lot from your contents. Just a quick question, what if the sap window doesn’t have a paste button. How can I macro the paste function in SAP? I’m referring to posting of payments with multiple invoice. Thanks much and more power!

@JoelTing Жыл бұрын

Hi puffykarl3818, sorry for the late reply. you don't necessarily have to use copy and paste. Usually each box in SAP is represented by an object and you can change the object value directly without using the paste function (ie. session.findById("wnd[0]/tbar[0]/okcd").text = Range("A2").Value

@BrianLiew 2 жыл бұрын

Thanks Joel for this useful video. I am trying to understand what you meant at 2:40 as my user case is that I want to limit my table size with my filtering , taking your example, besides County, i also want to select e.g. Customer ID and City. How should I adapt the above video with my case? TIA!

@JoelTing 2 жыл бұрын

Hi Brian, If there's 2 selection or more, right after you paste in the 1st selection, repeat the copy step again from Excel, open the multiple selection window for Customer ID in SAP, then click the paste from clipboard button again. Repeat the same step for the 3rd selection and so on.

@BrianLiew 2 жыл бұрын

@@JoelTing Thanks, will test them out.

@sanjeewasamaranayake 7 ай бұрын

I learned this thanks to you. Now I run one SAP t- code (IW39), use Power query to summaries and filter say order numbers and then load that as an input to commitment report (KOB2) to get the commitments for the orders. However the list goes to more than 10k items and copy paste method in most cases fail and only part of the range get copied so the KOB2 report outcome is partial. I do not get any errors but it is just that I cannot use the output from KOB2 as that is not for the full list of orders I want. When I step through with F8 it always work. I added wait times to the macros so that next step will not be executed sooner than copy paste to SAP get completed. Done lot of tweaks and nothing work 100% of the time. Added range +50 too and that still did not work. Other thing is that I run sequence of SAP scripts via combined macro with the idea of running mutiple tasks unattended and with this almost all the times the big range copies fail (in the sense only part of the list get copied so the output is incomplelte). Is there a fix for this that you can think of

@JoelTing 7 ай бұрын

Hi there. I seldom had experience with such a large copy and paste. So you meant the main issue is the timing of the execution, and when you add wait time, it increases the odds of the process running as intended? Since it's working all the time when you are stepping through, it's unlikely to be a SAP limitation. One option that you can try is you copy and paste on batches. So, basically you copy, the first 2000 values for example, click on the upload from clipboard button, then return to copy the next 2000, then click again on the upload from clipboard button. Continue that until you complete the whole list. Let me know if that would work?

@jearanas7569 5 ай бұрын

Do you have video which do the opposite? From SAP fields to excel? Thank you.

@JoelTing 5 ай бұрын

Hi there. I have a short portion in my video on vendor creation, where I explain how you can get a field into excel. kzfaq.info/get/bejne/pd9nabxmp8fPn30.html I just show how to do it for 1 field, but the same concept applies to the rest.

@minipriyaapokala180 2 жыл бұрын

Hi Joel This Video is very useful but I want to know is there a way to give different filename everytime while exporting the data form SAP.

@JoelTing 2 жыл бұрын

Hi Mini Priyaa Pokala, Yes, it is possible. It depends on how it is setup. You can set it up to be unique such that whenever you run the code, it is going to capture the year, month, date and time at the point of execution and name the file based on that. That way each file name for every code execution will be unique. You can refer to the video below as a reference: kzfaq.info/get/bejne/f7l0i5h93Mm6gIU.html

@ketuscorpion3717 2 жыл бұрын

Hi Joel, I work in SAP VA01 PO entry. We get list of PO details which we need to enter in SAP. Is it possible to automate it where I can give the inputs from Excel file which goes into SAP VA01??

@JoelTing 2 жыл бұрын

Hi Ketu, I'm not very familiar with SAP VA01 entries. However, it's certainly possible to copy and pasting data from Excel file to SAP and use loops (if you have multiples PO to be entered). You can check out Csongor's video on some of the basics. kzfaq.info/get/bejne/pbaAm6RimbbRXWw.html I will keep that in mind and perhaps work on a video on the concept of entering multiple entries into SAP.

@vincentsoong7778 2 жыл бұрын

Can you do a video of recording a sap posting and storing/saving the document number in Excel?

@JoelTing 2 жыл бұрын

Hi Vincent, I will try if I am able to work out something with my existing SAP server because currently, I do not have access to T-codes to perform posting. I will let you know if I'm able to work that out. That being said, from what I understand, you should be able to get the document number in the status bar by referencing session.findById("wnd[0]/sbar").Text and trimming it down to contain only the text you wanted.

@abbiramirez7467 Жыл бұрын

Hi Joel! Thank you for the video!! I have a question, do you know how to do the opposite? I mean, I want to fill an excel file with information that I have in SAP tables. Does somebody now? I already have the script recorded. Thank you!

@JoelTing Жыл бұрын

Hi Abbi, it should be similar, just identify the control id of the element you need in SAP, extract the text, then set it as a cell value.

@dhananjaypinjan2643 Жыл бұрын

Very well explained.. How to handle SAP Login and Password through Macro..

@JoelTing Жыл бұрын

Hi Dhananjay, that can get a bit complicated. You can try storing your Login and Password in Azure Key Vault, then use their REST API in VBA to retrieve your login information. Personally have not try that before. Only called API with Python, but not VBA.

@dhananjaypinjan2643 Жыл бұрын

So quick.. Thanks Joel

@Graciedits Жыл бұрын

First off, I want to say thank you for the amazing videos. They have been very helpful. With that said, I have an issue. I build my excel macros to pull SAP data from a pre-built SQVI query. This works great for me, but it does not work for anyone else I would like to share the tool with because they would need to have their SQVI query build exactly like mine. The other problem is that not all users I would like to share the tool with have access to Tcode SQVI. Is there a way to build a macro to pull data directly from material master records?

@JoelTing Жыл бұрын

hey scotyy, sorry for the late reply. Unfortunately, SAP scripting is meant to work based on your user interface. If there's any difference your colleagues setup, the script will break and unable to proceed. To pull data directly from material master, you probably need to reach out to your IT. From what I understand, they can pull the data into business warehouse and everyone with the business warehouse will then be able to pull that directly without going through SAP GUI. But, still access needs to be granted first before they can access the data. SAP is setup this way for security reasons so that unintended users will not have access to the data.

@shirenegooi2135 2 жыл бұрын

Hi Joel, thanks for the sharing. would you advise how if the SAP pages is without the multiple selections to let us perform the upload from clipboard? for example like when we would like to paste a large amount for invoice line item under f-32.. Appreciate for your sharing.

@JoelTing 2 жыл бұрын

Hi Shirene, I'm not very familiar with f-32. May I know if in usual cases, how do you perform the pasting action in F-32? since upload from clipboard is not available.

@shirenegooi2135 2 жыл бұрын

@@JoelTing Hi Joel, it was like one time we can only paste 10 invoice line item, then click enter and pasted another 10 and keep on repeat until all the invoice we wanted to clear is log inside the SAP thn only can proceed for the clearing. So i am looking if this can be automated it will really save the time and the repetitive step.

@JoelTing 2 жыл бұрын

@Shirene Gooi Do you mind to share a video recording of the routine (with about 3 rounds of copy paste) and the SAP script that you recorded with me? Let me have a look and see if I can work something out. If you are comfortable sharing, you can share the files with me via my email: joelting92@gmail.com

@shirenegooi2135 2 жыл бұрын

@@JoelTing Hi Joel, sure and thank you so much😊 Just give me some days, I will share u the video soon. Really appreciate it😊

@puffykarl3818 Жыл бұрын

Any update on this? I’ve been looking for this content too, uploading multiple invoice thank you more power

@nihil_bio 2 жыл бұрын

Thanks Joel for this video. Could you kindly also let us know how to copy a value/row/column from SAP screen & paste it in excel sheet using VBA codes ? Say we have the output of the customer table as shown in this video & I want to copy either whole table or selective cells and paste it directly to the excel file as a new sheet with different sheet name on each iteration of execution instead of export as spreadsheet which would create a file on every execution . In SAP , we can record till copy or copy to clipboard but beyond that I couldn’t identify the appropriate vba codes. I’m just a rookie on macros/vba/scripting.

@JoelTing 2 жыл бұрын

Hi NiHil, yes there's a way to interact with SAP GUI table directly but it requires some complex code writing and loops for it to work. Export as spreadsheet would be simpler but it's with it's own limitation. If you are interested to learn, here's one of the video I found talking about interacting with SAP GUI Gridview. kzfaq.info/get/bejne/idZ3m92Xs5nWj6c.html I think it's quite technical, I'll see if I can work something out similar to simplify the process.

@nihil_bio 2 жыл бұрын

@@JoelTing Thanks Joel. Till now for simple copy&paste, I have added the below code after “Copy to Clipboard” from SAP to paste it in a new sheet by creating one & renaming it. I’m searching on how to rename the new sheet name conveniently (without using time stamp). Still there is a lot to learn & understand. Sheets.Add After:=ActiveSheet ActiveSheet.Select ActiveSheet.Name = "Test_" & Date$ Range("A1").Select ActiveSheet.Paste

@Foodietheexplorer 2 жыл бұрын

Hi Joel, can you kindly prepare the same using Power Automate Desktop

@JoelTing 2 жыл бұрын

Hi Swaraj Chhallani, I have uploaded similar video with Power Automate Desktop previously. You can refer to this video: kzfaq.info/get/bejne/bMl0nZtn0MXQY2g.html It is quite similar to this video but it is performing the task with Power Automate Desktop.

@sujithortan5773 2 жыл бұрын

Hi I like your content I stuck at a point so my job is to create credit notes referring the invoice could you help me teaching how can I run an activity multiple times for a list of invoices

@JoelTing 2 жыл бұрын

Hi Sujith, are you referring to multiple rows of line items in your excel and you wanted to perform the same action for every single entry? Yes, that's possible, you can do that with loop. I'm looking to do a video on something similar. Meanwhile, you can check out this video from Csongor Varga. It's quite long but it may have information that you need. kzfaq.info/get/bejne/pbaAm6RimbbRXWw.html

@kalyanamurti4624 2 жыл бұрын

Hi Joel, I am usually working with a table that has multiple columns including column for Gl account, column for cost center, column for each amount allocated to each cost center. I want to be able to copy paste the whole thing from excel to SAP. Please help me.

@JoelTing 2 жыл бұрын

Hi Kalyana, I'm not sure how it was structured in SAP. I would need to understand more before I can provide any suggestion. Usually as long as you can do the copy and paste as a user, you should be able to do it in the script, provided that scripting is enabled. Do reach out to me via email with screenshots to help me understand better. I'll see how I can help.

@gssalian1 Жыл бұрын

Hi Joel, I would be very grateful if u could answer what if I have multiple ranges to copy from excel to SAP, like co.code, documents numbers in dynamic selection, also vendor codes to extract from fbl1n

@JoelTing Жыл бұрын

Hi there, would you be able to do that step by step? (copy from Excel, paste in SAP for co. code, then do the same for document numbers, and then vendor codes). Would this approach work for you?

@gssalian1 Жыл бұрын

@@JoelTing yes Joel..... Exactly the same step....it's step by step.... Copy and paste it into SAP

@vikrantnag86 2 ай бұрын

Can we do it with these multiple ranges options also in SAP. Like you have County in column A2:A4, you have City in B column with values from B2:B10. Can this be incorporated with this method

@JoelTing 2 ай бұрын

Hi there. Yes, that's possible. Just make sure the sequence of your copy is the correct one. 1) Copy the country in range A2:A4 2) Open the multi select option in SAP for country and click on paste from clipboard button, click on "OK" to close 3) Copy the City in range B2:B10 4) Open the multi select option in SAP for City and click on paste from clipboard button, click on "OK" to close This way, the correct value should appear.

@prasenjitmandal5486 2 жыл бұрын

Can you help in Salesforce Automation through VBA code

@JoelTing 2 жыл бұрын

Hi Prasenjit Mandel, what kind of automation you are looking for when using Salesforce? I am not familiar with Salesforce, but I can try to research on it

@prasenjitmandal5486 2 жыл бұрын

@@JoelTing loading data from Excel data to Salesforce for generating a new case on Salesforce, Data extraction from Salesforce to Excel

@PratikChaudhari003 9 ай бұрын

Can you please in this ? I am facing following error : Compile error : User-defined type not defined Thank you.

@JoelTing 9 ай бұрын

Hi there. Have you enabled SAP GUI Scripting API in References?

@PratikChaudhari003 9 ай бұрын

@@JoelTing Hi, I checked the .ocx file which you have mentioned in this video. But it's not there in my excel. however there are number of other sap related .ocx files. I am unable to decide which one to choose

@JoelTing 9 ай бұрын

@@PratikChaudhari003 Hi, you should specifically look for sapfewse.ocx file. That's the library that is needed to run automation. If you just look for it in file explorer, are you able to find the file?

@PratikChaudhari003 9 ай бұрын

@@JoelTing Thanks. I have to login from my organisation's device. I will check and will let you know.