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
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
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?
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
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.
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??
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. czcams.com/video/oPPhA14Pm-8/video.html I will keep that in mind and perhaps work on a video on the concept of entering multiple entries into SAP.
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.
Hello! If you can access it through the SAP GUI, then the script should work similarly. The SAP GUI script simply mimics user interactions. As long as you have the necessary access permissions and scripting is enabled, you should be able to successfully replicate the process in the script.
@@JoelTing Thanks Joel. I tried and this is works in SE16N. What if there are multiple excel ranges and paste into multiple SAP selection? Do I need modify some coding? For example: Customer ID (A1:A5) & Country(B1:B5) in excel ranges and paste into SAP "Customer ID", "Country" multiple selection.
In VBA, you can just copy the range using Range().Copy, then use the upload to clipboard button in SAP to paste. Just do it in sequence, then this should work.
Hi there. I have a short portion in my video on vendor creation, where I explain how you can get a field into excel. czcams.com/video/oy76Y5BakoI/video.html I just show how to do it for 1 field, but the same concept applies to the rest.
Hello! Have enabled SAP GUI Scripting API in "Reference"? I made a step by step video on how to enable it that you may refer to: czcams.com/video/7Rxh10Kt5v4/video.html
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.
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!
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
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!
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
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?
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.
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.
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!
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.
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: czcams.com/video/ISDX5LwcVPQ/video.html
Hi Swaraj Chhallani, I have uploaded similar video with Power Automate Desktop previously. You can refer to this video: czcams.com/video/6cDj86k_l34/video.html It is quite similar to this video but it is performing the task with Power Automate Desktop.
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
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. czcams.com/video/oPPhA14Pm-8/video.html
@@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
@@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?
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.
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. czcams.com/video/SpGhzfN3r_s/video.html I think it's quite technical, I'll see if I can work something out similar to simplify the process.
@@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
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.
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.
@@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.
@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
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?
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.
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
![Automate Data Extraction with SAP GUI Scripting & Excel Macro VBA [english]](http://i.ytimg.com/vi/8nvCW-l2Jdc/mqdefault.jpg)
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
I don't know how many times read discussions and didn't find the solution yet. HUGE thanks for it!!!!!
You are truly Amazing 👍
Joel - thank you! this was great - I was able to do exactly what I needed.
Once again, many thanks to your great work! Big fan :)
Beautiful! Thank you so much ❤🎉
Great tip! Thank you very much!
Wonderful for SAP user.
This is fantastic! Thank you so much.
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
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?
Great job 👏
Hi , it was pretty useful.Thanks👍.
Hi Ángel Bultrón, I'm glad you find it helpful. Nice to meet you!
you are amazing man
Thanks man!
Thank you!
Nice. Love it!
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
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.
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??
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.
czcams.com/video/oPPhA14Pm-8/video.html
I will keep that in mind and perhaps work on a video on the concept of entering multiple entries into SAP.
Very well explained.. How to handle SAP Login and Password through Macro..
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.
So quick.. Thanks Joel
Hi Joel, million thanks about your sharing. Can I know this coding can be used in SE16N?
Hello! If you can access it through the SAP GUI, then the script should work similarly. The SAP GUI script simply mimics user interactions. As long as you have the necessary access permissions and scripting is enabled, you should be able to successfully replicate the process in the script.
@@JoelTing Thanks Joel. I tried and this is works in SE16N. What if there are multiple excel ranges and paste into multiple SAP selection? Do I need modify some coding? For example: Customer ID (A1:A5) & Country(B1:B5) in excel ranges and paste into SAP "Customer ID", "Country" multiple selection.
In VBA, you can just copy the range using Range().Copy, then use the upload to clipboard button in SAP to paste. Just do it in sequence, then this should work.
Do you have video which do the opposite? From SAP fields to excel? Thank you.
Hi there. I have a short portion in my video on vendor creation, where I explain how you can get a field into excel.
czcams.com/video/oy76Y5BakoI/video.html
I just show how to do it for 1 field, but the same concept applies to the rest.
I am not able to do it showing user-defined type not defined. At Public As GuiApplication. Please help.
Hello! Have enabled SAP GUI Scripting API in "Reference"?
I made a step by step video on how to enable it that you may refer to:
czcams.com/video/7Rxh10Kt5v4/video.html
Thank you
Can you do a video of recording a sap posting and storing/saving the document number in Excel?
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.
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!
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
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!
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.
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
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?
@@JoelTing yes Joel..... Exactly the same step....it's step by step.... Copy and paste it into SAP
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.
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.
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!
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.
@@JoelTing Thanks, will test them out.
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.
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:
czcams.com/video/ISDX5LwcVPQ/video.html
Hi Joel, can you kindly prepare the same using Power Automate Desktop
Hi Swaraj Chhallani,
I have uploaded similar video with Power Automate Desktop previously. You can refer to this video: czcams.com/video/6cDj86k_l34/video.html
It is quite similar to this video but it is performing the task with Power Automate Desktop.
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
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.
czcams.com/video/oPPhA14Pm-8/video.html
Can you please in this ? I am facing following error :
Compile error :
User-defined type not defined
Thank you.
Hi there. Have you enabled SAP GUI Scripting API in References?
@@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
@@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?
@@JoelTing Thanks. I have to login from my organisation's device. I will check and will let you know.
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.
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. czcams.com/video/SpGhzfN3r_s/video.html
I think it's quite technical, I'll see if I can work something out similar to simplify the process.
@@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
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.
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.
@@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.
@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
@@JoelTing Hi Joel, sure and thank you so much😊 Just give me some days, I will share u the video soon. Really appreciate it😊
Any update on this? I’ve been looking for this content too, uploading multiple invoice thank you more power
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?
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.
Can you help in Salesforce Automation through VBA code
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
@@JoelTing loading data from Excel data to Salesforce for generating a new case on Salesforce, Data extraction from Salesforce to Excel