Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
fail to link a pdf file in excel 2003 with Adobe Acrobat 7.0 Prof | Excel Discussion (Misc queries) | |||
Excel 2003 hyperlinks to Adobe files fail to open | Excel Discussion (Misc queries) | |||
Open and Save fail after SP1 | Setting up and Configuration of Excel | |||
Templates fail to open | Excel Discussion (Misc queries) | |||
Templates fail to open | Setting up and Configuration of Excel |