Fail to open unicode txt file in excel
 

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

Fail to open unicode txt file in excel
 

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" wrote:

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

Fail to open unicode txt file in excel
 

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" wrote in message
...
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" wrote:

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

Fail to open unicode txt file in excel
 

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

"SF" wrote:

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" wrote in message
...
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" wrote:

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

Fail to open unicode txt file in excel
 

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

"SF" wrote in message
...
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

Fail to open unicode txt file in excel
 

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

"NickHK" wrote:

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

"SF" wrote in message
...
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

Fail to open unicode txt file in excel
 

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" wrote in message
...
The valid setting for XlPlatform is 1 - 3 in XP 2003. Where are you
getting
65001?
xlMacintosh = 1
xlWindows = 2
xlMSDOS = 3

"NickHK" wrote:

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

"SF" wrote in message
...
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

Fail to open unicode txt file in excel
 

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" wrote:

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" wrote in message
...
The valid setting for XlPlatform is 1 - 3 in XP 2003. Where are you
getting
65001?
xlMacintosh = 1
xlWindows = 2
xlMSDOS = 3

"NickHK" wrote:

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

"SF" wrote in message
...
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

Fail to open unicode txt file in excel
 

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

"Joel" wrote in message
...
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" wrote:

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" wrote in message
...
The valid setting for XlPlatform is 1 - 3 in XP 2003. Where are you
getting
65001?
xlMacintosh = 1
xlWindows = 2
xlMSDOS = 3

"NickHK" wrote:

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

"SF" wrote in message
...
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