How To – Formatierung nach der Datensperre anpassen


Oft besteht die Anforderung dem Benutzer eine visuelle Rückmeldung zu geben ob die Daten gesperrt sind oder nicht. Falls der User die Sperren selbst setzt, wird standardmäßig das Format der Query nicht angepasst. Die Zellen sehen immer noch so aus, als wären sie eingabebereit. Weiterlesen

Denis Reis ist Business Intelligence Consultant und gibt als Buchautor sein Wissen rund um den SAP Projektalltag weiter. Wenn Sie tatkräftige Unterstützung bei Ihren SAP BI Projekten benötigen, können Sie ihn über Xing, LinkedIn oder Facebook kontaktieren.
Des Weiteren unterrichtet er Projektmanagement und Controlling an der Wiesbaden Business School. Der aus Düsseldorf stammende Familienmensch zählt zu denjenigen, die auf komplizierte Darstellungen verzichten und das Ganze auf den Punkt bringen.

Excel VBA – Tabelle kopieren

Dieser Beitrag ist ein Teil der VBA Grundlagen Serie.
In diesem Beitrag zeige ich Ihnen wie Sie über VBA Tabellen kopieren können.
Diese Aufgabe können Sie ganz einfach mit einer Zeile Code erledigen.

Sub TabelleKopieren()

Tabelle1.Copy After:=Worksheets(Worksheets.Count)

End Sub

Mit der Methode Copy kopieren wir die jeweilige Tabelle. Mit dem Argument After geben wir an, dass wir die Tabelle ans Ende der Mappe kopieren wollen. Das Ende ermitteln wir über die Anzahl der bereits vorhandenen Tabellen Worksheets.Count.

Wenn Sie die Tabelle in eine neue Arbeitsmappe kopieren wollen, verwenden Sie einfach die Methode Copy ohne Argumente.

Sub TabelleKopieren()

Sheet1.Copy

End Sub

Die Beispiel Excel Datei mit dem sofort einsetzbaren Makro und 111+ andere Makros, die Ihr Leben leichter machen, finden Sie hier.

Beeindrucke deinen Chef mit coolen Excel Tricks!

  • Lasse dich von deinen Kollegen nicht abhängen!
  • Spare Zeit und nutze diese für angenehmere Dinge
  • Steigere deine Produktivität und Effizienz
  • Es ist leicht - In meinem Newsletter gebe ich Schritt für Schritt Anleitungen die jeder versteht
  • Bei der Anmeldung zu meinem Newsletter erhälst du das Buch „Excel Tricks die deinen Chef beeindrucken“ als Willkommensgeschenk.
20 Excel Tricks

Jetzt anfordern!

* Pflichtfeld
 
Kein SPAM. 100% sicher.

Quellen und weiterführende Literatur:
Bernd Held – VBA mit Excel: Das umfassende Handbuch

Falls Ihnen dieser Beitrag weitergeholfen hat, wäre es eine sehr nette Anerkennung meiner Arbeit wenn Sie z.B. Ihre Bücher über Amazon bestellen würden. Wenn Sie ein Produkt kaufen, erhalte ich dafür eine Provision. Für Sie ändert sich am Preis des Produktes gar nichts. Ich möchte mich an dieser Stelle jetzt schon für Ihre Unterstützung bedanken.

Dieser Beitrag ist ein Teil eines Kurses, der Ihnen einen Schnelleinstieg in VBA ermöglichen soll und ist nach der folgenden Struktur aufgebaut:


Grundlagen:
Einführung in die objektorientierte Programmierung
Objekte, Methoden, Eigenschaften, Ereignisse und der Objektkatalog
Entwicklertools einblenden
Erstes Makro anlegen

Erste Makros:
Das aktuelle Datum in eine Zelle eintragen
Mehrzeiliges Meldungsfenster
Eine Tabelle nach einer Rückfrage löschen
Eine Eingabemaske erstellen

Zellen:
Bereich auslesen
Definierten Bereich füllen und leeren
Zellen im Bereich summieren
Bedingte Summation – Alle Werte größer als 100 summieren
Letzte verwendete Zeile bestimmen
Letzte verwendete Spalte bestimmen
Kommentare hinzufügen

Bedingungen:
Prüfen ob der Inhalt einer Zelle numerisch ist
Prüfen ob die Zelle leer ist
Zelle auf ein gültiges Datum prüfen
Sprachabhängige Meldungen ausgeben (SELECT CASE)

Schleifen:
Alle verwendete Zeilen bearbeiten
Wochenenden hervorheben
Tabellen über eine Schleife ausblenden
Leere Spalten verstecken
Alle Zellen in dem markierten Bereich bearbeiten (Großschreibung / Kleinschreibung)
Mehrere Bereiche vereinen und alle Zellen formatieren
Alle Tabellen schützen

Zeilen:
Jede zweite Zeile einfärben
Zeilenhöhe festlegen
Gesamte Zeile summieren
Leere Zeilen löschen
Zeilen ausblenden
Mehrmals vorkommende Einträge filtern (Unikatsliste erstellen)
Bestimmte Zeilen in eine andere Tabelle kopieren
Leere Zeilen einfügen
Eigene Suchfunktion bauen – bestimmte Zeile finden mit Kundennr. und Namen. Eigenschaft .Row der Variable Treffer

Spalten:
Daten filtern
Spaltenbereite anpassen
Spalte summieren
Spalten ausblenden
Leere Spalten löschen
Spalten formatieren
Spalten in eine andere Tabelle kopieren
Neue Spalten einfügen
Bestimmte Spalte finden

Tabellen:
Tabellennamen auslesen
Neue Tabellen einfügen und benennen
Tabellen löschen
Formeln entfernen
Druckbereich bestimmen
Farbe der Tabellenreiter ändern
Tabelle kopieren
Prüfung Tabellen- oder Diagrammblatt
Eigene Kopf-und Fußzeilen erstellen

Arbeitsmappen:
Arbeitsmappe erzeugen
Arbeitsmappe speichern
Verknüpfungen entfernen
Dokumenteigenschaften auslesen und verändern
Persönliche Informationen aus Dateieigenschaften löschen
Daten und Informationen über Dokumentprüfung entfernen
Arbeitsmappe löschen

Applikation:
Neuberechnung ausschalten
Excel Funktionen ein- und ausschalten
Ansichtsoptionen über Makro steuern
Excel-Version auslesen
Umgebungsvariablen auslesen

Mappen-Ereignisse:
Scrollarea festlegen
Das Schließen der Mappe verhindern
Druckvorgang abfangen
Auswahl bestimmter Tabellen unterbinden
Neue Tabellen sofort löschen
Tastenkombination definieren

Tabellen-Ereignisse:
Bereichsauswahl verhindern
Geänderte Zellen markieren
Eingaben konvertieren (z.B. Großbuchstaben)
Änderungen in einem Change Log dokumentieren
Eingaben sofort rückgängig machen
Tabellenverschiebung verhindern
Eine Zeile per Doppelklick einfärben
Rechten Mausklick deaktivieren

Standardfunktionen:
Nach einer Zeichenkette suchen – InStr
Länge mit Left und Len messen und korrigieren
Pfad und Dateinamen mit Funktionen InStrRev, Left und Mid trennen
Zeichen ersetzen mit Replace
Leerzeichen mit Trim entfernen
Zeichenkette aufteilen und zusammenfügen mit Split und Join
Mit DatePart Quartal und Kalenderwoche aus einem Datum ableiten
Differenz von zwei Datumswerten mit DateDiff berechnen
Text mit CDate in Datum umwandeln

Benutzerdefinierte Funktionen
Nur sichtbare Zellen summieren
Fett formatierte Werte zählen
Letzten Wert in der Zeile ausgeben
Eine eigene AutoText Funktion erstellen
Formeltext in Zelle ausgeben
Sonderzeichen entfernen
Prüfen, ob ein Bereich leer ist
Auf Ablaufdatum prüfen

Modul-Funktionen
Tabellenexistenz prüfen
Existenz der Arbeitsmappe prüfen
Prüfen ob eine Mappe geöffnet ist
Letzten Monatstag ermitteln
Alle Leerzeichen entfernen
Alphanumerische Zeichen löschen
Tabellenschutz prüfen
Spalten auf Inhalte prüfen
Quersumme berechnen

Denis Reis ist Business Intelligence Consultant und gibt als Buchautor sein Wissen rund um den SAP Projektalltag weiter. Wenn Sie tatkräftige Unterstützung bei Ihren SAP BI Projekten benötigen, können Sie ihn über Xing, LinkedIn oder Facebook kontaktieren.
Des Weiteren unterrichtet er Projektmanagement und Controlling an der Wiesbaden Business School. Der aus Düsseldorf stammende Familienmensch zählt zu denjenigen, die auf komplizierte Darstellungen verzichten und das Ganze auf den Punkt bringen.

How To BPC – Variablenübergabe an ScriptLogic DataPackages aus Excel

Die DataPackages, auch Berichtspakete genannt, haben sehr viele Parameter. So muss der Benutzer jedes Mal, wenn er eine Kopier-Funktion aufruft, festlegen wie die Datensätze gehandhabt werden, ob nach der Kopie die Standard-Script Logic ausgeführt werden soll. Danach wird er gefragt ob eventuelle Datensperren (Work Status) berücksichtigt werden sollen. Anschließend muss er die Quell- und die Zielwerte eingeben.

DataPackage - Eingabe der Werte

Quell- und Zielwerte definieren

Schließlich muss der User entscheiden, ob das DataPackage sofort oder später ausgeführt werden soll. Obwohl SAP damit wirbt, dass BPC sehr benutzerfreundlich ist, ist der durchschnittliche Fachbereich schnell überfordert. Falsch gesetzte Parameter können leicht zu unerwünschten Ergebnissen führen.
Nun könnten Sie das Standardpaket kopieren und das Skript so anpassen, dass die User es leichter verstehen.

Skript eines Pakets anpassen

DataPackage Skript ändern

Allerdings muss sich der User dann trotzdem durch mehrere Screens durchklicken, obwohl er einfach von einer Version in die andere kopieren will.
In diesem Beitrag zeige ich Ihnen eine elegantere Lösung. Der Benutzer kann die Quell- und Zielversion über Dropdowns auswählen und die Funktion über ein Button ausführen.

Versionen können über Dropdown ausgewählt werden

Unser Ziel

Der Weg dahin führt über mehrere Stationen. Neben unserer Tabelle mit der Auswahl haben wir eine Tabelle mit den Bereichen PARAMETERS und PACKAGE.
Der Bereich PARAMETERS enthält alle Parameter für die jeweilge Funktion mit den zugehörigen Werten.

PARAMETERS Bereich

PARAMETERS Bereich

Der Bereich PACKAGE enthält die Angaben zu der DataPackage die ausgeführt werden soll. Die SAP legt beim Aufruf eines DataPackages eine XML-Datei namens DMUserSelection.xml mit den Parametern an. Wir legen unsere eigene Datei für diese Zwecke an. Daher wird im PACKAGE Bereich auch der Name für die Datei angegeben, die die Werte für die Eingabeaufforderung (Prompt) enthält.

PACKAGE Bereich

PACKAGE Bereich

Über ein Button wird das VBA Makro aufgerufen, welches das in PACKAGE definierte DataPackage mit den in PARAMETERS definierten Parametern versorgt.

Zunächst legen den Bereich PARAMETERS für die Parameter an.
Das Standardpackage Copy erwartet folgende Parameter:

  • CLEARDATA – enthält 0 oder 1. Standardeinstellung ist 0.
  • RUNLOGIC – enthält 0 oder 1. Standardeinstellung ist 0.
  • CHECKLCK – enthält 0 oder 1. Standardeinstellung ist 1.
  • SELECTION – Dimensionen und Member die den Datenbereich der Quelle festlegen.
  • TOSELECTION – Dimensionen und Member die den Datenbereich des Ziels festlegen. Dimensionen werden über die Konstante DIMS festgelegt. Den Inhalt können Sie in Berichtspaketeinstellungen unter Konstanten sehen.
DataPackage Script

Einstellungen des Berichtspakets

Diese Informationen können Sie der jeweiligen Skriptdatei entnehmen. Gehen Sie dabei wie folgt vor.
Wählen Sie im EPM AddIn den Reiter Daten-Manager aus. Gehen Sie anschließend auf OrganisierenBerichtspaketliste organisieren.

Daten-Manager Pakete organisieren

Berichtspaketliste organisieren

Wählen Sie nun das Berichtspaket (DataPackage), in unserem Beispiel ist es Copy, mit Rechtsklick aus und wählen Sie Berichtspaket ändern aus.

DataPackage anpassen

Berichtspaket ändern

Wählen Sie anschließend Skript ändern aus und klicken Sie im folgenden Fenster oben rechts auf Erweitert.

DataPackage Skript anpassen

Skript des Berichtspakets ändern

Sie sehen nun das Skript für die Berichtspaket Prozesskette mit allen Parametern. Diese Parameter übernehmen wir in unseren Excel Bereich PARAMETERS.
Erstellen Sie im neuen Reiter eine Tabelle mit dem folgenden Aufbau.

Aufbau PARAMETERS

Aufbau von PARAMETERS

Die Promptnamen sollten mit denen im Skript übereinstimmen, inklusive %-Zeichen. Zum Beispiel %CLEARDATA%. Prompt Typ kann entweder ein Parameter (z.B. Ladeeinstellungen) oder StringListPairs (Variablen) sein. Dimensionen sind nur für StringListPairs relevant. Als Wert tragen Sie den Wert ein, den das jeweilige Merkmal annehmen soll. Diese können hardgecodet sein oder über Formeln auf Usereingaben beziehen.
Dabei können Sie EPM-Formeln oder auch ganz normale Excel-Bezüge nutzen. In unserem Beispiel beziehe ich mich auf die Dropdown-Felder.
Als nächstes legen wir einen Bereich an. Markieren Sie dazu alle Zellen (A1 bis D20). Tragen Sie in das Namenfeld oben links PARAMETERS ein und drücken Sie ENTER.

Bereich benennen

Bereich benennen

Wir haben nun erfolgreich einen Bereich für die Parameter erstellt.
Im nächsten Schritt legen wir den PACKAGE Bereich an. Die notwendigen Infos sind in der Tabelle UJD_PACKAGES2 abgelegt. Um an die notwendigen Informationen zu kommen müssen wir uns zunächst in SAP GUI einlogen. Rufen Sie anschließend die Transaktion SE16 (Data Browser) auf. Geben Sie UJD_PACKAGES2 als Tabellennamen an.

TA SE16 Data Browser

Data Browser

Geben Sie im nächsten Screen APPSET_ID (Ihre Umgebung) sowie die APP_ID (Ihr Model / Cube) ein und drücken Sie Ausführen (F8).
Sie sehen nun alle Berichtpakete für Ihren Cube. Suchen Sie nach der PACKAGE_ID Copy. Selektieren Sie die Zeile und drücken Sie auf Anzeigen (F7).

Inhalt der Tabelle UJD_PACKAGES2

Einträge der Tabelle UJD_PACKAGES2

Sie sehen nun alle Informationen die wir für den PACKAGE Bereich benötigen.

Informationen über das DataPackage

Details für den PACKAGE Bereich

Tragen Sie diese Infos in die Excel Tabelle nach dem folgenden Muster ein:

UJD_PACKAGES2 Feld Excel Feld Beispiel
CHAIN ID Filename /CPMB/COPY
GROUP ID GroupId Data Management
PACKAGE ID PackageDesc Copy
PACKAGE ID PackageId Copy
PACKAGE TYPE PackageType Process Chain
TEAM ID TeamID <leer>
USER GROUP UserGroup 0010

Beachten Sie dabei, dass das Feld UserGroup als vierstelliges Feld mit führenden Nullen eingetragen werden muss. Darüber hinaus beinhaltet das Feld PromptFile den Pfad für die Datei, die auf dem PC von allen Benutzern existieren muss. Zum Beispiel C:\DataManagerPromptFile.txt.
Legen Sie nun den PACKAGE Bereich an.

Wählen Sie nun die Felder aus (F1 bis G9), tragen Sie PACKAGE im Namensbereich ein und drücken Sie ENTER.

Im nächsten Schritt legen wir den Visual Basic Code an. Wie man ein VBA Makro anlegt beschreibe ich in dem Beitrag Excel VBA Grundlagen. In der Entwicklungsumgebung (ALT+F11), gehen Sie auf ExtrasVerweise (engl. ToolsReferences) und wählen Sie die folgenden Einträge aus.

VBA Referenzen

VBA Verweise

Legen Sie nun ein neues Modul an und fügen Sie das folgende Coding ein:

Public Sub executeDmPackageWithParameters()
' Create the Answer Prompt file in the location
' specified in the Name Range "PACKAGE"
createAnswerPromptFile "PACKAGE", "PARAMETERS"
' Get the DM Automation class instance
Dim objDMautomation As EPMAddInDMAutomation
Set objDMautomation = New EPMAddInDMAutomation
' Run the package specified in Excel Name Range "PACKAGE",
' using the promtp file specified in Name Range "PACKAGE"
objDMautomation.RunPackage objPackageFromSheet("PACKAGE"), _
strFilename("PACKAGE")
End Sub
Private Function strFilename(strRange As String) As String
' Get the range in which the DM package paramteres is set
Dim rngPackageRange As Range
Set rngPackageRange = Application.Names(strRange).RefersToRange
' Loop through the rows
For i = 1 To rngPackageRange.Rows.Count
If rngPackageRange(i, 1).Value = "PromptFile" Then
strFilename = rngPackageRange(i, 2).Value
Exit Function
End If
Next
End Function
Private Function strPackageDescription(strRange As String) As String
' Get the range in which the DM package paramteres is set
Dim rngPackageRange As Range
Set rngPackageRange = Application.Names(strRange).RefersToRange
' Loop through the rows
For i = 1 To rngPackageRange.Rows.Count
If rngPackageRange(i, 1).Value = "PackageId" Then
strPackageDescription = rngPackageRange(i, 2).Value
Exit Function
End If
Next
End Function
Private Function objPackageFromSheet(strRange As String) As ADMPackage
' Get the range in which the DM package paramteres is set
Dim rngPackageRange As Range
Set rngPackageRange = Application.Names(strRange).RefersToRange
' Create the ADM Package object
Set objPackageFromSheet = New ADMPackage
' Loop through the rows
For i = 1 To rngPackageRange.Rows.Count
Select Case rngPackageRange(i, 1).Value
Case "Filename"
objPackageFromSheet.Filename = rngPackageRange(i, 2).Value
Case "GroupId"
objPackageFromSheet.GroupId = rngPackageRange(i, 2).Value
Case "PackageDesc"
objPackageFromSheet.PackageDesc = rngPackageRange(i, 2).Value
Case "PackageId"
objPackageFromSheet.PackageId = rngPackageRange(i, 2).Value
Case "PackageType"
objPackageFromSheet.PackageType = rngPackageRange(i, 2).Value
Case "TeamId"
objPackageFromSheet.TeamId = rngPackageRange(i, 2).Value
Case "UserGroup"
objPackageFromSheet.UserGroup = rngPackageRange(i, 2).Value
End Select
Next
End Function
Private Sub createAnswerPromptFile(strPackageName As String, _
strParametersName As String)
' Create a new XML document
Dim objDOM As DOMDocument
Set objDOM = New DOMDocument
' Set the processing instruction of the XML document
Dim objProcessingInstruction As IXMLDOMProcessingInstruction
Set objProcessingInstruction = _
objDOM.createProcessingInstruction("xml", _
" version='1.0' encoding='utf-16'")
objDOM.appendChild objProcessingInstruction
' Create root element
Dim objRootElem As IXMLDOMElement
Set objRootElem = objDOM.createElement("ArrayOfAnswerPromptPersistingFormat")
objDOM.appendChild objRootElem
' XSI Attribute
Dim objMemberRel As IXMLDOMAttribute
Set objMemberRel = objDOM.createAttribute("xmlns:xsi")
objMemberRel.NodeValue = "http://www.w3.org/2001/XMLSchema-instance"
objRootElem.setAttributeNode objMemberRel
' XSD Attribute
Set objMemberRel = objDOM.createAttribute("xmlns:xsd")
objMemberRel.NodeValue = "http://www.w3.org/2001/XMLSchema"
objRootElem.setAttributeNode objMemberRel
' Get the range of cells containing the parameters
Dim rngParameters As Range
Set rngParameters = ThisWorkbook.Names(strParametersName).RefersToRange
'Excel.Names(strParametersName).RefersToRange
'
Dim objCurrentStringPairParent As IXMLDOMElement
' Loop through each row
For i = 1 To rngParameters.Rows.Count
' See which type of parameter is being passed
Select Case rngParameters(i, 2).Value
' If it is a single Parameter, then add a parameter node to the root node
Case "Parameter"
addSingleSelectionParameterToXML rngParameters(i, 1).Value, _
rngParameters(i, 4).Value, _
objRootElem, _
objDOM
' If it is a list of values
Case "StringListPairs"
' If it's a new set of String List Pairs, then create a new parent
If rngParameters(i, 1).Value <> strCurrentStringPair Then
strCurrentStringPair = rngParameters(i, 1).Value
Set objCurrentStringPairParent = _
getStringListPairParent(rngParameters(i, 1).Value, _
objRootElem, _
objDOM)
End If
' Add the Dimension Name and Value to the parent
addStringListPair rngParameters(i, 3).Value, _
rngParameters(i, 4).Value, _
objCurrentStringPairParent, _
objDOM
End Select
Next
' Create the File object
Dim objFile As FileSystemObject
Set objFile = New FileSystemObject
' Create a stream to create and write to the file
Dim objStream As TextStream
Set objStream = objFile.OpenTextFile(strFilename(strPackageName), _
ForWriting, True)
' Write the name of the DM package first and then the XML output
objStream.WriteLine strPackageDescription(strPackageName) & _
"{param_separator}" & _
objDOM.XML
' Close the file
objStream.Close
End Sub

Private Function addStringListPair(strVariableName As String, _
strValue As String, _
objParent As IXMLDOMElement, _
objDOM As DOMDocument)
' Create the "StringListPair" node
Dim objStringListPairElement As IXMLDOMElement
Set objStringListPairElement = _
objDOM.createElement("StringListPair")
objParent.appendChild objStringListPairElement
' Create the "Str" element containing the variable name
Dim objStrElement As IXMLDOMElement
Set objStrElement = objDOM.createElement("str")
objStringListPairElement.appendChild objStrElement
objStrElement.Text = strVariableName
' Create the "lst" element
Dim objLstElement As IXMLDOMElement
Set objLstElement = objDOM.createElement("lst")
objStringListPairElement.appendChild objLstElement
' Create the "string" element containing the variable value
Dim objStringElement As IXMLDOMElement
Set objStringElement = objDOM.createElement("string")
objLstElement.appendChild objStringElement
objStringElement.Text = strValue
End Function
Private Function getStringListPairParent(strVariableName As String, _
objParent As IXMLDOMElement, _
objDOM As DOMDocument) As IXMLDOMElement
' Create the "AnswerPromptPersistingFormat" node
Dim objAnswerPromptPersistingFormatElement As IXMLDOMElement
Set objAnswerPromptPersistingFormatElement = _
objDOM.createElement("AnswerPromptPersistingFormat")
objParent.appendChild objAnswerPromptPersistingFormatElement
' Create the "_ap" node
Dim objApElement As IXMLDOMElement
Set objApElement = objDOM.createElement("_ap")
objAnswerPromptPersistingFormatElement.appendChild objApElement
' Create the parameter name element
Dim objParameterElement As IXMLDOMElement
Set objParameterElement = objDOM.createElement("Name")
objApElement.appendChild objParameterElement
objParameterElement.Text = strVariableName
' Create the values element
Dim objValuesElement As IXMLDOMElement
Set objValuesElement = objDOM.createElement("Values")
objApElement.appendChild objValuesElement
' Create the "_apc" node
Set getStringListPairParent = objDOM.createElement("_apc")
objAnswerPromptPersistingFormatElement.appendChild getStringListPairParent
End Function

Private Function addSingleSelectionParameterToXML(strVariableName As String, _
strValue As String, _
objParent As IXMLDOMElement, _
objDOM As DOMDocument)
' Create the "AnswerPromptPersistingFormat" node
Dim objAnswerPromptPersistingFormatElement As IXMLDOMElement
Set objAnswerPromptPersistingFormatElement = _
objDOM.createElement("AnswerPromptPersistingFormat")
objParent.appendChild objAnswerPromptPersistingFormatElement
' Create the "_ap" node
Dim objApElement As IXMLDOMElement
Set objApElement = objDOM.createElement("_ap")
objAnswerPromptPersistingFormatElement.appendChild objApElement
' Create the parameter name element
Dim objParameterElement As IXMLDOMElement
Set objParameterElement = objDOM.createElement("Name")
objApElement.appendChild objParameterElement
objParameterElement.Text = strVariableName
' Create the values element
Dim objValuesElement As IXMLDOMElement
Set objValuesElement = objDOM.createElement("Values")
objApElement.appendChild objValuesElement
' Create the string element with the value passed to the parameter
Dim objStringElement As IXMLDOMElement
Set objStringElement = objDOM.createElement("string")
objValuesElement.appendChild objStringElement
objStringElement.Text = strValue
End Function

Legen Sie nun die Dropdowns und den Button für VBA an. Über diesen Button rufen Sie das Makro executeDmPackageWithParameters aus.
Das Datenpaket wird nun ausgeführt. Betrachten Sie den Status indem Sie auf Daten-ManagerStatus anzeigen gehen. Die Daten wurden erfolgreich kopiert.

Sie wissen nun wie Sie Standardfunktionen mit Variablen füttern können.
Betrachten wir im nächsten Schritt eigene Skript Logiken die über ein DataPackage ausgeführt werden. Da werden die Variablen zum Beispiel über PROMPT %CATEGORY_DIM% oder COMPCODE (Für Kundeneigene Dimensionen) angefordert, anschließend verwenden wir in der Logik %CATEGORY_SET%.
So sieht mein DataPackage aus

PROMPT(SELECTINPUT,,,,"%CATEGORY_DIM%,COMPCODE,SAORG")
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SUSER,%USER%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPPSET,%APPSET%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPP,PSX_PLAN)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SELECTION,%SELECTION%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,LOGICFILENAME,DIR_STRAT.LGF)

In meiner RUNALLOCATION ScriptLogic verteile ich die Planwerte anhand der Ist-Werte des Vorjahres.

*XDIM_MEMBERSET ACCOUNT = SAAMO
*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%
*XDIM_ADDMEMBERSET CATEGORY = ACTUAL
*XDIM_MEMBERSET COMPCODE = %COMPCODE_SET%
*XDIM_MEMBERSET SAORG = %SAORG_SET%
*XDIM_MEMBERSET SAOFF = 
*XDIM_MEMBERSET DICHA = 
*XDIM_MEMBERSET PRODH1 = 
*XDIM_MEMBERSET PRODUCT = 
*XDIM_MEMBERSET INPUTCURRENCY = EUR
*XDIM_MEMBERSET TIME = BAS(%YEAR%.TOTAL)
*XDIM_ADDMEMBERSET TIME = BAS(%YEAR%(-1).TOTAL)

*RUNALLOCATION
*FACTOR = USING/TOTAL

*DIM ACCOUNT         WHAT = SAAMO;    WHERE = <<<;       USING = <<<;             TOTAL = <<<;
*DIM CATEGORY        WHAT = %CATEGORY_SET%;      WHERE = <<<;       USING = ACTUAL;          TOTAL = <<<;
*DIM COMPCODE        WHAT = %COMPCODE_SET%;     WHERE = <<<;       USING = <<<;             TOTAL = <<<;
*DIM SAORG           WHAT = %SAORG_SET%;     WHERE = <<<;       USING = <<<;             TOTAL = <<<;
*DIM SAOFF           WHAT = NONE;     WHERE = BAS(ALL);  USING = BAS(ALL);        TOTAL = <<<;
*DIM DICHA           WHAT = NONE;     WHERE = BAS(ALL);  USING = BAS(ALL);        TOTAL = <<<;
*DIM PRODH1          WHAT = NONE;     WHERE = BAS(ALL);  USING = BAS(ALL);        TOTAL = <<<;
*DIM PRODUCT         WHAT = NONE;     WHERE = NONE;  USING = BAS(ALL);        TOTAL = <<<;
*DIM INPUTCURRENCY   WHAT = EUR;      WHERE = <<<;       USING = <<<;             TOTAL = <<<;
*DIM TIME            WHAT = BAS(%YEAR%.TOTAL); WHERE = <<<;       USING = BAS(%YEAR%(-1).TOTAL); TOTAL = <<<;

*ENDALLOCATION

Wenn ich nun diese ScriptLogic über ein DataPackage aufrufen will, muss ich die Variablen noch einmal eingeben. Das ist natürlich kein Zustand.

Variablenwerte müssen vom User ausgewählt werden

Auswahl der Variablen

Sie kennen jetzt einen Weg um dieses Problem zu lösen. Als erstes legen wir unseren PARAMETERS Bereich an.
Die Werte für die zu füllenden Variablen lesen wir mithilfe der EPM-Formel EPMContextMember aus.

Im nächsten Schritt legen wir den PACKAGE Bereich mit den Infos aus der Tabelle UJD_PACKAGES2 an.

PACKAGE Bereich für ScriptLogic

ScriptLogic PACKAGE Bereich

Als nächsten aktivieren wir die Verweise und legen ein Modul mit dem Coding an. Danach triggern wir das executeDmPackageWithParameters Makro über ein Button. Et voilà ! Das DataPackage mit unserer ScriptLogic wird ausgeführt.

Was ist aber, wenn wir auch die Version für Referenzdaten (in unserem Beispiel ACTUAL) variabel setzen wollen? Dazu können wir die Formelvariablen benutzen. Zunächst müssen wir unsere ScriptLogic Verteilungsfunktion anpassen und die Variable $VAR_CAT$ hinzufügen.

 *XDIM_MEMBERSET ACCOUNT = SAAMO
*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%
*XDIM_ADDMEMBERSET CATEGORY = $VAR_CAT$
*XDIM_MEMBERSET COMPCODE = %COMPCODE_SET%
*XDIM_MEMBERSET SAORG = %SAORG_SET%
*XDIM_MEMBERSET SAOFF = <ALL>
*XDIM_MEMBERSET DICHA = <ALL>
*XDIM_MEMBERSET PRODH1 = <ALL>
*XDIM_MEMBERSET PRODUCT = <ALL>
*XDIM_MEMBERSET INPUTCURRENCY = EUR
*XDIM_MEMBERSET TIME = BAS(%YEAR%.TOTAL)
*XDIM_ADDMEMBERSET TIME = BAS(%YEAR%(-1).TOTAL)

*RUNALLOCATION
*FACTOR = USING/TOTAL

*DIM ACCOUNT         WHAT = SAAMO;    WHERE = <<<;       USING = <<<;             TOTAL = <<<;
*DIM CATEGORY        WHAT = %CATEGORY_SET%;      WHERE = <<<;       USING = $VAR_CAT$;          TOTAL = <<<;
*DIM COMPCODE        WHAT = %COMPCODE_SET%;     WHERE = <<<;       USING = <<<;             TOTAL = <<<;
*DIM SAORG           WHAT = %SAORG_SET%;     WHERE = <<<;       USING = <<<;             TOTAL = <<<;
*DIM SAOFF           WHAT = NONE;     WHERE = BAS(ALL);  USING = BAS(ALL);        TOTAL = <<<;
*DIM DICHA           WHAT = NONE;     WHERE = BAS(ALL);  USING = BAS(ALL);        TOTAL = <<<;
*DIM PRODH1          WHAT = NONE;     WHERE = BAS(ALL);  USING = BAS(ALL);        TOTAL = <<<;
*DIM PRODUCT         WHAT = NONE;     WHERE = NONE;  USING = BAS(ALL);        TOTAL = <<<;
*DIM INPUTCURRENCY   WHAT = EUR;      WHERE = <<<;       USING = <<<;             TOTAL = <<<;
*DIM TIME            WHAT = BAS(%YEAR%.TOTAL); WHERE = <<<;       USING = BAS(%YEAR%(-1).TOTAL); TOTAL = <<<;

*ENDALLOCATION

Die Variable $VAR_CAT$ muss durch das DataPackage (Berichtspaket) übergeben werden.
Daher passen wir im nächsten Schritt das Skript des Berichtspakets an.

PROMPT(SELECTINPUT,,,,"%CATEGORY_DIM%,COMPCODE,SAORG")
PROMPT(TEXT, %VAR_CAT%, "Version der Referenzwerte",). 

INFO(%EQU%,=)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SUSER,%USER%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPPSET,%APPSET%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPP,PSX_PLAN)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SELECTION,%SELECTION%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,LOGICFILENAME,TEST.LGF)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,REPLACEPARAM,VAR_CAT%EQU%%VAR_CAT%)

Neben unseren alten Variablen soll nun auch die Variable %VAR_CAT% gefüllt werden. Diese muss dann in das Format der ScriptLogic (allerdings ohne $) übersetzt werden.
Dies geschieht in der letzten Zeile des Codes.
Im nächsten Schritt erweitern wir unseren PARAMETERS Bereich. Den Namensbereich können Sie im Reiter FormelnNamens-Manager ändern.

PARAMETRS Bereich wird um Variablen erweitert

Zusätzlicher Prompt Typ – Variable

Um den zusätzlichen Prompt Typ verarbeiten zu können, müssen wir unsere VBA Logik ändern. Glücklicherweise stimmt die XML-Syntax für Variablen mit der der Parameter überein. Wir können also dieselbe Methode addSingleSelectionParameterToXML nutzen. Erweitern Sie einfach das Makro createAnswerPromptFile um eine zusätzliche CASE Abfrage.

' If it is a single variable, then add a variable node to the root node
Case "Variable"
addSingleSelectionParameterToXML rngParameters(i, 1).Value, _
rngParameters(i, 4).Value, _
objRootElem, _
objDOM

Zur Sicherheit nochmal das gesamte Coding:

 Public Sub executeDmPackageWithParameters()
' Create the Answer Prompt file in the location
' specified in the Name Range "PACKAGE"
createAnswerPromptFile "PACKAGE", "PARAMETERS"
' Get the DM Automation class instance
Dim objDMautomation As EPMAddInDMAutomation
Set objDMautomation = New EPMAddInDMAutomation
' Run the package specified in Excel Name Range "PACKAGE",
' using the promtp file specified in Name Range "PACKAGE"
objDMautomation.RunPackage objPackageFromSheet("PACKAGE"), _
strFilename("PACKAGE")
End Sub
Private Function strFilename(strRange As String) As String
' Get the range in which the DM package paramteres is set
Dim rngPackageRange As Range
Set rngPackageRange = Application.Names(strRange).RefersToRange
' Loop through the rows
For i = 1 To rngPackageRange.Rows.Count
If rngPackageRange(i, 1).Value = "PromptFile" Then
strFilename = rngPackageRange(i, 2).Value
Exit Function
End If
Next
End Function
Private Function strPackageDescription(strRange As String) As String
' Get the range in which the DM package paramteres is set
Dim rngPackageRange As Range
Set rngPackageRange = Application.Names(strRange).RefersToRange
' Loop through the rows
For i = 1 To rngPackageRange.Rows.Count
If rngPackageRange(i, 1).Value = "PackageId" Then
strPackageDescription = rngPackageRange(i, 2).Value
Exit Function
End If
Next
End Function
Private Function objPackageFromSheet(strRange As String) As ADMPackage
' Get the range in which the DM package paramteres is set
Dim rngPackageRange As Range
Set rngPackageRange = Application.Names(strRange).RefersToRange
' Create the ADM Package object
Set objPackageFromSheet = New ADMPackage
' Loop through the rows
For i = 1 To rngPackageRange.Rows.Count
Select Case rngPackageRange(i, 1).Value
Case "Filename"
objPackageFromSheet.Filename = rngPackageRange(i, 2).Value
Case "GroupId"
objPackageFromSheet.GroupId = rngPackageRange(i, 2).Value
Case "PackageDesc"
objPackageFromSheet.PackageDesc = rngPackageRange(i, 2).Value
Case "PackageId"
objPackageFromSheet.PackageId = rngPackageRange(i, 2).Value
Case "PackageType"
objPackageFromSheet.PackageType = rngPackageRange(i, 2).Value
Case "TeamId"
objPackageFromSheet.TeamId = rngPackageRange(i, 2).Value
Case "UserGroup"
objPackageFromSheet.UserGroup = rngPackageRange(i, 2).Value
End Select
Next
End Function
Private Sub createAnswerPromptFile(strPackageName As String, _
strParametersName As String)
' Create a new XML document
Dim objDOM As DOMDocument
Set objDOM = New DOMDocument
' Set the processing instruction of the XML document
Dim objProcessingInstruction As IXMLDOMProcessingInstruction
Set objProcessingInstruction = _
objDOM.createProcessingInstruction("xml", _
" version='1.0' encoding='utf-16'")
objDOM.appendChild objProcessingInstruction
' Create root element
Dim objRootElem As IXMLDOMElement
Set objRootElem = objDOM.createElement("ArrayOfAnswerPromptPersistingFormat")
objDOM.appendChild objRootElem
' XSI Attribute
Dim objMemberRel As IXMLDOMAttribute
Set objMemberRel = objDOM.createAttribute("xmlns:xsi")
objMemberRel.NodeValue = "http://www.w3.org/2001/XMLSchema-instance"
objRootElem.setAttributeNode objMemberRel
' XSD Attribute
Set objMemberRel = objDOM.createAttribute("xmlns:xsd")
objMemberRel.NodeValue = "http://www.w3.org/2001/XMLSchema"
objRootElem.setAttributeNode objMemberRel
' Get the range of cells containing the parameters
Dim rngParameters As Range
Set rngParameters = ThisWorkbook.Names(strParametersName).RefersToRange
'Excel.Names(strParametersName).RefersToRange
'
Dim objCurrentStringPairParent As IXMLDOMElement
' Loop through each row
For i = 1 To rngParameters.Rows.Count
' See which type of parameter is being passed
Select Case rngParameters(i, 2).Value
' If it is a single Parameter, then add a parameter node to the root node
Case "Parameter"
addSingleSelectionParameterToXML rngParameters(i, 1).Value, _
rngParameters(i, 4).Value, _
objRootElem, _
objDOM
' If it is a list of values
Case "StringListPairs"
' If it's a new set of String List Pairs, then create a new parent
If rngParameters(i, 1).Value <> strCurrentStringPair Then
strCurrentStringPair = rngParameters(i, 1).Value
Set objCurrentStringPairParent = _
getStringListPairParent(rngParameters(i, 1).Value, _
objRootElem, _
objDOM)
End If
' Add the Dimension Name and Value to the parent
addStringListPair rngParameters(i, 3).Value, _
rngParameters(i, 4).Value, _
objCurrentStringPairParent, _
objDOM
' If it is a single variable, then add a variable node to the root node
Case "Variable"
addSingleSelectionParameterToXML rngParameters(i, 1).Value, _
rngParameters(i, 4).Value, _
objRootElem, _
objDOM
End Select
Next
' Create the File object
Dim objFile As FileSystemObject
Set objFile = New FileSystemObject
' Create a stream to create and write to the file
Dim objStream As TextStream
Set objStream = objFile.OpenTextFile(strFilename(strPackageName), _
ForWriting, True)
' Write the name of the DM package first and then the XML output
objStream.WriteLine strPackageDescription(strPackageName) & _
"{param_separator}" & _
objDOM.XML
' Close the file
objStream.Close
End Sub

Private Function addStringListPair(strVariableName As String, _
strValue As String, _
objParent As IXMLDOMElement, _
objDOM As DOMDocument)
' Create the "StringListPair" node
Dim objStringListPairElement As IXMLDOMElement
Set objStringListPairElement = _
objDOM.createElement("StringListPair")
objParent.appendChild objStringListPairElement
' Create the "Str" element containing the variable name
Dim objStrElement As IXMLDOMElement
Set objStrElement = objDOM.createElement("str")
objStringListPairElement.appendChild objStrElement
objStrElement.Text = strVariableName
' Create the "lst" element
Dim objLstElement As IXMLDOMElement
Set objLstElement = objDOM.createElement("lst")
objStringListPairElement.appendChild objLstElement
' Create the "string" element containing the variable value
Dim objStringElement As IXMLDOMElement
Set objStringElement = objDOM.createElement("string")
objLstElement.appendChild objStringElement
objStringElement.Text = strValue
End Function
Private Function getStringListPairParent(strVariableName As String, _
objParent As IXMLDOMElement, _
objDOM As DOMDocument) As IXMLDOMElement
' Create the "AnswerPromptPersistingFormat" node
Dim objAnswerPromptPersistingFormatElement As IXMLDOMElement
Set objAnswerPromptPersistingFormatElement = _
objDOM.createElement("AnswerPromptPersistingFormat")
objParent.appendChild objAnswerPromptPersistingFormatElement
' Create the "_ap" node
Dim objApElement As IXMLDOMElement
Set objApElement = objDOM.createElement("_ap")
objAnswerPromptPersistingFormatElement.appendChild objApElement
' Create the parameter name element
Dim objParameterElement As IXMLDOMElement
Set objParameterElement = objDOM.createElement("Name")
objApElement.appendChild objParameterElement
objParameterElement.Text = strVariableName
' Create the values element
Dim objValuesElement As IXMLDOMElement
Set objValuesElement = objDOM.createElement("Values")
objApElement.appendChild objValuesElement
' Create the "_apc" node
Set getStringListPairParent = objDOM.createElement("_apc")
objAnswerPromptPersistingFormatElement.appendChild getStringListPairParent
End Function

Private Function addSingleSelectionParameterToXML(strVariableName As String, _
strValue As String, _
objParent As IXMLDOMElement, _
objDOM As DOMDocument)
' Create the "AnswerPromptPersistingFormat" node
Dim objAnswerPromptPersistingFormatElement As IXMLDOMElement
Set objAnswerPromptPersistingFormatElement = _
objDOM.createElement("AnswerPromptPersistingFormat")
objParent.appendChild objAnswerPromptPersistingFormatElement
' Create the "_ap" node
Dim objApElement As IXMLDOMElement
Set objApElement = objDOM.createElement("_ap")
objAnswerPromptPersistingFormatElement.appendChild objApElement
' Create the parameter name element
Dim objParameterElement As IXMLDOMElement
Set objParameterElement = objDOM.createElement("Name")
objApElement.appendChild objParameterElement
objParameterElement.Text = strVariableName
' Create the values element
Dim objValuesElement As IXMLDOMElement
Set objValuesElement = objDOM.createElement("Values")
objApElement.appendChild objValuesElement
' Create the string element with the value passed to the parameter
Dim objStringElement As IXMLDOMElement
Set objStringElement = objDOM.createElement("string")
objValuesElement.appendChild objStringElement
objStringElement.Text = strValue
End Function

Genauso können Sie mit der Zeit verfahren.
ScriptLogic:

 *XDIM_MEMBERSET ACCOUNT = SAAMO
*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%
*XDIM_ADDMEMBERSET CATEGORY = $VAR_CAT$
*XDIM_MEMBERSET COMPCODE = %COMPCODE_SET%
*XDIM_MEMBERSET SAORG = %SAORG_SET%
*XDIM_MEMBERSET SAOFF = <ALL>
*XDIM_MEMBERSET DICHA = <ALL>
*XDIM_MEMBERSET PRODH1 = <ALL>
*XDIM_MEMBERSET PRODUCT = <ALL>
*XDIM_MEMBERSET INPUTCURRENCY = EUR
*XDIM_MEMBERSET TIME = BAS($VAR_AY$.TOTAL)
*XDIM_ADDMEMBERSET TIME = BAS($VAR_PY$.TOTAL)

*RUNALLOCATION
*FACTOR = USING/TOTAL

*DIM ACCOUNT         WHAT = SAAMO;    WHERE = <<<;       USING = <<<;             TOTAL = <<<;
*DIM CATEGORY        WHAT = %CATEGORY_SET%;      WHERE = <<<;       USING = $VAR_CAT$;          TOTAL = <<<;
*DIM COMPCODE        WHAT = %COMPCODE_SET%;     WHERE = <<<;       USING = <<<;             TOTAL = <<<;
*DIM SAORG           WHAT = %SAORG_SET%;     WHERE = <<<;       USING = <<<;             TOTAL = <<<;
*DIM SAOFF           WHAT = NONE;     WHERE = BAS(ALL);  USING = BAS(ALL);        TOTAL = <<<;
*DIM DICHA           WHAT = NONE;     WHERE = BAS(ALL);  USING = BAS(ALL);        TOTAL = <<<;
*DIM PRODH1          WHAT = NONE;     WHERE = BAS(ALL);  USING = BAS(ALL);        TOTAL = <<<;
*DIM PRODUCT         WHAT = NONE;     WHERE = NONE;  USING = BAS(ALL);        TOTAL = <<<;
*DIM INPUTCURRENCY   WHAT = EUR;      WHERE = <<<;       USING = <<<;             TOTAL = <<<;
*DIM TIME            WHAT = BAS($VAR_AY$.TOTAL); WHERE = <<<;       USING = BAS($VAR_PY$.TOTAL); TOTAL = <<<;

*ENDALLOCATION

DataPackage Skript:

PROMPT(SELECTINPUT,,,,"%CATEGORY_DIM%,COMPCODE,SAORG")
PROMPT(TEXT, %VAR_CAT%, "Version der Referenzwerte",). 
PROMPT(TEXT, %VAR_AY%, "Aktuelles Jahr",). 
PROMPT(TEXT, %VAR_PY%, "Vorheriges Jahr",). 

INFO(%EQU%,=)
INFO(%TAB%,;)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SUSER,%USER%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPPSET,%APPSET%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPP,PSX_PLAN)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SELECTION,%SELECTION%)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,LOGICFILENAME,TEST.LGF)
TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,REPLACEPARAM,VAR_CAT%EQU%%VAR_CAT%%TAB%VAR_AY%EQU%%VAR_AY%%TAB%VAR_PY%EQU%%VAR_PY%)

PARAMETERS Bereich:

PARAMETERS Bereich mit Zeitvariablen

PARAMETERS Bereich mit Zeitvariablen

Viel Spass dabei!

Quellen:
Tristan Colgate et al – How to Call a BPC Data Manager Package from VBA
Sergey Nelyapenko – How to deal with BPC Data Manager packages programmatically
SAP Hilfe

Falls Ihnen dieser Beitrag weitergeholfen hat, wäre es eine sehr nette Anerkennung meiner Arbeit wenn Sie z.B. Ihre Bücher über Amazon bestellen würden. Wenn Sie ein Produkt kaufen, erhalte ich dafür eine Provision. Für Sie ändert sich am Preis des Produktes gar nichts. Ich möchte mich an dieser Stelle jetzt schon für Ihre Unterstützung bedanken.

Denis Reis ist Business Intelligence Consultant und gibt als Buchautor sein Wissen rund um den SAP Projektalltag weiter. Wenn Sie tatkräftige Unterstützung bei Ihren SAP BI Projekten benötigen, können Sie ihn über Xing, LinkedIn oder Facebook kontaktieren.
Des Weiteren unterrichtet er Projektmanagement und Controlling an der Wiesbaden Business School. Der aus Düsseldorf stammende Familienmensch zählt zu denjenigen, die auf komplizierte Darstellungen verzichten und das Ganze auf den Punkt bringen.

Excel VBA – Farbe der Tabellenreiter ändern

Dieser Beitrag ist ein Teil der VBA Grundlagen Serie.
Hallo, in diesem Beitrag färbe ich alle Reiter, in denen der Text „Kosten“ vorkommt, rot.
Ausgangstabelle
Wechseln Sie zunächst in die Entwicklungsumgebung (ALT+F11) und fügen Sie ein neues Modul mit dem folgenden Makro ein.
Als erstes deklarieren wir eine Variable vom Typ Worksheet. Dann durchlaufen wir mit einer For Each / Next Schleife alle Tabellenbläter (Reiter) und überprüfen den Namen auf die Zeichenkette „Kosten“. Dazu benutzen wir die Funktion InStr. Wenn der Text „Kosten“ im Tabellennamen vorkommt, liefert uns diese Funktion einen Rückgabewert mit der Position, an der Übereinstimmung beginnt. Falls nichts gefunden wurde, wird 0 ausgegeben.
Wenn es sich um eine Kostentabelle handelt, setzen wir die Farbe über ColorIndex auf rot.
In VBA übersetzt, sieht unser Makro wie folgt aus:

Sub FarbeReiter()
Dim Blatt As Worksheet

For Each Blatt In ActiveWorkbook.Worksheets

If InStr(Blatt.Name, "Kosten") > 0 Then
Blatt.Tab.ColorIndex = 3
End If

Next Blatt
End Sub

Führen Sie nun Ihr Makro mit F5 aus und überprüfen Sie das Ergebnis.
Reiterfarbe über VBA geändert
Die Beispiel Excel Datei mit dem sofort einsetzbaren Makro und 111+ andere Makros, die Ihr Leben leichter machen, finden Sie hier.

Beeindrucke deinen Chef mit coolen Excel Tricks!

  • Lasse dich von deinen Kollegen nicht abhängen!
  • Spare Zeit und nutze diese für angenehmere Dinge
  • Steigere deine Produktivität und Effizienz
  • Es ist leicht - In meinem Newsletter gebe ich Schritt für Schritt Anleitungen die jeder versteht
  • Bei der Anmeldung zu meinem Newsletter erhälst du das Buch „Excel Tricks die deinen Chef beeindrucken“ als Willkommensgeschenk.
20 Excel Tricks

Jetzt anfordern!

* Pflichtfeld
 
Kein SPAM. 100% sicher.

Quellen und weiterführende Literatur:
Bernd Held – VBA mit Excel: Das umfassende Handbuch

Falls Ihnen dieser Beitrag weitergeholfen hat, wäre es eine sehr nette Anerkennung meiner Arbeit wenn Sie z.B. Ihre Bücher über Amazon bestellen würden. Wenn Sie ein Produkt kaufen, erhalte ich dafür eine Provision. Für Sie ändert sich am Preis des Produktes gar nichts. Ich möchte mich an dieser Stelle jetzt schon für Ihre Unterstützung bedanken.

Dieser Beitrag ist ein Teil eines Kurses, der Ihnen einen Schnelleinstieg in VBA ermöglichen soll und ist nach der folgenden Struktur aufgebaut:


Grundlagen:
Einführung in die objektorientierte Programmierung
Objekte, Methoden, Eigenschaften, Ereignisse und der Objektkatalog
Entwicklertools einblenden
Erstes Makro anlegen

Erste Makros:
Das aktuelle Datum in eine Zelle eintragen
Mehrzeiliges Meldungsfenster
Eine Tabelle nach einer Rückfrage löschen
Eine Eingabemaske erstellen

Zellen:
Bereich auslesen
Definierten Bereich füllen und leeren
Zellen im Bereich summieren
Bedingte Summation – Alle Werte größer als 100 summieren
Letzte verwendete Zeile bestimmen
Letzte verwendete Spalte bestimmen
Kommentare hinzufügen

Bedingungen:
Prüfen ob der Inhalt einer Zelle numerisch ist
Prüfen ob die Zelle leer ist
Zelle auf ein gültiges Datum prüfen
Sprachabhängige Meldungen ausgeben (SELECT CASE)

Schleifen:
Alle verwendete Zeilen bearbeiten
Wochenenden hervorheben
Tabellen über eine Schleife ausblenden
Leere Spalten verstecken
Alle Zellen in dem markierten Bereich bearbeiten (Großschreibung / Kleinschreibung)
Mehrere Bereiche vereinen und alle Zellen formatieren
Alle Tabellen schützen

Zeilen:
Jede zweite Zeile einfärben
Zeilenhöhe festlegen
Gesamte Zeile summieren
Leere Zeilen löschen
Zeilen ausblenden
Mehrmals vorkommende Einträge filtern (Unikatsliste erstellen)
Bestimmte Zeilen in eine andere Tabelle kopieren
Leere Zeilen einfügen
Eigene Suchfunktion bauen – bestimmte Zeile finden mit Kundennr. und Namen. Eigenschaft .Row der Variable Treffer

Spalten:
Daten filtern
Spaltenbereite anpassen
Spalte summieren
Spalten ausblenden
Leere Spalten löschen
Spalten formatieren
Spalten in eine andere Tabelle kopieren
Neue Spalten einfügen
Bestimmte Spalte finden

Tabellen:
Tabellennamen auslesen
Neue Tabellen einfügen und benennen
Tabellen löschen
Formeln entfernen
Druckbereich bestimmen
Farbe der Tabellenreiter ändern
Tabelle kopieren
Prüfung Tabellen- oder Diagrammblatt
Eigene Kopf-und Fußzeilen erstellen

Arbeitsmappen:
Arbeitsmappe erzeugen
Arbeitsmappe speichern
Verknüpfungen entfernen
Dokumenteigenschaften auslesen und verändern
Persönliche Informationen aus Dateieigenschaften löschen
Daten und Informationen über Dokumentprüfung entfernen
Arbeitsmappe löschen

Applikation:
Neuberechnung ausschalten
Excel Funktionen ein- und ausschalten
Ansichtsoptionen über Makro steuern
Excel-Version auslesen
Umgebungsvariablen auslesen

Mappen-Ereignisse:
Scrollarea festlegen
Das Schließen der Mappe verhindern
Druckvorgang abfangen
Auswahl bestimmter Tabellen unterbinden
Neue Tabellen sofort löschen
Tastenkombination definieren

Tabellen-Ereignisse:
Bereichsauswahl verhindern
Geänderte Zellen markieren
Eingaben konvertieren (z.B. Großbuchstaben)
Änderungen in einem Change Log dokumentieren
Eingaben sofort rückgängig machen
Tabellenverschiebung verhindern
Eine Zeile per Doppelklick einfärben
Rechten Mausklick deaktivieren

Standardfunktionen:
Nach einer Zeichenkette suchen – InStr
Länge mit Left und Len messen und korrigieren
Pfad und Dateinamen mit Funktionen InStrRev, Left und Mid trennen
Zeichen ersetzen mit Replace
Leerzeichen mit Trim entfernen
Zeichenkette aufteilen und zusammenfügen mit Split und Join
Mit DatePart Quartal und Kalenderwoche aus einem Datum ableiten
Differenz von zwei Datumswerten mit DateDiff berechnen
Text mit CDate in Datum umwandeln

Benutzerdefinierte Funktionen
Nur sichtbare Zellen summieren
Fett formatierte Werte zählen
Letzten Wert in der Zeile ausgeben
Eine eigene AutoText Funktion erstellen
Formeltext in Zelle ausgeben
Sonderzeichen entfernen
Prüfen, ob ein Bereich leer ist
Auf Ablaufdatum prüfen

Modul-Funktionen
Tabellenexistenz prüfen
Existenz der Arbeitsmappe prüfen
Prüfen ob eine Mappe geöffnet ist
Letzten Monatstag ermitteln
Alle Leerzeichen entfernen
Alphanumerische Zeichen löschen
Tabellenschutz prüfen
Spalten auf Inhalte prüfen
Quersumme berechnen

Denis Reis ist Business Intelligence Consultant und gibt als Buchautor sein Wissen rund um den SAP Projektalltag weiter. Wenn Sie tatkräftige Unterstützung bei Ihren SAP BI Projekten benötigen, können Sie ihn über Xing, LinkedIn oder Facebook kontaktieren.
Des Weiteren unterrichtet er Projektmanagement und Controlling an der Wiesbaden Business School. Der aus Düsseldorf stammende Familienmensch zählt zu denjenigen, die auf komplizierte Darstellungen verzichten und das Ganze auf den Punkt bringen.

How To BPC – Sprachabhängige Workbooks über VBA mit API Aufruf

Planungsmappen in BPC werden hauptsächlich auf Basis von Excel umgesetzt. Das heißt, dass wir unsere Überschriften, Reiternamen, Buttons usw. in Excel anlegen. Oft stehen wir vor der Herausforderung die Layouts zu lokalisieren, das heißt in verschiedenen Sprachen verfügbar zu machen. Wie stellen wir es am Besten an?
Eine Alternative wäre es Überschriften über eine Wenn/Dann Formel zu füllen indem man sich auf die Merkmalsbeschreibung bezieht die in verschiedenen Sprachen unterschiedliche Werte annimmt. Zum Beispiel Umsatz/Revenue. Allerdings können wir auf diese Weise keine Buttons oder Reiter umbenennen.
Die zweite Alternative wäre es, über VBA die Spracheinstellungen von Excel auszulesen und über SELECT CASE die Beschreibung der Elemente dementsprechend zu ändern. Das grundsätzliche Prinzip habe ich bereits im Beitrag „Sprachabhängige Meldungen ausgeben“ dargestellt.
Betrachten wir das folgende Planungslayout.

Planungslayout in BPC Excel

Planungslayout

In dem folgenden Coding lese ich nun beim Aufruf des Workbooks die Spracheinstellungen von Excel aus und ändere so die Beschriftung.

Function AFTER_WORKBOOK_OPEN()
AFTER_WORKBOOK_OPEN = True
'Lokalisierung
Select Case Application.LanguageSettings.LanguageID(msoLanguageIDUI)
'Deutsch
Case 1031, 3079, 5127, 4103

With Aggregated 'Worksheet Aggregated Planning
.Name = "Aggregierte Planung"
.Cells(4, 1).Value = "Absatz- und Umsatzplanung: Strategische Vorgaben"
.CommandButton1.Caption = "Umsatz berechnen"
.Cells(12, 2).Value = "Buchungskreis"
.Cells(12, 3).Value = "Verkaufsorganisation"
End With

'Englisch
Case Else
With Aggregated 'Worksheet Aggregated Planning
.Name = "Aggregated Planning"
.Cells(4, 1).Value = "Sales Planning: Strategical Guidlines"
.CommandButton1.Caption = "Calculate Revenues"
.Cells(12, 2).Value = "Company Code"
.Cells(12, 3).Value = "Sales Org."
End With

End Select
End Function

Was ist aber wenn Sie die Beschriftung nicht von der Excel-, sondern von der BPC-Einstellung abhängig machen wollen?

Benutzeroptionen BPC

BPC Spracheinstellungen

In diesem Fall können Sie auf die von SAP bereitgestellten APIs zurückgreifen. Zunächst müssen Sie diese aktivieren. Gehen Sie dazu in der Entwicklungsumgebung (ALT+F11) auf Extras → Verweise (engl. Tools → References) und wählen Sie FPMXLClient aus.

VBA Referenzen aktivieren

VBA Verweise

Nun können Sie alle Objekte im Objektkatalog (F2) sehen. Wählen Sie in der Dropdown-Liste FPMXLClient aus.
Die Klasse EPMAddInAutomation enthält alle Makros, die für das EPM-AddIn ausgeführt werden können. Beachten Sie, dass Sie diese nicht mit der Funktion EPMExecuteAPI verwenden können.
Die Klasse IEPMExecuteAPI enthält Makros, die direkt aus einer Arbeitsblattzelle über die Funktion EPMExecuteAPI ausgeführt werden können. Beispiele für diese Funktion finden Sie in den Beiträgen Script Logic über Excel Button ausführen und Automatische Aktualisierung der Inhalte nach dem Reiterwechsel.
Uns interessiert die Methode GetUserOption. Mit dem Parameter LanguageIsoCode können wir die Anwendungssprache und mit dem Parameter LanguageEdition die Sprache der Daten auslesen. Diese Methode liefert uns eine Zeichenfolge (String) im ISO 639-1 Format. So steht zum Beispiel de für Deutsch. Weitere Parameter für dieses API können Sie dem Benutzerhandbuch für das EPM-Add-In entnehmen.
Sie können dieses API wie folgt aufrufen. Beachten Sie, dassLanguageIsoCode in Gänsefüßchen steht, da es ein Parameter und keine Variable ist.

Sub Sprache()
Dim language As String
Dim EPMObj As FPMXLClient.EPMAddInAutomation
Set EPMObj = New FPMXLClient.EPMAddInAutomation

language = EPMObj.GetUserOption("LanguageIsoCode")
Debug.Print language

End Sub

Unser Makro für die Lokalisierung würde nun wie folgt aussehen.

Function AFTER_WORKBOOK_OPEN()
AFTER_WORKBOOK_OPEN = True
'Lokalisierung
Dim language As String
Dim EPMObj As FPMXLClient.EPMAddInAutomation
Set EPMObj = New FPMXLClient.EPMAddInAutomation

language = EPMObj.GetUserOption("LanguageIsoCode")
Select Case language
'Deutsch
Case "de"

With Aggregated 'Worksheet Aggregated Planning
.Name = "Aggregierte Planung"
.Cells(4, 1).Value = "Absatz- und Umsatzplanung: Strategische Vorgaben"
.CommandButton1.Caption = "Umsatz berechnen"
.Cells(12, 2).Value = "Buchungskreis"
.Cells(12, 3).Value = "Verkaufsorganisation"
End With

'Englisch
Case Else
With Aggregated 'Worksheet Aggregated Planning
.Name = "Aggregated Planning"
.Cells(4, 1).Value = "Sales Planning: Strategical Guidlines"
.CommandButton1.Caption = "Calculate Revenues"
.Cells(12, 2).Value = "Company Code"
.Cells(12, 3).Value = "Sales Org."
End With

End Select
End Function

Falls Ihnen dieser Beitrag weitergeholfen hat, wäre es eine sehr nette Anerkennung meiner Arbeit wenn Sie z.B. Ihre Bücher über Amazon bestellen würden. Wenn Sie ein Produkt kaufen, erhalte ich dafür eine Provision. Für Sie ändert sich am Preis des Produktes gar nichts. Ich möchte mich an dieser Stelle jetzt schon für Ihre Unterstützung bedanken.

Denis Reis ist Business Intelligence Consultant und gibt als Buchautor sein Wissen rund um den SAP Projektalltag weiter. Wenn Sie tatkräftige Unterstützung bei Ihren SAP BI Projekten benötigen, können Sie ihn über Xing, LinkedIn oder Facebook kontaktieren.
Des Weiteren unterrichtet er Projektmanagement und Controlling an der Wiesbaden Business School. Der aus Düsseldorf stammende Familienmensch zählt zu denjenigen, die auf komplizierte Darstellungen verzichten und das Ganze auf den Punkt bringen.