Export multiple SAP reports with For Loop in Excel VBA

Export multiple SAP reports with For Loop in Excel VBA - SAP GUI Scripting

Рет қаралды 31,626

Күн бұрын

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/
In this video, I will share how you can export multiple SAP reports with different parameters using for loop in Excel VBA.
Timestamps
00:00 Overview
00:21 Script Recording in SAP GUI
01:27 Develop your script in Excel VBA
02:22 Basic structure of a for loop
04:30 Finishing touches
#SAPScripting #ExcelSAPAutomation #SAPExcelIntegration #SAPVBA
Source File:
docs.google.com/spreadsheets/...
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.

Пікірлер: 148

@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

@dcpowered Жыл бұрын

Amazing tutorial! This is a breakthrough! Thank you so much and please keep sharing your new and better methods to extract SAP data!

@JoelTing Жыл бұрын

Thanks for the kind word. I'll try my best to share more.

@Rawyu 2 жыл бұрын

Thanks Joel. Keep this coming :)

@JoelTing 2 жыл бұрын

Thanks for your kind words Abhishek!

@luisramirez2123 7 ай бұрын

Thanks a lot for providing this resources to help facilitate our daily task at work!

@tomaszkocjan8377 Жыл бұрын

Gr8 stuff. It actually helped me to do the opposite with Loop (For i = ... Next i) and import data from Excel to SAP in MAT1. Thank you

@JoelTing Жыл бұрын

Glad that it helped in some ways 😊

@minhngocnguyen5029 Жыл бұрын

Very nice stuff, man!

@behanassim4392 2 жыл бұрын

Mec je t'aime, t'es un génie

@latestupdatedeepsingh8771 2 жыл бұрын

Really you have given me a very lovely tricks and I am using in my daily work .100/100 I love you Joel You saved my lot's of time and please upload more video's Your videos more useful.

@JoelTing 2 жыл бұрын

Thanks for your kind word. I'm glad you find my videos useful. I'll work on more videos relating to tips to save time using different tools as an analyst. Thanks again for your support!

@KafkaTamura1 Жыл бұрын

thanks for video! useful!

@ecashmarketing Жыл бұрын

this is very good stuff, thanks for sharing.

@robguzman9087 2 жыл бұрын

Thank You, Joel!

@JoelTing 2 жыл бұрын

Hi Rob, hope it helps. Thanks for watching!

@davidtrejo2616 2 жыл бұрын

Muchas gracias Joel!, muy muy util para mi

@JoelTing 2 жыл бұрын

Thanks for watching!

@kyles7472 Жыл бұрын

Thank you So much!

@vikasgaikwad3832 11 ай бұрын

Very informative

@latestupdatedeepsingh8771 2 жыл бұрын

I Love your video

@latestupdatedeepsingh8771 2 жыл бұрын

I am recommending your videos to others .. really ,,I am herlty sying to you you are the best Please give more video's to us ,,,, I am working on SAP It's my firstexpirince

@JoelTing 2 жыл бұрын

Thanks for your support! Appreciate it a bunch! I will work on more videos during my free time.

@zhang4401 2 жыл бұрын

thank you so much Joel

@JoelTing 2 жыл бұрын

You're welcome. Hope it helps!

@vijaybharadwaj3816 2 жыл бұрын

Thanks Bro. It was very helpful

@JoelTing 2 жыл бұрын

Glad it helped!

@carljoeylosala1711 2 жыл бұрын

Nice joel. Thanks.

@JoelTing 2 жыл бұрын

Thank you too!

@kokitormmrj2320 Жыл бұрын

Genius 🎉. New subscriber.

@JoelTing Жыл бұрын

Thanks for subbing!

@saojoaomeriti 11 ай бұрын

Great Vídeo Joe! Please, prepare a video using LB10 transaction to stract report from it.

@JoelTing 11 ай бұрын

Hi there, I'm not too familiar about the uses of LB10 operationally. Perhaps you can try recording and see if it works with similar method and if you face issues, feel free to send me an email. I'll see how I can help.

@harendrasinhrajput1338 2 жыл бұрын

Superb information

@JoelTing 2 жыл бұрын

Hi Harendrasinh, appreciate the kind words 😀 I hope you find it useful.

@harendrasinhrajput1338 2 жыл бұрын

@@JoelTing Yes it's usefull 😊

@Piier7 Жыл бұрын

Hi Joel. Amazing videos, you helped me a lot. Is possible to have a tutorial for saving pdf files from an adobe crobat window open in sap?

@JoelTing Жыл бұрын

Hi Pier, I have not use the script to save pdf files from adobe acrobat opened. Do you mind telling me what is the T-code that you usually use for this and how you open the PDF in SAP step by step? I'll try running it in my test system to see if I can figure something out.

@bunbunai Жыл бұрын

Hi Joel, great stuff! I am wondering about saving pdf files from SAP though. I've tried your code up till a save window pops up, but have no idea how to automate inputing the file names from a sheet column and then selecting the save button. Could you suggest me on this?

@JoelTing Жыл бұрын

Hello! I saw this being asked in SAP forum. One of the suggested solution is to create a shell object and use a loop to look for the name of the Window that you are trying to activate. Could be "Save As" or something else. Once it is found, activate that shell object. Then, depending use the tab key to reach the file input field and the enter key to perform the manual work. Something like the following: Set Wshell = CreateObject("WScript.Shell") Do WindowFound = Wshell.AppActivate("Save As") wscript.sleep 1000 Loop Until WindowFound if (WindowFound) Then Wshell.appActivate "Save As" Wshell.SendKeys "{TAB}" WScript.Sleep 100 Wshell.SendKeys "{TAB}" WScript.Sleep 100 Wshell.SendKeys "{TAB}" WScript.Sleep 100 Wshell.SendKeys "{TAB}" WScript.Sleep 100 Wshell.SendKeys "{TAB}" WScript.Sleep 100 Wshell.SendKeys FileName WScript.Sleep 100 Wshell.SendKeys "{ENTER}" End if *Credit to Michal Udvardy for the suggested solution in SAP forum.

@ibrahimmib6423 Жыл бұрын

Hi Jo , You are rocking.... learned lot awesome. How to loop particular Sap steps in vba? Would be good to know that please

@JoelTing Жыл бұрын

Hi Ibrahim, usually what I will do is I will isolate the chunks of codes (like a new paragraph) that I need the loops to be performed. Once those are isolated, I will indent them and put a loop around it (could be For..Next, Do..While loop etc).

@hamadasalah7369 Жыл бұрын

Hello Joel, Thank you for your informative video. Do you have any video "How to import data into SAP?". I have time records in Excel sheet, and I want to import it to SAP instead of inserting it one by one using "CAT2".

@JoelTing Жыл бұрын

Hi Hamada, would you have the correspondent reference as well (which document number relate to which time record) in the same Excel sheet as well? Not too familiar with Tcode CAT2. In terms of importing data, not sure if you are referring to using script to loop through Excel and pasting in? The workflow should be similar. 1) Identify the range of cells you want to put in Excel 2) Get the cell value 3) Paste into SAP by assigning the value into the SAP control ID

@seandavis4904 11 ай бұрын

Hi Joel, thanks for the videos, as a complete beginner this has taught me a lot already. I have one question though, how to close all instances of Excel, it only seems to close one instance (The original one with the three rows of countries in your case). Since I have created 3 reports like yourself, I have all three created ones left open. Even with the code at the end

@JoelTing 10 ай бұрын

Hey there. I'm not too sure about this issue where not all instances of Excel is closed using the Application.Quit function. You could try looping through all your exported file path and then close and quit them accordingly. I'm just showing below how this could work for 2 workbooks. Loop through all the exported file path and substitute them in the GetObject function. Dim export1 As Object Dim export2 As Object Set export1 = GetObject(“file location\Export 1.file extension”).Application Set export2 = GetObject(“file location\Export 2.file extension”).Application export1.Workbooks(1).Close export2.Workbooks(1).Close export2.Quit

@vijayj3296 Жыл бұрын

Thank you this worked👌.... how can this be done for multiple report from different t-code

@JoelTing Жыл бұрын

You can string the different reports in a single process, run 1 report after another. You should be able to run it without issue.

@kpk9311 Жыл бұрын

You Got a new subscriber......👍👍 You are an inspiration to people who are lazy on repeated report extraction all the time..😜 and excited to improve productivity and save time🦾🦾

@yanhirata9969 Жыл бұрын

Thank you, Joel! Is it possible to save all results in 1 file, instead of the separate paths?

@JoelTing Жыл бұрын

Hi Yan Hirata, usually if I were to do that, I'll still save it in a separate path, then use workbook.open method to work with all the exports and copy the data into 1 single workbook. Then, you can delete those separate files if you want after the process is complete.

@MrsKee-qu5gc 6 ай бұрын

Hi Joel, thanks for this video. Do you have a code to export data from SAP to same VBA file but different tabs? Would really appreciate your comment

@JoelTing 6 ай бұрын

Hi there, I believe that you can do that. At the end of the script, you can continue to read the exported data workbook, then copy all the data and paste it into the VBA file that you are in. The typical workflow would be export from SAP -> read the exported spreadsheet or data -> copy all the data -> create a new worksheet in your VBA file -> paste in all the data.

@MrAyan_7861editz Жыл бұрын

Hi Joel, I want to create the loop for maintaining mass data for some deliveries in VL06. When I used your code, it gave me an error that x1UP is not defined. What should I do in this case?

@JoelTing Жыл бұрын

Hey Mohammad Ayan, should that be xlUp instead of x1UP?

@calinranger1291 11 ай бұрын

@@JoelTing you're amazing!!!

@changmalichang 2 жыл бұрын

Hi Joel , How would you handle an exception management in this case , in the event say parameter define has typo in the country field. Thanks

@JoelTing 2 жыл бұрын

Hi Jeremy, It will depend on what signs or text would be shown upon encountering the error made. Usually, the way I go about it is to read the message appearing in the status bar or the message type shown in the status bar of SAP GUI. This can be done with the following lines: session.findById("wnd[0]/sbar").text session.findById("wnd[0]/sbar").MessageType

@changmalichang 2 жыл бұрын

@@JoelTing Thank you for taking time to respond !

@maheshchagi6349 Жыл бұрын

Hello, I was trying to loop the data but getting an error message (followed your steps still count not get loop)

@ajendrajeshthi5138 Жыл бұрын

Hello Joel thanks for sharing automate video of sap .. I am stucked in where i want to run two transaction in sap mb51 and Mb52 on a button click in excel and it saved in desired location ..can you please help me out

@JoelTing Жыл бұрын

Hello. What problem are you facing? You should be able to do this by just combining the 2 scripts together.

@Hellcryer94 Жыл бұрын

Hi Joel, do you know how can run this script on second window/tab of SAP. When I run all the excel vba code to SAP, it will always use the first window of SAP, but i want the script to run on other gui window.

@JoelTing Жыл бұрын

Hi Hellcryer94, in the line where you declare the session variable, you can try changing the number to run on a different session: Set Session = objConn.Children(0) -> Set Session = objConn.Children(1)

@castone3085 8 ай бұрын

Hi Joel do you have tutorial to automate SAP GUI data to Google Sheets

@JoelTing 7 ай бұрын

Hi there. Currently I don't have a tutorial for Google Sheets. From a brief research, Python looks to be a simpler way to do it instead of VBA as there's already libraries readily available. Probably it can work this way: Script to extract data from SAP GUI into Excel -> Use Python Pandas to read the Excel data -> send data via Google Sheets API.

@kishorebagubali 7 ай бұрын

Hi @joel, Thanks for the video... I have a request, how to record 2 different transactions (MM60 and ME2M) and export the output to one Excel file with each sheets seperately... I tried to do it after watching your video of dynamic scripting...but for me it's not working... Can you help me how to do it?

@JoelTing 7 ай бұрын

Hi there. What is currently not working for you? The steps should be quite similar you record yourself going through both tcode, then once both are exported, open both sheet, copy the data and placed it in your desired Excel workbook.

@saketvaldez7697 2 жыл бұрын

Hey!! Thanks a lot. I have downloaded multiple excel sheets from SAP through VBA, now I want to close those open excel sheet automatically after download through VBA, kindly help

@JoelTing 2 жыл бұрын

Hi Saket Valdez, you can include the following at the end of your script: ThisWorkbook.Save Application.Quit ThisWorkbook.Saved = True This will quit the Excel Application. Currently I am not able to find a workaround to close individual Excel sheets opened by SAP.

@manbearpig3231 2 жыл бұрын

Great videos, is there any chance you could do a video, where, you can create a macro in excel to run multiple reports from preferably iw49n and info from iw32 documents > completion confemation in sap, the data to then be uploaded onto an existing document and the for it to use that data on the document to create a job in iw31? Cheers,

@JoelTing 2 жыл бұрын

Hey there! Unfortunately, I'm not able to do that as I am not authorised to use the 2 T-codes you mentioned in my organization. If there's a flow to it, usually that's workable, but I cant be sure as I'm not able to test it out from my end.

@manbearpig3231 2 жыл бұрын

@@JoelTing cheers, even just how sap can transfer to excel without exporting would be great, I can figure the rest out. It just that part of the macro really I need.

@JoelTing 2 жыл бұрын

I see. If you don't need it to be exported in Excel, would the data you need be accessible directly by the script itself? That would be one way to do it. The other may be just export them in Excel, read from them, pass the info to the next process, and by the end of the macro, just delete those exported files automatically.

@manbearpig3231 2 жыл бұрын

@@JoelTing yeah accessible directly to the script, I can to it by doing 2 scripts already, like a first step and second step one after exporting, but just wanted it as one script really so no export.. if that's possible

@NareshKumar-ge4vl 10 ай бұрын

Hi Joel, I'm really seeking for this same. I need the same what you showed with the text files. I have a 20 set of text files which needs to be used for downloading a report. And need to save with the same name. Could you pls help me out with this

@JoelTing 10 ай бұрын

Hi there, what do you mean by 20 sets of text file and download 1 report? What is the outcome? Is it only one spreadsheet at the end? Since yhou are saving it with the same name, do you mean you will replace the previous version upon export?

@saket004 2 жыл бұрын

Hi Joel. This is not wokring for TCODE /nf.27 The print option is not being recorded by SAP GUI And hence my export is not working. If you can help me with this.

@JoelTing 2 жыл бұрын

Hi saket004, apologies for the late reply. You can try out the following code which I found on SAP forum (credits to Script Man): set Wshell = CreateObject("WScript.Shell") Do bWindowFound = Wshell.AppActivate("Print") WScript.Sleep 1000 Loop Until bWindowFound bWindowFound = Wshell.AppActivate("Print") if (bWindowFound) Then Wshell.appActivate "Print" WScript.Sleep 100 Wshell.sendkeys "{ENTER}" end if bWindowFound = Wshell.AppActivate("Print") if (bWindowFound) Then Wshell.appActivate "Print" WScript.Sleep 100 Wshell.sendkeys "{TAB}{ENTER}" end if bWindowFound = Wshell.AppActivate("Print") if (bWindowFound) Then Wshell.appActivate "Print" WScript.Sleep 100 Wshell.sendkeys "{TAB}{TAB}{ENTER}" end if on error resume next session.findById("wnd[1]").close on error goto 0

@sagedoescode Жыл бұрын

Joel, what if no data is found from the i 3 per example, how do I make it skip to the next one without needing to re-run the script?

@JoelTing Жыл бұрын

Hi. What you can try doing is try to read the status bar with session.findById("wnd[0]/sbar").Text in cases where there's no data found. For example, you can put an if statement, For i = 1 to 3 if Not(session.findById("wnd[0]/sbar").Text = "No items selected") Then "run your script" End If Next i. The text needs to be changed based on what is being displayed at your end.

@sagedoescode Жыл бұрын

@@JoelTing thank you very much, you should make a error handling video 😁😄

@user-ec9hq6md1f Ай бұрын

hello, i'm trying to do similar task of extracting multiple reports but from different sap systems, i tried to do multiple vbscript one after another but after extracting 1 system, it stops, do you have an idea how to do it?

@JoelTing Ай бұрын

Hey there. Did you use "/n" in front of the Tcodes that you are running in the command bar? Eg. "/nKE24"

@naseeba9165 Жыл бұрын

Hi Joel, when I try to run vba code, its shows a 'runtime error 13' type mismatch. I follow your code for SAP connection with excel, I don't know where its going wrong for me. How should I proceed with this?

@JoelTing Жыл бұрын

Hi Naseeba, I'm not too sure what happened there. You can look into the portion where you declare your variable as which type of data. I may need to see your code to understand better.

@lucaschiqui 8 ай бұрын

Joel I have a question after watching this. Suppose that the for loop works the other way: for example you have to iterate over an excel table but you paste the values in a different sap cell. The value from the cell (1,1) in excel goes to the cell in (0,1) in SAP, and then the value from the excel cell (2,1) goes to the Sap cell (0,2), and it goes... My problem is that I can't find the way to iterate over sap cells. "session.findById("wnd[0]...45A-MABNR[0,1]").text" would be the first cell but the script presents an error if I put an iterator and write "...MABNR[0,iterator]").text". Do you get what I'm saying? And I can't just simply copy and paste all the column because SAP has it limits (I can copy only 8 registers, then I have to put enter and copy 7 by 7) and it depends on the amount of values I have.

@JoelTing 8 ай бұрын

Hi Lucas, your thinking is spot on. To iterate it correctly, you need to treat it like a string and concatenate them together. In your example, instead of "...MABNR[0,iterator]").text", use "...MABNR[0," & iterator & "]").text" One thing to take note when using this approach is to make sure you adjust for the VerticalScrollbar position as well if it exceeds the number of entries available on screen. When you need to scroll down in the UI, you need to scroll down using the script as well.

@lucaschiqui 8 ай бұрын

Thanks @@JoelTing I'm gonna try that!

@luisramirez2123 7 ай бұрын

Hello Lucas. I just wanted to share with you what I found by accident to copy more than 8 registers. You can simply COPY and PASTE, but you have to use the SAP clipboard icon to paste the copied values of your Excel column instead of using the CTRL+V method. At least that allowed me to copy 30+ values in one shot (thats what I need normally). Idon't know what the limit of the clipboard is, but certainly was a very nice thing to find out. Hope that may help you or someone else that has the same problem when copying more than 8 items.

@lucaschiqui 6 ай бұрын

@@luisramirez2123 Hi Luis, thanks for sharing that, but I dont seem to find that icon, where it might be? Because if you are talking about the "Upload and portapapeles" option that's not the one I have in my trx.

@latestupdatedeepsingh8771 2 жыл бұрын

DEAR JOEL I AM WAITING YOUR NEW VIDEO,,,AND HOW ARE YOU?

@JoelTing 2 жыл бұрын

Hello there. I'm fine. Apologies for being missing for a while. Have been quite busy as there's some major change in my career. I'll get back to upload schedule as soon as possible. As always, thanks for the support!

@mohammadsuheb.l9872 11 ай бұрын

How to set date from - to in excel and what's the code i have to add ?

@JoelTing 11 ай бұрын

Hi there, just convert the date into the string format that your SAP will need and store it in a variable, then replace the hard coded date in your recorded script with the variable.

@christianrees3676 Жыл бұрын

I have a question. Suppose I want to extract data from Sap, but my company has a lot of layers or visualizations and I have to choose the correct one in order to automatize the process. Which is the code to do that?

@christianrees3676 Жыл бұрын

I mean, we found out with the macro recorder that you can choose it by order number, but if any person add a layer, script would be broken... We need a way to select by the name or user of the layer

@JoelTing Жыл бұрын

Hi Christian Rees, is there a way to identify whether a layer have been added?

@sidaggarwal7305 4 ай бұрын

Hello Joel we stuck in this VBA please help on this. in this code line we face the issue this line code is red colour mark

@JoelTing 3 ай бұрын

Which code you are referring to?

@SureshKumar-tj1yh 2 жыл бұрын

Bro, I need code for selecting specific layout before extracting the report. Kindly help

@JoelTing 2 жыл бұрын

Hi Suresh Kumar, could you select the layout in the recording itself when you are recording your steps?

@eduardo18xxx 2 жыл бұрын

Is there personality clases?

@JoelTing 2 жыл бұрын

Excuse me, I didn't really understand your question there.

@bluemingu 6 ай бұрын

hi, i'm new to sap vba. i have a question, what if the last row is not a data? how can we specify the last row on some condition? thanks

@JoelTing 6 ай бұрын

Hi there! What do you mean it's not data? It depends on what your condition is. For example, if your pattern is always the 2nd last row, then use the formula, then -1. Or, if it is even more complex where you need to check the value before you can decide, we can always make a conditional statement to check the cell values to get the desired "last row" in your context before running the loop.

@bluemingu 6 ай бұрын

For example I have a data value from column 1-10 but I only want to use the value from column 1-8. How should I write to that condition?@@JoelTing

@JoelTing 6 ай бұрын

In VBA, if you need to work specifically with values from columns 1 to 8, you can clearly define this column range in your code. Suppose your task involves handling a range within a worksheet. In this case, you can create a Range object encompassing only columns 1 to 8. Typically, you would determine the last row number to define this range. Once set, you can assign specific columns to represent certain data, like setting column 1 as "ID", column 2 as "name", and column 4 as "address". If you don't need to use a particular column, like column 3 in this instance, you can simply exclude it from assignment.

@maheshchagi6349 Жыл бұрын

Hello Joel, Hope you are doing good, Need your help on my problem, I am trying to write a script, we do have a transaction code to maintain 1000+ vendor codes to exclud, but the thing is we only can copy and paste 17 codes in a shot and by using page down key we can update 1000+ codes so in macro how do we have to write coding or script which will take automatically even after 17 codes by using page down until finishes the list of 1000 codes? Appreciate your help,,,,

@JoelTing Жыл бұрын

Hello. Sorry for the late reply. When you record the script, any button is being registered when you use the page down key? Is there a paste from clipboard button available any where in the interface?

@maheshchagi6349 Жыл бұрын

@@JoelTing Thanks for your prompt reply bro, Yes while recording script page down key is also recorded however there is no clipboard to paste, My issue is I am facing difficulties in looping the task , how can I loop this for 17 line items in macro, If you get a chance plz share ur mail, so I can share my coding to understand more, tried to send over here but but it's not getting delivered.

@JoelTing Жыл бұрын

@@maheshchagi6349 my email is joelting92@gmail.com. if possible, try sending me a recording of what you are trying to achieve as well

@maheshchagi6349 Жыл бұрын

@@JoelTing Hello Brother, I have shared SAP script recording via mail, plz do help

@maheshchagi6349 Жыл бұрын

@@JoelTing Hi Joel, Hope you are doing well, Thanks you so much for your help, with the help of you I have completed my task,

@RaMeShBaBu-gf1wc 6 ай бұрын

Hi user is asking don't want close macro file but need to close SAP download reports only. Please do video on this. SAP reports downloading multiple files so those files need to close only not macro file.

@JoelTing 6 ай бұрын

I do not have a working solution now. A workaround that can be done is to export it using a local file instead of a spreadsheet and then look for some ways to automate the extraction from the local file to Excel.

@AKASHMANDAL2386 11 ай бұрын

Can I automate SAP GUI without RZ11 authorization?

@JoelTing 11 ай бұрын

Hi there. Unfortunately no. If it is disabled, you will not be able to connect to a SAP session with VBA.

@AKASHMANDAL2386 11 ай бұрын

@@JoelTing actually its tough to get authorization

@JoelTing 11 ай бұрын

@@AKASHMANDAL2386 I understand. SAP scripting may be disallowed in some organizations due to security concerns. Since that is disabled, it is meant to block any scripts or automation from running. There should not be any way around it.

@maheshchagi6349 Жыл бұрын

Error message was " wrong number of arguments or invalid property assignment" Could you please help me to get it fixed this

@JoelTing Жыл бұрын

Do you mind to share your script?

@maheshchagi6349 Жыл бұрын

@@JoelTing thanks for ur prompt reply, I got it n working fine, once again thanks a lot for your video on SAP macro looping, waiting for many more

@sohelnadaf3191 Жыл бұрын

If I click on button I want to download table data in pdf format. With json data as well as odata

@JoelTing Жыл бұрын

Hi Sohel, usually are you able to download in pdf or json format manually?

@sohelnadaf3191 Жыл бұрын

@@JoelTing yes usually, I can download table data in excel format (.xlsx) formatbut I want to download in pdf format

@sohelnadaf3191 Жыл бұрын

@@JoelTing 😊 thank you for responding 🙏

@JoelTing Жыл бұрын

If you are not able to export to PDF directly from SAP, you can try using the ExportAsFixedFormat method to export your excel into a PDF file after the spreadsheet is exported.

@sohelnadaf3191 Жыл бұрын

@@JoelTing I will try if do you have any blog related this please, share with me thanks 😊

@fakhrullnoor4102 Жыл бұрын

I keep getting error for script out of range for lastrow = .... Any idea?

@JoelTing Жыл бұрын

Usually it happens when you try to reference a variable that does not exist in a code. Did you declare lastrow? Have you assign it to a value?

@fakhrullnoor4102 Жыл бұрын

@@JoelTing yes i already assign the value.. btw.. can i ask for your suggestion.. where i got 3 column start at A17:C20 .. is there any way for me to do the looping where those data need to be input into the sap.. means need to put cells A17,B17,C17 then loop A18,B18,C18.. loop until C20..i really stuck..

@JoelTing Жыл бұрын

If you are still not able to resolve, you can share with me your script so that I can have a quick look. As for your question, you can just use a for loop. For i = 17 to lastrow column1 = Range("A"&i).Value column2 = Range("B"&i).Value column3 = Range("C"&i).Value 'Your SAP script here and push in the value using variables above Next i

@fakhrullnoor4102 Жыл бұрын

@@JoelTing Thanks a lot..it works! Such a genius... Thanks ya

@mohanishmurkute5831 Жыл бұрын

Coding is a challenge to me. I'm bad at it. Where n how can I learn it?

@JoelTing Жыл бұрын

KZfaq can be a good starting point for you to learn some of the basics for VBA. But like everything else, coding needs practise. Once you know the basics, best way to advance your skill would be to have a hands on project and learn along the way.

@Jakepr26 Жыл бұрын

Use this code to close the export files without closing all of excel. Dim export1 As Object Dim export2 As Object Set export1 = GetObject(“file location\Export 1.file extension”).Application Set export2 = GetObject(“file location\Export 2.file extension”).Application export1.Workbooks(1).Close export2.Workbooks(1).Close export2.Quit Error Situations: This will close either all of the Excel files opened after the Macro is run, explicitly listed or not (running correctly); or it will close all of the excel files opened prior to running the macro, to include the file running the Macro, if one of the explicitly listed files is not open or saved to the listed location. Because of the above, when this code fails, it will not throw a debug error. If you can figure out how to improve it, so an error is handled instead of the files closing, I’d be very grateful. Otherwise, I run this code everyday without issue.

@JoelTing Жыл бұрын

Hi, thanks for the code! Yes, you are right. Closing the exported workbook is always a hit or miss for me as well. I gave up working on that part as I felt like it's not a good use of my time to keep trying and debugging this one simple action of closing the workbook, since I automated the most time consuming part. Nevertheless, I will try to look for a workaround when I have the time.

@m4ki4v3lo1 8 ай бұрын

@@JoelTing good stuff thanks for the information.

@m4ki4v3lo1 8 ай бұрын

I typically used something like this: session.findById("wnd[1]/usr/ctxtDY_PATH").Text = "C:\temp\" session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "export.XLSX" session.findById("wnd[1]/tbar[0]/btn[11]").press 'delay to allow file to open Dim i As Long For i = 1 To 10000 'adjust as needed Application.StatusBar = "Delay count " & i DoEvents Next i 'do stuff with the export Workbooks("export.XLSX").Close

@laradanicalim3954 Жыл бұрын

Hello, Joel! This is exactly what I needed! I tried for 2 instances. It successfully ran for the first extraction but I keep having "Run-time error '619' The control could not be found by id " here: session.FindById("wnd[1]/tbar[0]/btn[3]").Press ---> clicking the back button So, it does not proceed to the next extraction.

@JoelTing Жыл бұрын

Hi there! It seems like your steps got stuck after the first extraction. The button it is trying to click is not there. When it stopped, are you able to see where how it is different from the time when you run the script? You can go into debugging mode, then try running the script step by step and see why the button that it should be clicking is no longer there after the first extraction.

@laradanicalim3954 Жыл бұрын

I tried recording again and used the shortcut key for back (F3) instead of clicking the back button. And it worked! Thank you so much! Your vids are very helpful. 👏🏻👏🏻

@laradanicalim3954 Жыл бұрын

@@JoelTing Hmmm how about handling 'No record found'. How do you keep the script running? I'm sorry, I'm new to SAP GUI scripting.

@JoelTing Жыл бұрын

@@laradanicalim3954 It depends on how the no record found is displayed. You can combine if statements and on error statements to decide what to do in the event that there's no data there or if there's an absence of an element in the screen. It can get a bit complicated, but you really need to try it out to know how to handle the different events.

@laradanicalim3954 Жыл бұрын

@@JoelTing Got this! After several tests, finally it worked! Thank you!