Copying lines from Wordpad into Excel using VBA

I am writing some code where I import some files under TMX (a form of xml). I tried various options

a) using the Open FileName For input, but this messes up the character encoding

b) opening the file and copying the data using the msoDialog, but this return an error if the file is too large (which is often the case) and this put the data in an utterly messy manner.

c) opening the file using notepad, but there are the same limitations in so far as copying the entirety of the file into Excel as the previous option. I am not trying to use a shell function calling onto Wordpad.

My issue right now, is that I need to copy the file line by line to treat its content according to my needs (hopefully without losing the character encoding

Would someone know how to copy every single line from the file opened in WordPad and paste it post treatment (selection of the relevant elements) into Excel?

Thank you

Solution

For large files you can use this solution:

Public Sub ImportTMXtoExcel()

Call Application.FileDialog(msoFileDialogOpen).Filters.Clear
Call Application.FileDialog(msoFileDialogOpen).Filters.Add("TMX Files", "*.tmx")
Application.FileDialog(msoFileDialogOpen).Title = "Select a file to import..."
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
intChoice = Application.FileDialog(msoFileDialogOpen).Show
If intChoice <> 0 Then
    strFileToImport = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
Else
    Exit Sub
End If

intPointer = FreeFile()
Open strFileToImport For Input Access Read Lock Read As #intPointer

intCounter = 0
Do Until EOF(intPointer)
    Line Input #intPointer, strLine
    intCounter = intCounter + 1
    Worksheets(1).Cells(intCounter + 1, 1).Value2 = strLine
Loop

Close intPointer

End Sub

For other encodings you can use ADO's Stream as described in this solution: VB6/VBScript change file encoding to ansi

If you have large files which require ADO's Stream then you might want to consider breaking down the large files first as described in this solution: How to split a large text file into smaller files with equal number of lines?

The following website provides a tool which mimics the Unix command split for Windows in command prompt: https://www.fourmilab.ch/splits/