Opening UTF-16 text file with VBA

[prdufresne]

I'm writing some VBA code to open a text file (.txt), read and parse the contents line by line to isolate some data that will be used to populate a spreadsheet in Excel.

The program hasn't progressed very far, because my initial test code was failing to read the text files I need to work with. If I cut and paste the contents into a new file, it would read it, so I decided to compare the two files with a HEX editor only to discover that the original file is using 16 bits per character (I assume UTF-16, but I don't know for sure).

Sample Hex from the start of the file:

ff fe 46 00 75 00 6c 00 6c 00

Notepad handles the file without trouble. It does't show the first two bytes, but shows the next 8 as the word Full.

Even when I set my file handling to use UTF, it fails to read the file.

How can I get VBA to read this file correctly? I'm using the OpenTextFile and ReadLine methods as in the following sample:

[vba]
fn = SelectFile() ' Calls a function that allows the user to select a file

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile(fn, ForReading, -1)


Response = MsgBox(fn)
ALine = f.ReadLine
Response = MsgBox(ALine)
[/vba]
Any suggestions on how to read this file line by line?

ThanX!

[shrivallabha]

Try the following syntax:

[VBA]Dim GetOpenFile As String
Dim MyData As String
Dim r As Long
GetOpenFile = Application.GetOpenFilename
r = 1
Open GetOpenFile For Input As #1
Do While Not EOF(1)
Line Input #1, MyData
Cells(r, 1).Value = MyData
r = r + 1
Loop
Close #1[/VBA]

Apart from this there is another application.opentext method for handling text file. Did you try it?

[prdufresne]

Thank you so much. That worked great.

The next step is to modify this code so I can select multiple files, but this will certainly get me started.

[amateur]

What do you mean exactly select multiple files? Do you want open more than one file in a single process or do you want an "Open Files" dialogue window?

Use this code for Open File dialogue window:

Sub GetImportFileName()
Dim Filt As String
Dim FilterIndex As Integer
Dim Title As String

Filt = "Text Files (*.txt),*.txt," & _
"All Files (*.*),*.*"
FilterIndex = 1

Title = "Select a File to Import"

FileName = Application.GetOpenFilename _
(FileFilter:=Filt, _
FilterIndex:=FilterIndex, _
Title:=Title)
If FileName = False Then
MsgBox "No file was selected."
Exit Sub
End If
'put shrivallabha's code here or use the following:

'Open FileName For Input As #1
'r = 0
'Do Until EOF(1)
' Line Input #1, Data
' ActiveCell.Offset(r, 0) = Data
' r = r + 1
' Loop
'Close #1End Sub

sorry for my weak English