Add a data source using VBA

[former_member199967]

Dear all,

I am quite new in SAP Analysis for Office. One of my first steps is to set up an automatic reporting workbook for products.

I have to show n times a specific query (query designer). Every product should have an own spread sheet in Excel and the queries shuold be filled by different variables (product_Ids).

My questions:

Is there a way to add the data source (query) into an Excel wokbook using VBA?

I want to prepare a kind of master list containing the relevant product IDs. The macro should Loop the table and

a) add an addtional table in the Excel workbook

b) add a copy of the specific product query and

c) fill the query's variable with the relevant product-Id

In the user guide I found a command to set/Change the query variables but I did not find a way to add a new query.

Many thanks in advance

Hans-Peter

[Former Member]

I am a beginner tinkering on somebody else's code so please be lenient and further correct my errors. I tried to code and played with the VBA help The following worked with me:

Function currAddressTest(dataRangeTest As Range) As String  currAddressTest = ActiveSheet.Name & "$" & dataRangeTest.Address(False, False)  End Function

When I select data source argument for my function, it is turned into Sheet1$A1:G3 format. If excel changes it to Table1[#All] reference in my formula, the function still works properly

I then used it in your function (tried to play and add another argument to be injected to WHERE...

Function SQL(dataRange As Range, CritA As String)

Dim cn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim currAddress As String

currAddress = ActiveSheet.Name & "$" & dataRange.Address(False, False)

strFile = ThisWorkbook.FullName

strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _

& ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")

Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = "SELECT * FROM [" & currAddress & "]" & _

         "WHERE [A] =  '" & CritA & "'  " & _

         "ORDER BY 1 ASC"

rs.Open strSQL, cn

SQL = rs.GetString

End Function

Hope your function develops further, Have a nice day!

[former_member199967]

Hi Lisa,

thanks for your reply.

Unfortunately I think this does not work as I have to add data sources (= BEx queries from SAP BW).

The Manual way would be using this button on the Analysis ribbon:

Thanks

Regards

Hans-Peter

[former_member199967]

I am using SAP Business Objects Analysis 2.2.1.53213 with Excel 2016