SAP T-Codes in VBA

[LearnVBA83]

Hi all,

I've got a complex problem I'm hoping someone can shed some light on. I'm an accountant and when doing my reconciliations I used to run two separate transaction codes in SAP and I would snipit the account activity into my reconciliations to verify the balances/activity matched my top sheet. Well I discovered that SAP has a scripting tool and I recorded myself running the GD20 and GD13 Tcodes in SAP. From there I opened the script and pasted the code into Excel's VBA. With a little manipulation I was able to open my reconciliations change the numerical month and click a button that would execute the GD20 and GD13 in SAP, download the reports to excel, copy and paste the activity in my reconciliation and close the downloaded reports. The biggest challenge I had while doing this was pausing the macro until the SAP reports downloaded and opened in excel. The below code is the only method I've found to work.

Sub RunAll()


Application.ScreenUpdating = False


Call GD20Rec
Call GD13Rec
Application.OnTime Now + TimeValue("00:00:06"), "GD13Report"
Application.OnTime Now + TimeValue("00:00:08"), "GD20Report"


Application.ScreenUpdating = True


End Sub

Now that i've gotten a little better in VBA, I would like to either:
1. Create a custom button on a ribbon that I click and it allows me to select the reconciliation (excel file) to run the macro on then saves and closes that file
Problems I'm running into - I use the below code to get openfile and the first macro works but the other macros don't recognize the file that I've opened. Is there code I can put in the other 3 macros to recognize that wb is the file that I selected to open?

FileName = Application.GetOpenFilename(Title:="Select File to Open")
If FileName = False Then Exit Sub
Set wb = Workbooks.Open(FileName:=FileName, Format:=4)


2. I'd love to create a loop if at all possible that would loop through each file in a folder and run the GD20 and GD13 in SAP for the account template in that file, download that excel document, copy and paste it into the activity tab in my reconciliation, and close the files then loop to the next file in the folder. The problem I had with this was the macro loops through all of the files in the folder and says it's complete and then the very first excel download from SAP pops up. So I can't figure out how to stop the macro's long enough to let SAP download and open those files. It's almost like while my macro is running excel won't let another file open until it's completely finished.

Any help would be greatly appreciated.

Thanks!

 

[tonyyy]

Item 1: Variables can be declared for a Procedure, Module or Project. If all your macros are in a single module, you would place the "Dim wb As Workbook" statement in the declarations area (ie, above the Sub line). If your macros are in separate modules, you would write your declaration as "Public wb As Workbook". You might want to review the Declaring Variables documentation.

Item 2: Pausing a macro until an external process completes can be tricky, and depends on the type of process. One place to start is the DoEvents Function. I've had the most success with this approach when getting data from external sources. Otherwise you might consider a couple other approaches from this post.

Good luck!

tonyyy

 

[LearnVBA83]

Item 1: Variables can be declared for a Procedure, Module or Project. If all your macros are in a single module, you would place the "Dim wb As Workbook" statement in the declarations area (ie, above the Sub line). If your macros are in separate modules, you would write your declaration as "Public wb As Workbook". You might want to review the Declaring Variables documentation.

Item 2: Pausing a macro until an external process completes can be tricky, and depends on the type of process. One place to start is the DoEvents Function. I've had the most success with this approach when getting data from external sources. Otherwise you might consider a couple other approaches from this post.

Good luck!

tonyyy

Thanks so much Tony! I look forward to reading the other post and documentation you provided!

 

[tonyyy]

You're welcome.

Cheers!