Refresh a workbook via VBA

[Former Member]

Hi all,

I have a workbook with a query inside and so far, users have to download this workbook, connect to SAP, and then refresh the query, changing the month. It's a simple sales report on a monthly basis.

My idea is to create a vba macro, so the users can pick-up the month from a combo-box and then click on "Refresh" and the query is automatically updated.

We were using Bex3 for those reports but now we have to migrate to Bex7 mandatory and as far as I understood, once you migrate the report to Bex7 this macro doesn't work anymore.

What I did so far is the connection string (yeah, so easy, I know...) and I need a guideline with function should I use to automatically refresh the query

Dim ObjBAPIControl As Object

Dim Sapconnection As Object

Set ObjBAPIControl = CreateObject("SAP.Functions")

Set Sapconnection = ObjBAPIControl.Connection

Sapconnection.Logon

If Sapconnection.Logon(0, True) <> True Then

MsgBox "CANNOT Log on to SAP"

End If

Thanks a lot for your help!

Martin

[Former Member]

Hi Ernst

Thanks for your feedback. I'm not into SAP so much, so apologies if I misunderstood you.

What I've done is just inserted a query in the excel workbook, and everytime I need to refresh it, I go to the add-in, press the refresh query, enter the new values in the entry mask and this is it.

What I would like to do, it's to offer the user to pickup the month from a drop-list in excel, then click on a "refresh query" button (to call the vba macro) and avoid the user to see the variable entry mask

Hope I was crear

Thanks a lot!

Martin

Edited by: Tincho2010 on May 19, 2011 1:03 PM

[Former Member]

Hello Martin, If you could clearify from where you want to fetch the information within SAP? Is the information possible to get from i.e SE16 in a table? Or is it some sort of report you've created that needs to be executed? There's alot of recent examples on how to execute and run RFC_READ_TABLE on the forum if the data is available there.

ScriptMan has posted a good example of this here:

And i've tried to explain what I see as the most critical bits of that code example here:

Best Regards,

Ernst

Edited by: Zeblion on May 19, 2011 12:49 PM