| Der Austausch von Daten mit anderen Programmen ist ein besonders wichtiges Kapitel in VBA. Die Beherrschung dieser Technik ermöglicht es, die Verwendung Hersteller-gebundener Programme auf ein vernünftiges Minimum einzuschränken. | Zentrale Bestandteile von Lösungen laufen daher auf Offenen Systemen und OpenSource-Programmen (z.B. Linux, Apache, PHP, Perl, MySQL, ...) Alle anderen Programme werden über Import / Export von Daten eingebunden. |
VBA
|
Visual Basic for Applications |
| Volumes | Volumes (Drives) - Festplatten, CD/DVD. Netzlaufwerke, ... |
| Dateien | Verzeichnisse (Ordner, folder) und Dateien (files) |
| Pfade | Pfade zu Verzeichnissen und Dateien |
| DateisystemObjekt | Volume, Verzeichnis, Datei existiert ?, Eigenschaften ? |
| Existenz | Existiert ein Volume, Verzeichnis, eine Datei ? |
| Import: Text | Dateien lesen: Text, XML, ... |
| Export: Text | Dateien schreiben: Text, Grafik, XML, ... |
| Export: XML | XML-Dateien für die Weiterverarbeitung exportieren |
| Export: GIF | Grafik-Dateien für die Weiterverarbeitung exportieren |
| Binär-Dateien | Lesen und Schreiben binärer Dateien |
| ActiveX | Zugriff auf das Dateisystem mit WSH / ActiveX |
| ODBC | Daten als Data Source Name (DSN) anbieten |
| Links |
Ausgewählte
|
Verzeichnisse (Ordner, folder) & Dateien (files) |
|
|
■
Verzeichnisse und Dateien werden im Dateisystem ähnlich behandelt und sind
daher meist mit gleichen oder ähnlichen Funktionen zugänglich. |
■ Ein Sonderfall sind komprimierte → XML-Dateien: Immer mehr Programme (OpenOffice, neues Versionen von M$Office, ...) gehen dazu über, ihre Daten in XML-Formaten zu verwalten: Diese Dateien enthalten nur einfachen Text und sind deshalb besonders einfach zu verwalten. Der große Platzbedarf von Text-Dateien wird durch Kompression (Zip-Verfahren) stark reduziert. Dabei werden zwar Binär-Dateien erzeugt, diese sind jedoch einheitlich aufgebaut und können deshalb von jedem Programm rasch ausgepackt und verwendet werden. |
Verzeichnis-Liste:Dieses Beispiel zeigt, wie man eine Liste aller Sub-Verzeichnisse und Dateien eines Verzeichnisses erhält.Dir() wird zunächst mit dem Pfad (hier mypath ) als Argument aufgerufen und liefert den ersten Dateinamen (hier: thename ) In der Do-While-Loop-Schleife wird die aktuelle Datei ( thename ) bearbeitet und zuletzt mit erneutem Aufruf von Dir() der nächste Dateiname ausgewählt. Bearbeitung: Das aktuelle Verzeichnis (".") und das übergeordnete Verzeichnis ("..") werden ignoriert. Alle anderen Einträge werden in MsgBoxen ausgegeben. Das Attribut-Bit vbDirectory wird extra ausgewiesen → damit ist ein Subverzeichnis identifiziert: In diesem könnte das gleiche Programm (rekursiver Aufruf) weiterarbeiten. |
Sub File_List()
Dim mypath, thename, msg, a
mypath = "c:\"
End Sub
thename = Dir(mypath, vbDirectory) Do While thename <> ""
If thename <> "." And thename <> ".." Then
msg = thename
End Ifa = GetAttr(mypath & thename) msg = msg & Chr(13) & "GetAttr=" & a If (a And vbDirectory) Then msg = msg & Chr(13) & "Dir"
End IfMsgBox (msg) thename = Dir ' Nächster Eintrag Loop |
Datei-Attribute:Die hier vorgestellte Function dient als einfacher Test, ob eine Datei oder ein Verzeichnis vorliegt.Sie gibt -1 zurück, wenn das gesuchte Objekt nicht existiert. Ansonsten werden die Datei-Attribute zurückgegeben: Werte der Datei-Attribute:
00 ... vbNormal
01 ... vbReadOnly 02 ... vbHidden 04 ... vbsystem 16 ... vbDirectory 32 ... vbArchive 64 ... vbAlias |
Function FileAttGet(ByVal Filename As String) As Integer
Dim i As Integer
Err.Clear
myerr:
On Error GoTo myerr FileAttGet = GetAttr(Filename) Exit Function FileAttGet = -1
End Function
|
Datei-Länge:Dieses Beispiel gibt die Länge einer Datei in Byte zurück, oder -1, wenn die Datei nicht existiert. |
Function FileLengthGet(ByVal Filename As String) As Integer
If (FileAttGet(Filename) >= 0) Then
End Function
FileLengthGet = FileLen(Filename)
Else
FileLengthGet = -1
End If
|
Datei-Datum & Zeit:Dieses Beispiel gibt Datum und Uhrzeit der letzten Änderung zurück, oder -1, wenn die Datei nicht existiert.Hinweis: Falls sie das Ergebnis in Excel verwenden, müssen sie die Zelle als Datum / Zeit formatieren ! |
Function FileDaTimGet(ByVal Filename As String) As Variant
' Returns date of latest change - if used in Excel, format as date & time !!!
If (FileAttGet(Filename) >= 0) Then
FileDaTimGet = FileDateTime(Filename)
Else
FileDaTimGet = -1
End If
|
Neues Verzeichnis:Dieser Befehl erzeugt ein neues Verzeichnis |
MkDir "test123" |
Dateien löschen:Der Kill-Befehl löscht Dateien. Wenn die Datei nicht existiert, wird ein Fehler gemeldet - prüfen sie daher vor der Ausführung, ob die Datei existiert.Hier kann auch ein Joker "*" (wildcard) eingesetzt werden. - Vorsicht ! |
Kill "test.txt"
Kill "*.gif" |
Verzeichnis löschen:Befehl RmDir löscht ein Verzeichnis. Das ist erst möglich, wenn alle enthaltenen Verzeichnissse und Dateien gelöscht wurden ! |
RmDir "demo" |
Pfade |
|
|
►
Ein Absoluter Pfad enthält den kompletten "Weg"
zu einem Verzeichnis oder zu einer Datei: Volume (Drive - ... - Verzeichnis - ... - Datei Der Pfad kann dabei über zahlreiche Unter-Verzeichnisse laufen. ► Das Volume wird (derzeit, nur bei Win) mit einem Grossbuchstaben und ":" bezeichnet. Alle weiteren Elemente des Pfades werden (nur bei Win) durch das Separator-Zeichen "\" getrennt. Dateinamen bestehen (nur bei Win) aus dem eigentlichen Namen, einem Punkt und der Datei-Erweiterung (extension, meist 3 Zeichen). Achtung: Alle anderen gängigen Betriebssysteme, das Internet usw. verwenden "/" als Separator, und können wahlweise mit oder ohne Datei-Erweiterung arbeiten. ► Zu jedem Zeitpunkt ist ein "Aktueller Pfad" ausgewählt, meist zu der gerade bearbeiteten Datei. Dieser Pfad kann jedoch beliebig geändert werden. |
►
Ein Relativer Pfad führt ausgehend vom aktuellen Pfad zu
einem Verzeichnis oder zu einer Datei. Ein Aufstieg in der Verzeichnis-Hierarchie wird mit "..\" bezeichnet. Die Verwendung absoluter Pfade bei der Programmierung ist meist sicherer ! Dateinamen:Verwenden sie in den Namen von Verzeichnissen und Dateien keine Umlaute, Leerzeichen oder Sonderzeichen. Das ist wichtig, wenn sie mit Betriebssystemen oder menschlichen Partnern aus anderen Sprach-Bereichen zusammen arbeiten. Die Umstellung aller Namen auf den international einheitlichen Unicode ist zwar im Gange, jedoch noch lange nicht abgeschlossen. |
Aktuelles Verzeichnis:Diese Funktion gibt den absoluten Pfad zum aktuellen Arbeits-Verzeichnis (current working directory, cwd) zurück. Dieser kann jederzeit geändert werden und ist daher vom Pfad zur eigenen Datei unabhängig ! |
Function MyDir()
MyDir = CurDir
End Function
|
Der eigene Dateipfad:Dieses Sub zeigt den kompletten (absoluten) Pfad zum aktuellen Dokument an.Anschließend wird daraus der Pfad zum aktuellen Verzeichnis abgeleitet: Der String wird umgekehrt und nach dem ersten Auftreten des Trennzeichens "\" gesucht. |
Sub Path_Show()
Dim myname, mypath
myname = ActiveWorkbook.FullName
End Sub
MsgBox ("FullName=" & myname) mypath = Left(myname, InStrRev(myname, "\")) MsgBox ("Path=" & mypath) |
| Befehl ChDir ändert den Pfad zum aktuellen Arbeits-Verzeichnis. | ChDir "C:\test" |
| Befehl ChDrive wechselt den aktuellen Pfad auf Volume "D" | ChDrive "D" |
Informationen über Dateisystem-Objekte |
|
| Dateisystem-Objekte machen auf einfache Weise eine Reihe von Eigenschaften zugänglich. |
Eine Live-Demonstration finden sie auf der Seite
'Umgebungs-Daten
mit ActiveX'.
|
VolumeIn jedem der hier vorgestellten Beispiele wird ein 'FileSystemObject' erzeugt und seine Eigenschaften untersucht. Die Eigenschaft driveexists liefert die gewünschte Aussage vom Typ BooleanWeitere Eigenschaften sind verfügbar, wie im auskommentierten Teil angedeutet. |
Function drv_exist(drv As String)
Set fso = CreateObject("Scripting.FileSystemObject")
End Function
drv_exist = fso.driveexists(drv)
' Set d = fso.GetDrive(drv)
' x = d.isready ' x = d.driveletter ' x = d.DriveType ' x = d.ShareName ' x = d.VolumeName ' x = d.FileSystem ' x = d.SerialNumber ' x = d.TotalSize ' x = d.FreeSpace |
VerzeichnisDie Eigenschaft folderexists liefert die gewünschte Aussage vom Typ BooleanWeitere Eigenschaften sind verfügbar, wie im auskommentierten Teil angedeutet. Folders und Files sind Listen, die selbst (rekursiv) mit den gleichen Methoden untersucht werden können. |
Function dir_exist(dirpath As String)
Set fso = CreateObject("Scripting.FileSystemObject")
End Function
dir_exist = fso.folderexists(dirpath)
' set f = fso.GetFolder(dirpath)
' x = f.size ' x = f.DateCreated ' x = f.DateLastModified ' x = f.DateLastAccessed ' Set Folders = f.SubFolders ' x = Folders.Count ' For Each SubFolder In Folders
' s = Subfolder.Name
' Next' s = Subfolder.size ' . . . ' Set Files = f.Files ' x = Files.Count |
DateiDie Eigenschaft fileexists liefert die gewünschte Aussage vom Typ BooleanWeitere Eigenschaften sind verfügbar, wie im auskommentierten Teil angedeutet. |
Function file_exist(filepath As String)
Set fso = CreateObject("Scripting.FileSystemObject")
End Function
file_exist = fso.fileexists(filepath)
' set f = fso.GetFile(filepath)
' x = f.Name ' x = f.Type ' x = f.size ' x = f.DateCreated ' x = f.DateLastModified ' x = f.DateLastAccessed |
Export von XML-Dateien |
|
|
Neuere Versionen von Excel bieten zwar die Speicherung von Daten im
XML-Format, das Ergebnis ist jedoch selten
so wie gewünscht. Daher muss die XML-Datei meist mit einem eigenen Mini-Programm
erzeugt werden. ► Die XML-tags (z.B. <person></person> ) können im Kalkulationsblatt mit Hilfe der üblichen Text-Funktionen erstellt und anschließend als Text-Datei *.xml exportiert werden. ► Alle Zeichen außerhalb des ASCII-Bereichs müssen UTF-8 codiert werden (z.B. die Umlaute ÄÖÜäöü und das ß). |
►
Hier werden die tags mit VBA erzeugt: Für den Daten-Bereich A3:B8 (s.u.) wurde der Name "xml_data" definiert. (Bereich markieren, dann Menübefehl Einfügen | Namen | definieren) Die tags werden hier explizit eingesetzt, könnten jedoch auch aus den Tabellen-Überschriften ermittelt werden. |
|
►
Mit Cells(zeile,spalte) wird jeweils eine Datenzelle des
Bereichs angesprochen. Im String x wird jeweils ein Datensatz zusammengesetzt. ► An Stelle der MsgBox können sie die Daten - wie oben angegeben - in eine Text-Datei exportieren. In diesem Falle müssen sie an den Datei-Anfang als 'Byte Order Mark' (BOM) die 3 Bytes EF BB BF stchreiben. ♦ Details dazu im Kapitel UTF-8. ► Hinweis: aus XML-Daten wie diesen können mit moderner Web-Technik (z.B. XML - XSL) komplette Webseiten erzeugt werden - XML bietet eine ideale Daten-Brücke zum Web ! ► Wenn sie keine anderen Zeichen als ASCII verwenden (Ziffern, Satzzeichen, 'englische' Zeichen), dann können sie auf die aufwändige Umwandlung in UTF-8 Code verzichten. ► Falls sie die Datei auf einen Webserver laden, achten sie auf ausreichende Zugriffsrechte !
|
Sub XML_Export()
Dim v, z, x As String Dim i as Integer Dim datarange As Range
Set datarange = Range("xml_data")
End Subz = "#" i = 1 While Len(z)
x = "<person>"
Wend
v = datarange.Cells(i, 1).Value If (Len(v)) Then
x = x & "<vorname>"
End Ifx = x & str_to_utf8(v) x = x & "</vorname>" z = datarange.Cells(i, 2).Value If (Len(z)) Then
x = x & "<zuname>"
Else
x = x & str_to_utf8(z) x = x & "</zuname>" x = x & "</person>" MsgBox (x) x = ""
End Ifi = i + 1 Private Function str_to_utf8(ByVal s As String) As String Dim c, utf As String
utf = ""
End FunctionFor i = 1 To Len(s)
c = Mid(s,i,1)
Nextutf = utf & chr_to_utf8(c) str_to_utf8 = utf Private Function chr_to_utf8(ByVal s As String) As String Dim uc As Long Dim c, utf As String
utf = ""
End Function
c = Left(s, 1) uc = AscW(c) If uc < 0 Then uc = uc& + &H10000 If (uc < 128) Then utf = c
ElseIf (uc < 2048) Then
utf = utf & Chr((uc \ 64) + 192)
Else
utf = utf & Chr((uc Mod 64) + 128)
utf = utf & Chr((uc \ 4096) + 224)
End Ifutf = utf & Chr((uc \ 64) Mod 64 + 128) utf = utf & Chr((uc Mod 64) + 128) chr_to_utf8 = utf |
|
So werden XML-Daten in Webseiten eingebunden:
<object data="muster2.xml" width="100%"
height="120" border="0">
Ihr Browser kann kein <object> der Type text/xml anzeigen. </object> |
|
|
►
Der <object>-Bereich wurde absichtlich klein gewählt,
damit sie die Wirkung sehen: Wenn der Inhalt größer ist, werden Rollbalken
(scrollbars) angezeigt. ► Die XML-Datei wurde mit XSL formatiert. Das oben angezeigte VBA-Programm ist entsprechend zu ergänzen Links zu XML & XSL. Ansicht der hier verwendeten Dateien (nach dem Laden Quelltext anzeigen): XML & XSL ► So werden variable Daten sauber in Webseiten eingebunden. Beachten sie, dass die zur Formatierung verwendete XSL-Datei nicht verändert wird - nur die Daten werden aktualisiert ! |
►
Die Anzeige neuer, geänderter Daten kann ein Problem darstellen, insbesondere bei M$IE.
Der Browser zeigt hartnäckig alte XML-Daten an, obwohl diese längst durch neue
ersetzt wurden. ► Abhilfe beim Client (unsicher !): Cache-Dateien löschen und Seite neu laden. ► Abhilfe beim Server (besser): XML-Dateinamen mit jedem update erneuern (z.B. Datum & Zeit im Dateinamen). Das erfordert die Erstellung der "gastgebenden" Webseite mit PHP oder Perl: Das Attribut "data" wird entsprechend modifiziert. |
Lesen und Schreiben von Binär-Dateien: |
|
|
Bei Bedarf können sie mit VBA auch Binäre Dateien lesen und schreiben. Der innere Aufbau üblicher Binär-Dateien (Bilder, Sounds, ..) ist zwar gut dokumentiert (Internet), jedoch meist ziemlich komplex. Zu ihrer Erstellung und Bearbeitung verwendet man daher meist fertige Software-Werkzeuge. |
Die vorgestellten Funktionen werden daher meist in Sonderfällen eingesetzt,
für die es (noch) keine verfügbaren Software gibt. Unabhängig davon dienen die gezeigten Beispiele zur Demonstration. |
Lesen einer Binär-Datei:Dieses Beispiel liest eine Binär-Datei und zeigt ihre Daten in einfacher Form an.Da eine Binärdatei keine Struktur (Zeilen..) vorgibt, kann man die Daten in beliebig großen Portionen lesen. Die Anzahl der mit dem Get-Befehl gelesenen Bytes ergibt sich aus der Länge der Lese-Variablen str, die hier mit 16 Byte festgelegt wurde. Mit Befehl Get wird je eine 'Portion' der Daten in die Variable str gelesen und anschließend mit minidump ausgewertet. Kleinere Dateien werden meist mit einem einzigen Get-Befehl komplett in eine Variable eingelesen, wo sie bequem verarbeitet werden können. Größere oder unbekannte Dateien werden in einer Schleife (wie hier) bearbeitet. Auswertung durch minidump: Wenn ihre Dateien nur lesbare Zeichen enthalten, genügt zur Anzeige eine einzige (hier durch Kommentar abgeschaltete) MsgBox. Alle anderen Befehle dieses Sub können dann entfallen. Ansonsten wird in der For-Schleife je ein Byte inb isoliert, sein Zahlenwert bn bestimmt und in Hexadezimal-Format h umgewandelt. Wenn möglich (bn>32) wird das Byte als ASCII-Zeichen angezeigt, ansonsten als Punkt. Die MsgBox zeigt in der ersten Zeile die Hex-Werte der gelesenen Bytes, in der zweiten Zeile die gleichen Bytes als ASCII-Text, soweit möglich. Ähnliche Formen der Ausgabe werden oft als "dump" oder "hex dump" bezeichnet und vorteilhaft mit Hex-Editor Programmen ausgeführt. Achtung: Für je 16 Byte der Datei wird in dieser Demo eine eigene MsgBox angezeigt, verwenden sie daher zum Test kurze Dateien ! |
Sub BinaryRead()
Dim InPath, str As String Dim fn As Integer
InPath = "c:testwav.wav"
End Subfn = FreeFile ' next free filenumber str = String(16, " ") Open InPath For Binary Access Read As #fn
While (Not EOF(fn))
Get #fn, , str
Call minidump(str)
Close #fn Sub minidump(str As String) Dim i, bn As Integer Dim a, b, h, s As String
a = ""
End Sub
s = "" For i = 1 To Len(str)
b = Mid(str, i, 1)
Next ibn = Asc(b) h = Right("0" & Hex(bn), 2) s = s & h & " " If (bn > 32) Then a = a & b
Else
a = a & "."
End If
s = "hex: " & s & vbCrLf s = s & "ascii: " & a MsgBox s &'MsgBox "str=" & str
|
Schreiben einer Binär-Datei:Dieses Beispiel erstellt (schreibt) eine binäre Datei der Länge 256 Byte.Die einzelnen Bytes enthalten von 0..255 (#00..#FF) alle Werte, die für Bytes möglich sind. Die erzeugte Datei können sie (je nach verwendetem Programm) nur teilweise 'lesen', da die ersten Bytes 0..31 Steuerzeichen enthalten. Wenn dieFor-Schleife auf i=32 To 255 geändert wird, dann enthält die erzeugte Datei nur mehr lesbare Zeichen (beginnend mit dem Leerzeichen Chr(32)). Mit dem Put-Befehl wird ein String (der beliebige Zeichen, auch Sonderzeichen enthalten kann) in die Datei geschrieben. Natürlich kann der Put-Befehl nach Bedarf wiederholt werden, z.B. in einer Schleife. |
Sub BinaryWrite()
Dim OutPath, str As String Dim i, fn As Integer
OutPath = "c:\test.bin"
End Sub
fn = FreeFile ' next free filenumber 'Kill OutPath
Open OutPath For Binary Access Write As #fn
str = ""For i = 0 To 255 str = str & Chr(i)
Next i
Put #fn, , str
Close #fn |
|
(Nur) bei Binär-Dateien werden Variable genauso geschrieben und gelesen,
wie sie verwendet und gespeichert werden (Zumindest alle Variablen
fixer Länge). Das erspart die aufwändige Codierung / Decodierung der einzelnen Daten-Typen. Allerdings empfiehlt sich eine genaue Kontrolle der Programme, ob wirklich immer genau die gewünschten Daten-Typen verwendet wurden. Es hat sich bewährt, die Typ-Kennzeichen nach den Variablen-Namen zu verwenden (z.B. $ für String, % für 2Byte-Integer, usw.). ♦ Details zur Codierung / Decodierung von Gleitkomma-Zahlen nach IEEE-754 im Kapitel Gleitkomma-Standards ● Achtung: In jedem anderen Modus ( Append, Input, Output, Random statt Binary ) sowie mit anderen Anweisungen ( Input, Print, Write statt Get, Put ) werden die Variablen anders codiert, d.h. mit zusätzlichen Angaben über Type, Länge usw. ● Achtung: Unter bestimmten Bedingungen (welchen ??) schreibt VBA beim Put von binären Strings zuerst 4 Byte mit Angaben über den String, dann erst die String-Daten (Testen !). In diesem Fall muss man jeden String in einer Schleife in seine einzelnen Bytes (Type Byte ) zerlegen, wie gezeigt. Diese werden (fixe Länge) ohne zusätzliche Daten geschrieben. |
' String (beliebige Länge, hier 6):
str$ = chr(10) & "A" & chr(13) & "usw"Put #fn1, ,str$ ' oder als einzelne Bytes
For i = 1 To Len(str$)
Put #fn1, ,CByte(Asc(Mid(str$,i,1)))
Nextstr$ = String(6, " ") Get #fn2, ,str$ ' Ganze Zahl, 2 Byte (integer)
i2% = 123%Put #fn1, ,i2% Get #fn2, ,i2% ' Ganze Zahl, 4 Byte (long)
i4& = 123456&Put #fn1, ,i4& Get #fn2, ,i4& ' Gleitkomma-Zahl, 4 Byte (single)
r4! = 123!Put #fn1, , r4! Get #fn2, , r4! ' Gleitkomma-Zahl, 8 Byte (double)
r8# = 123#Put #fn1, , r8# Get #fn2, , r8# |
ODBC-Export |
|
| Der Export von Daten als Data Source Name (DSN) für ODBC erfordert weder den Export von Dateien noch VBA-Programmierung: |
■
Vergeben sie einen Namen für den Datenbereich
(Überschriften und Daten !) und speichern sie die Datei als normales
Arbeitsblatt. ■ Der ODBC-Treiber von M$ für Excel kann direkt darauf zugreifen und liefert die enthaltenen Daten an andere Programme. |
|
|
| Microsoft: Datei-Eingabe und -Ausgabe-Optionen in Visual Basic .NET, Data Access Functions | ● Visual Basic 2005 Handbuch (Andreas Kühnel / Galileo) - Laufwerke, Verzeichnisse und Dateien, Dateien & Streams |
|