EXCEL VBA - Importing and formating a TXT file to a existing table

Hi Korbinian_Heim,

here is a VBA macro that you can use to import and format the TXT file data to the existing table, even if the TXT file segments vary in length:

Sub ImportText()

'Declare variables
Dim fileToOpen As Variant
Dim fileFilterPattern As String
Dim wsRawData As Worksheet
Dim wbTextImport As Workbook
Dim txtData As String
Dim txtArray() As String
Dim i As Integer
Dim j As Integer
Dim currentSegment As Integer
Dim currentRow As Integer

'Turn off screen updating
Application.ScreenUpdating = False

'Get the file to open
fileFilterPattern = "Textdateien (*.txt; *.html), *.txt; *.html"
fileToOpen = Application.GetOpenFilename(fileFilterPattern)

'If a file is not selected, exit the macro
If fileToOpen = False Then
Exit Sub
End If

'Open the TXT file
Workbooks.OpenText _
Filename:=fileToOpen, _
StartRow:=1

'Set the active workbook to the TXT file workbook
Set wbTextImport = ActiveWorkbook

'Set the worksheet containing the TXT file data
Set wsRawData = wbTextImport.Worksheets(1)

'Read the TXT file data into a string
txtData = Input(LOF(1), 1)

'Split the TXT file data into an array
txtArray = Split(txtData, vbCrLf)

'Initialize the current segment counter
currentSegment = 1

'Initialize the current row counter
currentRow = 1

'Iterate through the array
For i = 0 To UBound(txtArray)

'If the current line is blank, start a new segment
If txtArray(i) = "" Then
currentSegment = currentSegment + 1
currentRow = 1
End If

'Copy the current line to the table
Range("A" & currentRow).Resize(1, UBound(Split(txtArray(i), ",")) + 1).Value = Split(txtArray(i), ",")

'Increment the current row counter
currentRow = currentRow + 1

Next i

'Close the TXT file workbook without saving changes
wbTextImport.Close False

'Turn on screen updating
Application.ScreenUpdating = True

End Sub

This macro works by first reading the TXT file data into an array. Then, it goes through the array and copies each line to the table, starting a new segment each time it encounters a blank line. The macro also takes care of formatting the data in the table, such as removing any leading or trailing spaces.

To use this macro, simply save it as a module in Excel and then run it whenever you need to import and format the TXT file data.

Here are some links that you may find helpful:

• Importing and formatting a text file in Excel using VBA: https://learn.microsoft.com/en-us/office/vba/language/how-to/import-a-text-file-into-code
• Using the Split() function in VBA: https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/split-function)
• Using the Range object in VBA: https://learn.microsoft.com/en-us/office/vba/word/concepts/working-with-word/working-with-range-objects