Externe Daten sind Daten, die sich nicht in der Excel-Arbeitsmappe befinden. Beispiele für externe Daten sind Textdateien oder andere Excel-Arbeitsmappen. In der Praxis kommte es häufig vor, dass man mit externen Daten arbeiten muss. Es gibt zahlreiche Möglichkeiten, Daten in Excel zu importieren.Ich beschränke mich im Folgenden darauf wie sich Daten mit VBA Makros importieren lassen.
Mit der Anweisung „Application.Dialogs“ kann man in Excel integrierte Dialogfelder aufrufen.
Das Excel Dialogfeld „Öffnen“ kann man mit der Anweisung
Application.Dialogs(xlDialogOpen).Show
aufrufen:
Sub Datei_oeffnen_1()
Dim bnReturn As Boolean
bnReturn = Application.Dialogs(xlDialogOpen).Show
Debug.Print bnReturn
End SubWird diese Sub Prozedur gestartet wird das Excel Dialogfeld „Öffnen“ angezeigt:
In diesem Dialogfeld kann man die gewünschte Datei anklicken und durch Klick auf den Button „Öffnen“ wird die Datei geöffnet. Durch Klicken auf den Button „Abbrechen“ wird der Dialog abgebrochen.
Einen Pfad und eine MS-DOS-Platzhalter-Dateifilterspezifikation kann man wie folgt übergeben:
Sub Datei_oeffnen_1b()
Dim bnReturn As Boolean
bnReturn = Application.Dialogs(xlDialogOpen).Show("D:\Dokumente\Eigene Dokumente\Excel\vba\Delete\*.xlsx")
Debug.Print bnReturn
End SubDer Nachteil ist, dass bei der Ausführung des Befehls der Dateiname nicht zwischengespeichert wird.
Dateinamen mit Application.GetOpenFilename ermitteln
Die Anweisung „Application.GetOpenFilename“ zeigt das Excel Dialogfeld „Öffnen“ an und fragt einen Dateinamen vom Benutzer ab, ohne die Datei zu öffnen. In Klammern kann man wie im Beispiel gezeigt einen Dateifilter eingeben. Wie im folgenden Beispiel gezeigt besteht der Dateifilter aus zwei Anteilen, die durch ein Komma getrennt sind
- dem Dateifilter Anzeigetext, z. B. „Excel-Dateien(„*.xls*)“
Dieser Anzeigetext wird im Excel Dialogfeld „Öffnen“ rechts unten angezeigt. - der MS-DOS-Platzhalter-Dateifilterspezifikation, z. B. *.xls*
Fehlt das Dateifilter Argument in Klammern, wird der voreingestellte Wert „Alle Dateien (*.*),*.*“ verwendet.
Beispiel:
Sub OpenTextFile_1()
Dim varFileName As Variant
varFileName = Application.GetOpenFilename("Excel-Dateien(*.xls*), *.xls*")
Debug.Print varFileName
End SubWichtig: der Rückgabewert für den Dateinamen, im Beispiel „varFileName“ muss als Variant deklariert werden:
- falls auf den Button „Abbrechen“ geklickt wird, enthält die Variable den boolschen Wert „False“
- falls auf den Button „Öffnen“ geklickt wird enthält die Variable den Dateinamen als String.
Wird diese Sub Prozedur gestartet wird das Excel Dialogfeld „Öffnen“ angezeigt:
Hier kann man den gewünschten Dateinamen anklicken. Wenn man dann den Button „Öffnen“ klickt wird der Dateiname zurückgegeben. Die Datei wird nicht geöffnet. Durch Klicken auf den Button „Abbrechen“ wird der Dialog abgebrochen.
Excel Datei mit der Anweisung "Workbooks.Open" öffnen
Eine Excel Datei lässt sich mit der Anweisung „Workbooks.Open“ öffnen. Das ist besonders wichtig, wenn man Daten aus der zu öffnenden Datei kopieren möchte. Ich habe das in der Praxis sehr häufig gemacht.
Sub OpenTextFile_2()
Dim varFileName As Variant, sWBInput As String
varFileName = Application.GetOpenFilename("Excel-Dateien(*.xls*), *.xls*")
If varFileName <> False Then
Workbooks.Open varFileName
sWBInput = ActiveWorkbook.Name
Debug.Print varFileName
' Geoeffnete Excel Datei schliessen, falls benoetigt
Workbooks(sWBInput).Close SaveChanges:=False
End If
End SubFalls die mit dem VBA Makro geöffnete Excel Datei nach der Bearbeitung nicht mehr benötigt wird, kann man sie mit dem Befehl
Workbooks(sWBInput).Close SaveChanges:=False
schließen, ohne das Änderungen gespeichert werden.
Daten aus einem anderen Excel Tabellenblatt einlesen:
Mit der Anweisung „Workbooks.Open“ lassen sich auch Daten aus einem anderen Tabellenblatt einlesen. Wichtig ist dabei, dass die Zeilen in den Excel Tabellenblatt durch einen eindeutigen Kennzeichner gekennzeichnet sind. Ich habe das in der Praxis sehr häufig eingesetzt und halte es daher für sehr wichtig.
Beispiel:
Eine Excel Datei enthält den Kurs einiger Aktien. Die Aktien sind durch ihre „International Security Identification Number“ (ISIN) gekennzeichnet. Die ISIN dient der eindeutigen internationalen Identifikation von Wertpapieren. Eine zweite Excel Datei enthält weiteren Details zu den Aktien wie Marktkapitalisierung, Dividendenrendite, KGV oder Gewinn pro Aktie. Nun sollen die Aktiendetails in die Excel Datei kopiert werden, die nur die Kurse enthält.
Die zu befüllende Excel Datei sieht wie folgt aus (die aufgeführten Zahlen sind willkürlich):
Die Excel Datei mit den Details zu den Aktienkursen sieht wie folgt aus (die Zahlen sind willkürlich):
Die zu befüllende Excel Datei kann mit folgender VBA Sub Prozedur „Copy_info_from_other_file“ befüllt werden. Dazu wird die zu befüllende Excel Datei als „xlsm“ Datei gespeichert und der Code in ein Modul der Datei eingefügt.
Option Explicit
'
Sub Copy_info_from_other_file()
Dim i As Integer, k As Integer
Dim iNoOfRowsInput As Integer, iNoOfRowsMaster As Integer
Dim sDateiName1 As String
Dim sISINMaster() As String, sISINInput() As String
Dim sSheetMaster As String, sSheetInput As String
Dim sWBMaster As String, sWBInput As String
Dim oWBMaster As Worksheet, oWBInput As Worksheet
'
sSheetMaster = "Tabelle1"
sSheetInput = "Tabelle1"
'
sWBMaster = ActiveWorkbook.Name
Set oWBMaster = Workbooks(sWBMaster).Worksheets(sSheetMaster)
'
sDateiName1 = "E:\Dokumente\Excel Neu\vba\_Blog\Beispiele\Aktien_Details_2022_08_30.xlsx"
Workbooks.Open Filename:=sDateiName1
sWBInput = ActiveWorkbook.Name
Set oWBInput = Workbooks(sWBInput).Worksheets(sSheetInput)
'
iNoOfRowsMaster = GetLastRow(oWBMaster)
iNoOfRowsInput = GetLastRow(oWBInput)
ReDim sISINMaster(iNoOfRowsMaster)
ReDim sISINInput(iNoOfRowsInput)
'
For i = 2 To iNoOfRowsMaster
sISINMaster(i) = Replace(oWBMaster.Cells(i, 3).Value, "'", "")
Next i
'
For i = 2 To iNoOfRowsInput
sISINInput(i) = Replace(oWBInput.Cells(i, 3).Value, "'", "")
Next i
'
For i = 2 To iNoOfRowsMaster
Debug.Print i
For k = 2 To iNoOfRowsInput
If Len(sISINMaster(i)) > 0 And sISINMaster(i) = sISINInput(k) Then
oWBMaster.Cells(i, 4).Value = oWBInput.Cells(k, 4).Value ' Marktkapitalisierung
oWBMaster.Cells(i, 5).Value = oWBInput.Cells(k, 5).Value ' Dividendenrendite
oWBMaster.Cells(i, 6).Value = oWBInput.Cells(k, 6).Value ' KGV
oWBMaster.Cells(i, 7).Value = oWBInput.Cells(k, 7).Value ' Gewinn pro Aktie
End If
Next k
Next i
'
Workbooks(sWBInput).Close savechanges:=False
Set oWBMaster = Nothing
Set oWBInput = Nothing
End Sub
'
'
Function GetLastRow(ws As Worksheet) As Long
Dim iFirstRow As Integer
Dim i As Long, iLastRow As Long, iLastRowFixed As Long
'
Application.Goto ws.Cells(1, 1)
ws.Activate ' Make the sheet "ws" the active sheet
ActiveSheet.UsedRange ' Reset Used Range
'
' Save existing values
iFirstRow = ws.UsedRange.Row
iLastRow = ws.UsedRange.Rows.Count + iFirstRow - 1
' iLastRow = ws.UsedRange.SpecialCells(xlCellTypeLastCell).Row ' this doesn't work with filtered Sheet
iLastRowFixed = iLastRow
'
' Do rows from bottom up
For i = iLastRowFixed To 1 Step -1
' The CountA function counts the number of cells that are not empty in a range
If Application.CountA(ws.Rows(i)) = 0 Then
iLastRow = iLastRow - 1
Else
Exit For
End If
Next i
'
GetLastRow = iLastRow
End Function
Wichtig: im Beispiel Code wird die Funktion „GetLastRow“ verwendet. Mit dieser Funktion kann die letzte befüllte Zeile in einem Excel Tabellenblatt ermittelt werden. Ich verwende diese Funktion seit Jahren und sie hat immer das richtige Ergebnis geliefert.
Text-Datei mit mit der Anweisung "Worksbooks.OpenText" öffnen
Eine Textdatei lässt sich mit der Anweisung „Workbooks.OpenText“ öffnen. Eine Textdatei kann beispielsweise Daten im ASCII Format enthalten, die mit einer anderen Software erzeugt wurden.
Beispiel:
Die kommaseparierte Text-Datei „Testdaten.txt“ soll eingelesen werden. Diese Datei habe ich für den Test erstellt. Sie enthält zwei Zeilen mit jeweils 4 durch ein Komma getrennten Zahlen:
Die Anweisungen „ChDrive“ sowie „ChDir“ dienen dazu das Laufwerk und das Verzeichnis für die Anweisung „Application.GetOpenFilename“ voreinzustellen.
Sub OpenTextFile_4()
Dim varFileName As Variant, sWBInput As String
' Laufwerk und Verzeichnis für den Öffnen-Dialog vorgeben:
ChDrive "E:\"
ChDir "E:\Dokumente\Excel Neu\vba\_Blog\Beispiele"
' Dateiname inkl. Pfad auswählen
varFileName = Application.GetOpenFilename("Textdateien (*.txt), *.txt")
' Prüfen, ob nicht abgebrochen wurde
If varFileName <> False Then
Workbooks.OpenText Filename:=CStr(varFileName), DataType:=xlDelimited, Comma:=True
sWBInput = ActiveWorkbook.Name
Debug.Print varFileName
'Workbooks(sWBInput).Close SaveChanges:=False
Else
Debug.Print "Es wurde keine Datei ausgewählt."
End If
End SubWird die Sub-Prozedur gestartet und die Text-Datei „Testdaten.txt“ ausgewählt, wird der Inhalt der Datei als neue Excel Datei geöffnet. Von dort kann man die Daten in das gewünschte Excel Tabellenblatt kopieren. Falls die mit dem VBA Makro geöffnete Excel Datei nach der Bearbeitung nicht mehr benötigt wird, kann man sie mit dem Befehl
Workbooks(sWBInput).Close SaveChanges:=False
schließen, ohne das die Änderungen gespeichert werden.
Text-Datei mit mit der Anweisung "Open" öffnen
Als Alternative für das Einlesen der Textdatei „Testdaten.txt“ kann man die im folgenden Beispiel gezeigten „Open“ Anweisung verwenden und die Daten Zeile für Zeile mit der Anweisung „Line Input“ einlesen. Die Textdatei „Testdaten.txt“ enthält Kommas als Trennzeichen. Die Daten in den eingelesenen Zeilen lassen sich daher mit einer Splitanweisung trennen und in ein Datenfeld (engl. Array) einlesen. Im Beispiel werden die Datenfeldelemente im Direktfenster ausgegeben. Wie im nächsten Beispiel gezeigt könnte man die Daten auch in ein Excel Tabellenblatt schreiben.
Sub OpenTextFile_4_Alternative()
Dim varFileName As Variant, sWBInput As String, textline As String
Dim iZeile As Integer, varFeld As Variant, k As Integer
' Laufwerk und Verzeichnis für den Öffnen-Dialog vorgeben:
ChDrive "E:\"
ChDir "E:\Dokumente\Excel Neu\vba\_Blog\Beispiele"
' Dateiname inkl. Pfad auswählen
varFileName = Application.GetOpenFilename("Textdateien (*.txt), *.txt")
' Prüfen, ob nicht abgebrochen wurde
If varFileName <> False Then
Open varFileName For Input As #1
iZeile = 1
Do Until EOF(1)
Line Input #1, textline
varFeld = Split(textline, ",")
For k = LBound(varFeld) To UBound(varFeld)
Debug.Print varFeld(k),
Next k
Debug.Print
iZeile = iZeile + 1
Loop
Close #1
Else
Debug.Print "Es wurde keine Datei ausgewählt."
End If
End SubIm folgenden Beispiel wird die kommaseparierte Textdatei „Testdaten.txt“ eingelesen und das Ergebnis in das Excel Tabellenblatt „Tabelle1“ geschrieben.
Wichtig:
Das mit der „Split“ Anweisung erzeugte Datenfeld beginnt mit dem Index 0. Der minimale Spaltenindex in der Anweisung „ws.cells(iZeile, k+1).value =“ muss größer oder gleich 1 sein. Daher wurde „k+1“ verwendet.
Sub OpenTextFile_4_Alternative_2()
Dim varFileName As Variant, sWBInput As String, textline As String
Dim iZeile As Integer, varFeld As Variant, k As Integer, strSeparator As String
Dim ws As Worksheet
'
' Die beiden folgenden Zeilen muessen angepasst werden
Set ws = Worksheets("Tabelle1")
strSeparator = ","
' Laufwerk und Verzeichnis für den Öffnen-Dialog vorgeben:
ChDrive "E:\"
ChDir "E:\Dokumente\Excel Neu\vba\_Blog\Beispiele"
'
' Dateiname inkl. Pfad auswählen
varFileName = Application.GetOpenFilename("Textdateien (*.txt), *.txt")
' Prüfen, ob nicht abgebrochen wurde
If varFileName <> False Then
Open varFileName For Input As #1
iZeile = 1 ' Zeilenzaehler auf 1 setzen
Do Until EOF(1)
Line Input #1, textline
varFeld = Split(textline, ",")
For k = LBound(varFeld) To UBound(varFeld)
ws.Cells(iZeile, k + 1).Value = CStr(varFeld(k))
Next k
iZeile = iZeile + 1
Loop
Close #1
Else
Debug.Print "Es wurde keine Datei ausgewählt."
End If
'
Set ws = Nothing
End Sub