Announcement:
wanna exchange links? contact me at sapchatroom@gmail.com.
Posted by
Admin at
Question from sandrapoh on Sep 28 at 10:26 PM Good morning. I am new to BW automation.
Currently, my company is using BW 3.5 and I have one workbook that contains 5 worksheet containing different queries. I have written one vba program to refresh the 5 worksheet.
Understood from all the websites that I can gather that we can use the command (Run "sapbex.xla!SAPBEXsetVariables", shtPST.Range("rngBWVariables")) to set variables to auto refresh the reports without the selection screens appearing. But it does not work for me. Would appreciate any advice with reference to below.
Below is the values in the sheet "SAPBEXqueries".
43
20
4 YUPSALOR 1 P I EQ SA31 SA31 SGORG 60 0 0 20 0SALESORG
4 YUPDIVIS 1 P I EQ HH HH SGDIV 20 0 0 20 0DIVISION
4 YUSPRGRP 1 S 0 0 0 20 YMATSALES__YPRODH2
4 YUSMAT 1 S 0 0 0 20 YMATERIAL
4 YCICALMN 1 I I BT 201308 08/2013 201408 08/2014 AUG 2013 40 AUG 2014 40 0 20 0CALMONTH
5 YUPSALOR 1 P I EQ SA31 SA31 SGORG 60 0 0 20 0SALESORG
5 YUPDIVIS 1 P I EQ HH HH SGDIV 20 0 0 20 0DIVISION
5 YPLOCTN 1 P I EQ A320 A320 SGPlnt 40 0 0 20 0PLANT
5 YUSMTLEV 1 S I BT ML1 ML1 ML4 ML4 01 20 04 20 0 20 YAPOLPRO__YAPOATTL1
5 YUSMAT 1 S 0 0 0 20 YMATERIAL
6 YCSLOCCD 1 I EQ A3 A3 SGComp 40 0 0 20 0COMP_CODE
6 YUPLODIV 1 P I EQ HH HH SGDIV 20 0 0 20 0DIVISION
6 YUSLOPLT 1 S I EQ A320 A320 SGPlnt 40 0 0 20 0PLANT
6 YUSLOABD 1 S 0 0 0 20 YMATPLNTM__0ABCKEY
6 YUSPRHL2 1 S 0 0 0 20 YMATERIAL__YPRODH2
6 YCILOCMT 1 I I BT 201308 08/2013 201408 08/2014 AUG 2013 40 AUG 2014 40 0 20 0CALMONTH
7 YUPLOCCD 1 P I EQ A3 A3 SGComp 40 0 0 20 0COMP_CODE
7 YUPLODIV 1 P I EQ HH HH SGDIV 20 0 0 20 0DIVISION
7 YUSLOPLT 1 S I EQ A320 A320 SGPlnt 40 0 0 20 0PLANT
7 YUSLOABC 1 S 0 0 0 20 YMATPLANT__0ABCKEY
7 YUSPRHL2 1 S 0 0 0 20 YMATERIAL__YPRODH2
7 YCPLOCDY 1 P I EQ 20140928 09/28/2014 0 0 0 20 0CALDAY
8 YUPSALOR 1 P I EQ SA31 SA31 SGOrg 60 0 0 20 0SALESORG
8 YUSDISCH 1 S 0 0 0 20 0DISTR_CHAN
8 YUPDIVIS 1 P I EQ HH HH SGDIV 20 0 0 20 0DIVISION
8 YUSMAJCL 1 S 0 0 0 20 YSOLDTOSL__0CUST_GRP1
8 YUSLCHAN 1 S 0 0 0 20 YSOLDTOSL__0CUST_GRP4
8 YUSLOCCH 1 S 0 0 0 20 YSOLDTOSL__0CUST_GRP5
8 YUSPRGRP 1 S 0 0 0 20 YMATSALES__YPRODH2
8 YUSCATEG 1 S 0 0 0 20 YMATSALES__YPRODH3
8 YUSBRNDI 1 S 0 0 0 20 YMATSALES__YPRODH4I
8 YUSSUBBR 1 S 0 0 0 20 YMATSALES__YPRODH5
8 YCICALMN 1 I I BT 201404 04/2014 201409 09/2014 APR 2014 40 SEP 2014 40 0 20 0CALMONTH
Then, the vba that I wrote is as below.
Function DownloadBWReport()
Initialise
Application.ScreenUpdating = False
Set shtPST = ThisWorkbook.Worksheets("SAPBEXqueries")
' create Logon & RFC-Handle
'load SAP functionality by opening BEX file ( use your installation path)
If Dir("C:\Program Files (x86)\", vbDirectory) <> "" Then
Workbooks.Open ("C:\Program Files (x86)\Common Files\SAP Shared\BW\SAPBex.xla")
Else
Workbooks.Open ("C:\Program Files\Common Files\SAP Shared\BW\SAPBex.xla")
End If
With Run("sapbex.xla!sapbexGetConnection") ' call the connection
' Set the params for Auto logon
.client = "100"
.user = strBID ' BW USER ID
.Password = strBPW ' I recommend to create a dummy reporting user for this task
'.Language = "EN"
'.SystemNumber = "01" '
'.ApplicationServer = "XX.XX.XX.XX"
.System = "1 ABS Production BW/SEM ABP"
.SystemNumber = "01"
.GroupName = "DIA_GROUP"
.MessageServer = "XX.XX.XX.XXX"
.UseSAPLOgonIni = False 'important for automatic connection
.logon 1, True ' This will provide a dialog to appear
If .IsConnected <> 1 Then
.logon 0, False
If .IsConnected <> 1 Then
MsgBox "Connection to BW failed"
Exit Function
End If
Else
'Set g_oFunction = CreateObject("SAP.Functions")
'Set g_oFunction.Connection = g_oConnection
End If
End With
Run "sapbex.xla!sapbexinitConnection" ' this will enable the connection you just created
' make SAPBEXqueries sheet visible
' usually, parametes are stored in columns GE..GH
' (column FY refers to the query where the parameters will be used
' and it is important if you have more than one query sheet in your spreadsheet)
shtPST.Visible = True
shtPST.Select
' assign variable as parameter for my query
shtPST.Range("FY2") = 1
shtPST.Range("rngBWVariables").ClearCont ents
shtSET.Range("rngBWSetting").Copy
shtPST.Range("rngBWVariables").PasteSpec ial xlPasteValues
' to hide again SAPBEXqueries sheet I found out that I need
' to go out of it to another sheet, or I won't be allowed to close it
'shtPST.Visible = False
Run "sapbex.xla!SAPBEXsetVariables", shtPST.Range("rngBWVariables")
Run "sapbex.xla!SAPBEX.SAPBEXrefresh", True
End Function
| Reply to this email to post your response. __.____._ |   _.____.__ |
