0

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

Community
  • 1
  • 1
user4384294
  • 21
  • 5
  • Possible duplicate: Rich text format (with formatting tags) in Excel to unformatted text (http://stackoverflow.com/questions/1673025/rich-text-format-with-formatting-tags-in-excel-to-unformatted-text). Best regards, – Alexander Bell Jun 01 '15 at 00:38
  • Are you using the Data Tab From Outer Sources and choosing XML. Do you want XML and not RTF / Wordpad ? Have you tried opening it in Word ? I would use the XMLDocument or XMLNode object in .NET myself. – Sql Surfer Jun 01 '15 at 01:45
  • no, this is not a repeat of that question. That question is for output, mine is about input and how to select line after line from Wordpad. – user4384294 Jun 01 '15 at 09:25
  • using the Data tab and xml does not work as this is a special form of xml. (I already tried that) – user4384294 Jun 01 '15 at 09:25
  • I tried with Word, but for this type of files, Word give a message: Error, DTD is prohibited. Notepad is limited so I really need to use WordPad, copying and pasting 1 line at a time. I just don't know how to select line after line in Wordpad using VBA – user4384294 Jun 01 '15 at 09:29

1 Answers1

0

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/

Community
  • 1
  • 1
Ralph
  • 9,284
  • 4
  • 32
  • 42
  • Thank you Ralph, but this does not sort the issue. The character encoding remains messed up. I was hoping the "Access Read Lock" would do the trick, but alas, no. :-( – user4384294 Jun 01 '15 at 12:00
  • @user4384294: It seems to me that you need to combine a few solutions in order to get there. Unfortunately, there is no easy solution as Excel is not really strong at handling different encodings. I had the same problem. But now I am getting all files > 10MB using ansi. – Ralph Jun 01 '15 at 12:24
  • Thank you Ralph. Yes, I have to combine solutions which complicate my task A LOT. I now need a solution to identify the size of the file before opening it – user4384294 Jun 01 '15 at 16:57
  • For file size you might want to look at this solution: http://stackoverflow.com/questions/15883237/vba-excel-function-for-returning-file-size-in-byte – Ralph Jun 01 '15 at 18:35
  • Thank you Ralph, this is perfect. A bit more coding, but worth it. – user4384294 Jun 02 '15 at 02:28