Archiv

Artikel Tagged ‘Excel’

Excel – Blattschutz aufheben bei unbekanntem Passwort

3. Januar 2013 23 Kommentare

Grade in die Situation gekommen, ein altes Makro sezieren zu müssen. Bei der Gelegenheit ist mir aufgefallen, dass ich in der Datei vor ca. 4 Jahren einen Blattschutz mit Passwort eingerichtet habe. Und wie es der Zufall so will, viel mir selbiges natürlich auch nicht mehr ein.

Also musste schnell eine Lösung her, und die lieferte mir Google überraschend schnell. Ich hätte gedacht, dass die Schutzmechanismen von Office etwas ausgeklügelter wären, aber man kann den Blattschutz mit einem kleinen Makro sehr leicht und relativ schnell aushebeln.

Eine Videoanleitung dazu habe ich bei Youtube gefunden und spare mir daher an dieser Stelle die weiteren schriftlichen Ausführungen und verweise einfach auf das Tutorial.

 
Und hier noch der benötigte Makro-Code, einfach als Modul in dem Dokument speichern, dessen Blattschutz man entfernen möchte, und anschließend auf dem Blatt ausführen.

Sub Blattschutz_Entfernen()
  On Error Resume Next
  For i = 65 To 66
   For j = 65 To 66
    For k = 65 To 66
     For l = 65 To 66
      For m = 65 To 66
       For n = 65 To 66
        For o = 65 To 66
         For p = 65 To 66
          For q = 65 To 66
           For r = 65 To 66
            For s = 65 To 66
             For t = 32 To 126

ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & Chr(l) & Chr(m) & _
Chr(n) & Chr(o) & Chr(p) & Chr(q) & Chr(r) & Chr(s) & Chr(t)

             Next t
            Next s
           Next r
          Next q
         Next p
        Next o
       Next n
      Next m
     Next l
    Next k
   Next j
  Next i
  MsgBox "Der Blattschutz sollte jetzt deaktiviert sein."
End Sub

Funktioniert mit Excel 2003 ganz hervorragend, nach knappen 2 Minuten konnte ich das Dokument wieder vollständig nutzen und bearbeiten. Laut Video soll das Makro auch in Excel 2007 nutzbar sein.

Excel – Inhalte als Plain Text einfügen

17. August 2012 Keine Kommentare

Wer oft mit Excel arbeitet, dürfte das folgende Problem nur zu gut kennen. Man kopiert Daten aus der einen Tabelle, fügt sie in der nächsten ein und siehe da, die Formatierung ist hinüber. Also entweder manuell nachbessern oder aber Bearbeiten => Inhalte einfügen… verwenden.

Dieses Fenster erscheint, wenn man innerhalb von Excel Daten kopiert. Bei mir kommt das täglich beruflich vor. Und da es sehr nervig sein kann, sich jedes mal durch das Menü zu klicken, hab ich mir die Funktion Werte einfügen auf die Symbolleiste gezogen.

Mit dieser kleinen Anpassung kann man schon ein gutes Stück schneller arbeiten. Aber was tun, wenn Daten von einer externen Anwendung in Excel eingefügt werden sollten, beispielsweise ein Link aus dem Browser. Werte einfügen funktioniert in diesem Fall nicht. Also erstmal wieder Bearbeiten => Inhalte einfügen…

Excel blendet ein völlig anderes Menü auf, sobald die Zwischenablage Daten enthält, die nicht aus einer Excel-Tabelle stammen. Leider gibt es für diesen Fall keine vorgefertigte Funktion, die man sich einfach so auf die Symbolleiste legen kann. Aber hier kann man mit einem Makro Abhilfe schaffen. Und da man diese Funktion ständig brauchen kann, wird sie auch gleich gloabl verfügbar gemacht.

Es wird also in der PERSONAL.XLS ein Modul angelegt und darin folgender Makro-Code gespeichert:

Sub AlsTextEinfuegen()
   ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
End Sub

Das war es im Grunde auch schon. Das Makro macht genau das selbe, was man vorher manuell erledigt hat. Es fügt beliebige Inhalte aus der Zwischenablage als reinen Text ein. Jetzt kann man dem Makro wahlweise eine Tastenkombination zuweisen oder es auf die Symbolleiste legen. Ich habe letzteres gemacht.

Das Symbol hab ich gleich neben Werte einfügen abgelegt, somit sind beide immer schnell zu erreichen. Solange der Anpassen-Modus noch aktiv ist, kann mit einem Rechtsklick auf das Symbol selbiges geändert werden und dem Symbol auch ein Makro zugewiesen werden.

Jetzt hat man 2 Buttons in der Symbolleiste, mit deren Hilfe man deutlich schneller und komfortabler Inhalte ohne Formatierung einfügen kann.

 
Btw… um in Word das gleiche Ergebnis zu erzielen, muss folgender Makro-Code genutzt werden:

Sub AlsTextEinfuegen()
    Selection.PasteAndFormat (wdFormatPlainText)
End Sub

Gespeichert wird das Makro in der Normal.dot, was der PERSONAL.XLS entspricht. Angelegt wird diese nach dem selben Schema, erstmal ein Dummy-Makro erstellen und dann wie oben beschrieben fortfahren.

PERSONAL.XLS(B) – Die Excel Arbeitsmappes für globale Makros

17. August 2012 4 Kommentare

Makros können einem das tägliche Arbeiten mit Excel ein gutes Stück vereinfachen. Einige Makros sind anwendungsspezifisch und daher nur in einer bestimmten Datei nutzbar, andere hingegen sind universell einsetzbar. Da liegt es Nahe, diese auch global für alle geöffneten Dateien verfügbar zu machen, ohne den Code in jede Datei kopieren zu müssen oder immer eine zusätzliche Datei geöffnet zu haben, die man ansonsten für die Arbeit gar nicht benötigt.

Für genau diesen Fall gibt es die PERSONAL.XLS (ab Office 2007 PERSONAL.XLSB). Sie wird automatisch im Hintergrund geladen, wenn Excel geöffnet wird. Somit gibt es zwar kein Fenster für diese Datei, durch das man auf die Arbeitsblätter zugreifen könnte, aber die Makros sind dennoch jederzeit aufrufbar.

Hinweis: Alle nachfolgenden Erläuterungen beziehen sich auf Office 2003!

 
Anlegen der PERSONAL.XLS

Die Datei sollte durch Excel angelegt werden. Hierzu wird einfach ein beliebiges Dummy-Makro aufgezeichnet. Extras => Makro => Aufzeichnen…

Im Feld Makro speichern in: muss Persönliche Makroarbeitsmappe ausgewählt sein.

Anschließend eine beliebige Aktion aufzeichnen, beispielsweise das Einfärben einer Spalte und die Aufzeichnung beenden. Nun Excel beenden und die Sicherheitsabfrage mit Ja bestätigen.

Fertig. Nachdem Excel erneut geöffnet wurde, ist das Dummy-Makro jederzeit ausführbar.

 
Und nun ein sinnvolles Makro erstellen

Je komplexer ein Makro sein soll, umso geringer die Wahrscheinlichkeit, dass es sich mit dem Rekorder erstellen lässt. Also öffnet man mit ALT + F11 den VBA-Editor. In der linken Baumstruktur befindet sich ein Eintrag für die Datei PERSONAL.XLS. Dieser am besten als erstes einen ordentlichen Namen geben: Rechte Maustaste => Eigenschaften von … und dann unter Projektname die Beschriftung ändern.

Da bereits ein Dummy-Makro erzeugt wurde, hat Excel ein Modul angelegt, welches den Code des Makros enthält. Diesen kann man nun mit beliebigem eigenen Code ersetzen.

 
Speicherort herausfinden

Je nach verwendeter Office Version und Systemkonfiguration kann sich der Speicherort der PERSONAL.XLS unterscheiden. Es gibt aber einen schnellen Weg, dennoch an den Speicherpfad zu gelangen. Dazu öffnet man wieder den VBA-Editor.

  1. Excel öffnen
  2. Mit ALT + F11 den VBA Editor öffnen
  3. Mit STRG + G den Direktbereich öffnen
  4. Mit STRG + A den gesamten Inhalt des Direktbereich markieren
  5. ?Application.StartupPath eingeben
  6. Mit ENTER bestätigen

Jetzt bekommt man den Pfad der PERSONAL.XLS angezeigt. In meinem Fall ist das

D:\Dokumente und Einstellungen\Schmidt.M\Anwendungsdaten\Microsoft\Excel\XLSTART

oder kurz und universell nutzbar

%APPDATA%\Microsoft\Excel\XLSTART

Mit Excel VBA Makro Anwendungen fernsteuern und Eingaben automatisieren

5. Juni 2012 Keine Kommentare

Kaum hat man die Überschrift gelesen, kommt einem schon der Gedanke: Warum sollte ich das tun? Es gibt unzählige Programme im Netz, mit denen man Vorgänge automatisieren und andere Programme fernsteuern kann. Bekannter Vertreter dieser Gattung ist AutoIt, aber auch die Suchbegriffe Maus + Tastatur + Makro fördern einiges zu Tage.

Was aber tun, wenn man keins davon zur Verfügung hat. AutoIt verlangt Admin-Rechte für die Installation, und nicht jeder Arbeitgeber steht der Installation oder auch nur der Ausführung fremder Software auf den Firmenrechnern besonders offen gegenüber. Weniger hilfreich sind diese Programme auch dann, wenn Informationen aus einer Excel-Tabelle verarbeitet werden sollen. Und genau an dieser Stelle werden Excel Makros dann schnell wieder interessant, möchte man sich trotz Einschränkungen dennoch die Arbeit erleichtern. Denn die Berechtigungen und Möglichkeiten von VBA sind schon recht beeindruckend und ein etwas genauerer Blick darauf lässt auch erahnen, warum man unbedingt die Ausführung von Makros in Dokumenten unbekannter Herkunft verhindern sollte.

Im folgenden werde ich daher mal ein paar Module vorstellen, mit denen man zumindest ähnliche Ergebnisse wie mit AutoIt erreichen kann.
 


Index

    Vorbereitungen
1. Einfache Warteschleife
2. Programme öffnen
3. Wechseln des aktiven Fensters
4. Tastatureingaben mit VBA
5. Zwischenablage nutzen
6. Den Mauszeiger positionieren
7. Mausklicks ausführen
    Abschließende Worte & Download
 


Vorbereitungen

Eines vor weg… ich verwende noch Office 2003. Ob die vorgestellten Makros auch mit neueren Versionen von Office funktionieren, kann ich nicht beurteilen. Ich komme mit dieser Version gut zurecht und auch mein AG sieht scheinbar keinen Grund für einen Wechsel ^^

Zuerst öffnet man in Excel eine neue Datei. Mit ALT+F11 oder alternativ über Extras => Makro => Visual Basic Editor wechselt man anschließend in selbigen. Um das ganze später übersichtlich zu halten, verwende ich für die einzelnen Programmmodule VBA Module.


1. Einfache Warteschleife

Das erste Modul ist für Eingabeverzögerungen nützlich. Nicht jedes Programm kommt damit zurecht, wenn es sämtliche Steuerungsbefehle innerhalb Sekundenbruchteilen verarbeiten soll. Daher hier eine schlicht gehaltene Warteschleife.

Public Sub wait(ByVal zeit As Long)
  i = Timer
  Do
  Loop While Timer <= i + zeit
End Sub

Der Übergabewert entspricht Sekunden, die gewartet werden soll. Der Aufruf

Call wait.wait(2)

sorgt für eine Verzögerung von 2 Sekunden.

Der Code-Schnipsel stammt aus einem Makro, welches ich vor Jahren mal zur Massenbeauftragung in einer firmeneigenen Anwendung erstellt habe. Es gibt aber auch elegantere Ansätze, wie ich grad gelesen habe:

Application.Wait (Now + TimeValue("00:00:02"))

Selbes Ergebnis, besserer Code 🙂


2. Programme öffnen

Geht wunderbar mit VBA, dazu ist nur ein kleiner Befehl notwendig. Hier am Beispiel vom Windows-Rechner:

Shell "calc"

Schon öffnet sich der Rechner und ist bereit für Eingaben. Man kann hier natürlich auch absolute Pfadangaben eintragen und so beliebige Programme ausführen.


3. Wechseln des aktiven Fensters

Entweder verlässt man sich auf ALT+TAB oder man nutzt eine VBA-Funktion, um ein anderes Fenster anzusteuern. Man benötigt dazu nur einen Befehl und den Titel des Fensters. Ein geöffneter Rechner heißt auch Rechner.

AppActivate "Rechner"

Nachdem der Code ausgeführt wurde, ist der Rechner das aktive Fenster. Das Makro arbeitet im Hintergrund weiter, alle Eingaben erfolgen nun aber im Rechner.

Dies funktioniert aber nur bei Fenstern, die nicht minimiert sind. Im Gegensatz zu ALT+TAB ist AppActivate nicht in der Lage, ein minimiertes Fenster wieder aus der Taskleiste zu holen. Liegt es nur im Hintergrund, ist es aber kein Problem.


4. Tastatureingaben mit VBA

Drücke Taste x, bestätige mit Enter.

SendKeys "{x}", True
SendKeys "{ENTER}", True

Via Sendkeys man fast alle Tasten der Tastatur senden. Aber leider auch nur fast alle.
Eine Liste aller unterstützten Befehle findet sich in der MSDN Library.


5. Zwischenablage nutzen

Liegen Daten in Excel vor, die vom Makro verarbeitet und in einer anderen Anwendung eingefügt werden sollen, macht es wenig Sinn, diese Informationen einzeln via SendKeys zu übergeben. Die Nutzung der Zwischenablage bietet sich hier viel eher an.

Public Sub Clipboard(text)
  Dim oData As New DataObject
  oData.SetText text
  oData.PutInClipboard
End Sub

Um diese Funktion nutzen zu können, muss zuvor noch ein Verweis auf die Microsoft Forms 2.0 Object Library gesetzt werden. Dazu geht man im VB-Editor auf Extras => Verweise und setzt dort einen Haken bei dem entsprechenden Eintrag. Sollte der Eintrag fehlen, kann man ihn über Durchsuchen.. manuell hinzufügen. Die Bibliothek befindet sich unter %SystemRoot%\system32\FM20.DLL. Anschließend Speichern nicht vergessen.

Falls einem das zu umständlich erscheint, reicht es auch, in der Excel-Tabelle über die Steuerelement-Toolbox einen Button oder ähnliches einzufügen. Dadurch wird die Bibliothek ebenfalls eingebunden.

Am Beispiel eines bereits geöffneten Notepads:

  AppActivate "Editor"
  Call Clipboard.Clipboard("Hier kann beliebieger Text oder ne Variable rein")
  SendKeys "^{v}", True

Fertig. Der Text sollte nun im Editor stehen.

Grade bei VB-Paradise gesehen, dass das auch auf umgekehrten Wege geht:

Public Function HoleTextVonZwischenablage() As String
  Dim oData As New DataObject
  On Error Resume Next
  oData.GetFromClipboard
  HoleTextVonZwischenablage = oData.GetText
End Function

So kann man auch aus anderen Anwendungen kopierte Informationen direkt mit VBA verarbeiten.


6. Den Mauszeiger positionieren

Diesen Code habe ich vor Jahren mal zusammengesucht. Ursprünglich für ein VB-Projekt gedacht, war er mit leichten Modifikationen auch mit VBA in Excel nutzbar.

Private Declare Function SetCursorPos Lib "user32" (ByVal x As Long, _
  ByVal y As Long) As Long

Private Type POINTAPI
    x As Long
    y As Long
End Type

Public Sub MoveMouse(x As Long, y As Long)
    Dim udtP As POINTAPI
    udtP.x = CLng(x)
    udtP.y = CLng(y)
    SetCursorPos udtP.x, udtP.y
End Sub

Mit Hilfe dieser Funktion lässt sich der Mauszeiger frei auf dem Bildschirm positionieren. Die Koordinaten beginnen in der linken, oberen Ecke und werden in Pixel angegeben. Die Positionierung ist somit abhängig von der genutzten Auflösung und nicht beliebig auf andere Rechner übertragbar.


7. Mausklicks ausführen

Wie die Positionierung der Maus stammt auch die Funktion für Klicks eigentlich aus einem VP-Projekt. Hier waren ebenfalls leichte Modifikationen fällig, bevor sie die gewünschten Ergebnisse lieferte.

Private Declare Sub mouse_event Lib "user32" (ByVal dwFlags As Long, _
  ByVal dx As Long, ByVal dy As Long, ByVal cButtons As Long, _
  ByVal dwExtraInfo As Long)

Private Const MOUSE_LEFT = 0
Private Const MOUSE_MIDDLE = 1
Private Const MOUSE_RIGHT = 2

Public Sub SendClick(ByVal mButton As Long)
  Const MOUSEEVENTF_LEFTDOWN = &H2
  Const MOUSEEVENTF_LEFTUP = &H4
  Const MOUSEEVENTF_MIDDLEDOWN = &H20
  Const MOUSEEVENTF_MIDDLEUP = &H40
  Const MOUSEEVENTF_RIGHTDOWN = &H8
  Const MOUSEEVENTF_RIGHTUP = &H10

  If (mButton = MOUSE_LEFT) Then
    Call mouse_event(MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0)
    Call mouse_event(MOUSEEVENTF_LEFTUP, 0, 0, 0, 0)
  ElseIf (mButton = MOUSE_MIDDLE) Then
    Call mouse_event(MOUSEEVENTF_MIDDLEDOWN, 0, 0, 0, 0)
    Call mouse_event(MOUSEEVENTF_MIDDLEUP, 0, 0, 0, 0)
  Else
    Call mouse_event(MOUSEEVENTF_RIGHTDOWN, 0, 0, 0, 0)
    Call mouse_event(MOUSEEVENTF_RIGHTUP, 0, 0, 0, 0)
  End If
End Sub

In diesem Code werden 3 Möglichkeiten genutzt: Einfacher Klick mit linker, rechter und mittlerer Maustaste. Einen Doppelklick erreicht man durch doppeltes Aufrufen der Funktion, muss daher nicht unbedingt berücksichtigt werden. Wer eine gedrückte Taste simulieren möchte, beispielsweise für Markierungen oder Selektierung von Elementen, muss sich die Funktion entsprechend anpassen.


Abschließende Worte & Download

Man kann von Excel halten, was man will, die Möglichkeiten dieses Programms sind schon beeindruckend. Ich hoffe, diese Tipps helfen dem einen oder anderen weiter und erleichtern die tägliche Arbeit ein wenig.

Hier nochmal eine einfache Beispiel-Datei, damit jeder das bisher vorgestellte auch nachvollziehen und nutzen kann.