Fail to open unicode txt file in excel

[SF]

Hi,

I can mannually import a unicode text file into excel by specifying the File
origin to be "65001 : Unicode (UTF-8)" but fail to automate the opening of
the same file with the following code.

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet


Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlApp.Workbooks.OpenText "C:\PIC\OUTPUT.TXT" => Excel open the
OUTPUT.TXT file but not display the correct Unicode format

Could someone advice

SF

 

[Joel]

I'm not usre but I think the UNICODES look like Binary data. Opening a File
in Text Mode that contains Binary characters can have problems. When you
open a file in text Mode Basic expects carriage returns and line feed to be
in the file. Basic has a limitation for the length of a Text String. Errors
will occur in Basic if the Text String length is exceeeded.

try Opening the file in binary mode.

 

[SF]

Hi Joel,

Thank you very much for your promt advice. I have come up with the code
below but I am still having problem in replacing the C:\PIC\OUTPUT.XLS,
close the worksheet and exit Excel


ActiveWorkbook.SaveAs FileName:="C:\PIC\OUTPUT.xls",
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Close SaveChanges:=True
xlApp.UserControl = False
'Application.DisplayAlerts = False
xlApp.Quit

SF

 

[Joel]

You only need to save the file and quit the application. Closing the
workbook terminates the macro and the Macro doesn't finsih. The Macro is
part of the workbook. I don't know what the xlApp is. I used just
application.


ActiveWorkbook.SaveAs FileName:="C:\PIC\OUTPUT.xls", _
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Appplication.quit

 

[NickHK]

have a look at the 2nd argument to the OpenText method, Origin.
<Help>
Origin
Optional Variant. Specifies the origin of the text file. Can be one of the
following XlPlatform constants: xlMacintosh, xlWindows, or xlMSDOS. If this
argument is omitted, the method uses the current setting of the File Origin
option in the Text Import Wizard.
<Help>

You can try to this property to 65001 when opening.

NickHK

 

[Joel]

The valid setting for XlPlatform is 1 - 3 in XP 2003. Where are you getting
65001?
xlMacintosh = 1
xlWindows = 2
xlMSDOS = 3

 

[NickHK]

Yes that's what Help says, if you see the recorded code from opening a text
file, assuming you change the setting, you will see something like ",
Origin:=949," referring to the code page to use when opening.
65001 refers to Unicode (UTF-8), which the OP says he needs.

NickHK

 

[Joel]

This may be another 2007 problem? Below is the code I got from 2003. There
is no 65001. try going to VBA window. right click mouse and select object
browser. Then search for XlPlatform. Check if the values of the different
platforms are 1 to 3.

It may be that 2007 is using a wider data word and 65001 is really 1 in 2003.



Workbooks.OpenText Filename:= _
"C:\Documents and Settings\Joel\My Documents\computer.txt",
Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2,
1)), _
TrailingMinusNumbers:=True

 

[NickHK]

I'm saying that Help is somewhat incomplete, as it only mentions xlPlatform
values as valid, whereas you can actually pass a code page.
In your code below, there is no 65001, but there is 437, which "OEM United
States", and hence not a member of xlPlatform.

Can't say about XL2007.

NickHK