Import of data sets with VBA in Microsoft Excel

You can open simple text files with the Excel text conversion assistant and save the data in a spreadsheet. If, however, further program logic has to be built in, e.g. If, for example, you carry out a special data selection, you cannot avoid your own import program. Own import program The following program should serve as the basic framework for such an import program. It reads so-called CSV files (CSV = Commaseparated Values). These are text files in which there is one data record per line. The data of the data record are separated from one another by defined characters (e.g. semicolons).

The text file can have the file extension .txt. The file extension .csv is also often used. In order to read in the lines, the lines are broken down into the individual data. This is done with the already known function Split (). The data is then clearly arranged on a worksheet:


Sub DatensaetzeLesen()
Dim Zeile As String
Dim T() As String
Dim i As Integer, k As Integer
ThisWorkbook.Worksheets("Tabelle2").Activate
On Error GoTo Fehler
' Datei öffnen zum Lesen
Open ThisWorkbook.Path & "\eindaten.txt" _
For Input As 1
i = 1
' Solange bis Datei-Ende
Do Until EOF(1)
' Zeile lesen
Line Input #1, Zeile
' Zeile zerlegen
T = Split(Zeile, ";")
Fork=0ToUBound(T)
Cells(i, k + 1).Value = T(k)
Next k
i = i + 1
Loop
' Datei schließen
Close 1
Range("A:E").Columns.AutoFit
Exit Sub
Fehler:
MsgBox (Err.Description)
End Sub

The application is an extension of the previous "Reading from Text Files" example. Only the additional components are explained here. A dynamic data field is declared. This is required to split the lines. After reading a line, the line is split up with the Split () function and saved in the dynamic data field. All elements of the data field are output side by side in individual cells. The number of elements in a data record and thus the upper limit of the dynamic data field are recognized with the UBound () function.

Was the explanation to "Import of data sets with VBA in Microsoft Excel"Helpful? Rate now:

Weitere Erklärungen zu Skriptsprachen