如何在VBA中打开使用不正确的字符编码编写的Excel文件 [英] How to open Excel file written with incorrect character encoding in VBA

问题描述

我使用文本编辑器阅读了一个Excel 2003文件，以查看一些标记语言。
当我在Excel中打开文件时，会显示不正确的字符。在检查文件时，我看到编码是Windows 1252或其他一些。如果我用UTF-8手动替换，我的文件打开正常。好的，到目前为止这么好，我可以手动纠正这个事情。

现在的诀窍是这个文件是自动生成的，我需要自动处理（没有人互动）与我的桌面上的有限工具（没有perl或其他脚本语言）。

有没有任何简单的方法来打开这个XL文件在VBA中使用正确的编码（和忽略文件中指定的编码）？

注意，Workbook.ReloadAs对我来说不起作用，它会出错（需要手动操作，因为文件已经打开）。

还是纠正文件通过一些环的唯一方法？或者：文本，检查行编码字符串，如果需要替换，将每行写入新文件...;或导出到csv，然后从csv再次导入特定的编码，另存为xls？

任何提示赞赏。

ADODB对我来说无效（XL表示用户定义的类型，未定义）。

我解决了我的问题解决方法：

  name2 =替换（名称，.xls，.txt）
设置wb = Workbooks.Open（name，True，True）'open只读
设置ws = wb.Worksheets（1）
 ws.SaveAs FileName：= name2，FileFormat：= xlCSV 
 wb.Close False'关闭工作簿而不保存更改
设置wb =无空闲内存
 Workbooks.OpenText文件名：= name2，_ 
原产地：= 65001，_ 
 DataType：= xlDelimited，_ 
逗号：= True 
  

解决方案

我想你可以从另一个工作簿中执行。添加对AcitiveX数据对象的引用，然后添加此子：

 子编码（ByVal sPath $，可选SetChar $ =UTF -8）
 
 Dim stream As ADODB.stream 
设置流=新ADODB.stream 
 
带流
 .Open 
。 LoadFromFile sPath'加载文件
 .Charset = SetChar'设置流编码（UTF-8）
 .SaveToFile sPath，adSaveCreateOverWrite 
 .Close 
结束
 
设置stream = Nothing 
 Workbooks.Open sPath 
 End Sub 
  

然后使用带有关闭编码的文件路径调用此子。

I read an Excel 2003 file with a text editor to see some markup language. When I open the file in Excel it displays incorrect characters. On inspection of the file I see that the encoding is Windows 1252 or some such. If I manually replace this with UTF-8, my file opens fine. Ok, so far so good, I can correct the thing manually.

Now the trick is that this file is generated automatically, that I need to process it automatically (no human interaction) with limited tools on my desktop (no perl or other scripting language).

Is there any simple way to open this XL file in VBA with the correct encoding (and ignore the encoding specified in the file)?

Note, Workbook.ReloadAs does not function for me, it bails out on error (and requires manual action as the file is already open).

Or is the only way to correct the file to go through some hoops? Either: text in, check line for encoding string, replace if required, write each line to new file...; or export to csv, then import from csv again with specific encoding, save as xls?

Any hints appreciated.

EDIT:

ADODB did not work for me (XL says user defined type, not defined).

I solved my problem with a workaround:

name2 = Replace(name, ".xls", ".txt")
Set wb = Workbooks.Open(name, True, True) ' open read-only
Set ws = wb.Worksheets(1)
ws.SaveAs FileName:=name2, FileFormat:=xlCSV
wb.Close False                    ' close workbook without saving changes
Set wb = Nothing                  ' free memory
Workbooks.OpenText FileName:=name2, _
                   Origin:=65001, _
                   DataType:=xlDelimited, _
                   Comma:=True

解决方案

Well I think you can do it from another workbook. Add a reference to AcitiveX Data Objects, then add this sub:

Sub Encode(ByVal sPath$, Optional SetChar$ = "UTF-8")

    Dim stream As ADODB.stream
    Set stream = New ADODB.stream

    With stream
        .Open
        .LoadFromFile sPath ' Loads a File
        .Charset = SetChar  ' sets stream encoding (UTF-8)
        .SaveToFile sPath, adSaveCreateOverWrite
        .Close
    End With

    Set stream = Nothing
    Workbooks.Open sPath
End Sub

Then call this sub with the path to file with the off encoding.

这篇关于如何在VBA中打开使用不正确的字符编码编写的Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！