vba 如何创建文件名中包含特殊字符的文本文件

How can I create text files with special characters in their filenames

提问by nixda

Demonstration of my problem

演示我的问题

• Open a new Excel workbook and save these symbols 設計師協會 to cell [A1]
• insert the following VBA code somewhere in the editor (Alt+F11)
• execute it line per line (F8)

• 打开一个新的 Excel 工作簿并将这些符号设计师协会保存到单元格 [A1]
• 在编辑器中的某处插入以下 VBA 代码 ( Alt+ F11)
• 每行执行一次 ( F8)

Sub test()

    strCRLF = StrConv(vbCrLf, vbUnicode)
    strSpecialchars = StrConv(Cells(1, 1), vbUnicode)
    strFilename = "c:\test.txt"

    Open strFilename For Output As #1
    Print #1, strSpecialchars & strCRLF;
    Close #1

End Sub

You will get a textfile which contains the chinese characters from [A1]. This proofs that VBA is able to handle unicode characters if you know the trick with adding StrConv(vbCrLf, vbUnicode)

您将获得一个文本文件，其中包含来自 [A1] 的中文字符。如果您知道添加的技巧，这证明 VBA 能够处理 unicode 字符StrConv(vbCrLf, vbUnicode)

Now try the same for strFilename = "C:\" & strSpecialchars & ".txt". You will get an error that you can't create a file with this filename. Of course you can't use the same trick adding a new line since its a filename.

现在对strFilename = "C:\" & strSpecialchars & ".txt". 您将收到一个错误，提示您无法使用此文件名创建文件。当然，您不能使用相同的技巧添加新行，因为它是文件名。

How can I create text files with special characters in their filenames using VBA?
Is there a work-around or am I doing something wrong?

如何使用 VBA 创建文件名中包含特殊字符的文本文件？
有解决方法还是我做错了什么？

Note

笔记

• I'm using Windows 7 x64. I'm able to create text files with special characters manually
• I found a second method using FileSystemObject. But I hope I could avoid setting a reference to the VB script run-time library

• 我正在使用 Windows 7 x64。我可以手动创建带有特殊字符的文本文件
• 我使用 FileSystemObject找到了第二种方法。但我希望我可以避免设置对 VB 脚本运行时库的引用

回答by GSerg

Value retrieved from the cell is already in Unicode.

从单元格中检索到的值已经是 Unicode 格式。

StrConv(vbUnicode)gives you "double unicode" which is broken because it went through a conversion using the current sustem codepage.
Then the Printcommand converts it back to "single unicode", again using the current system codepage. Don't do this. You're not saving unicode, you're saving invalid something that may only appear valid on your particular computer under your current settings.

StrConv(vbUnicode)为您提供“双 unicode”，该代码已损坏，因为它使用当前系统代码页进行了转换。
然后该Print命令再次使用当前系统代码页将其转换回“单一 unicode”。不要这样做。您不是在保存 unicode，而是在保存无效的内容，这些内容可能仅在您当前设置下的特定计算机上才显示有效。

If you want to output Unicode data (that is, avoid the default VB mechanism of auto-converting output text from Unicode to ANSI), you have several options.

如果您想输出 Unicode 数据（即避免将输出文本从 Unicode 自动转换为 ANSI 的默认 VB 机制），您有多种选择。

The easiest is using FileSystemObjectwithout trying to invent anything about unicode conversions:

最简单的方法是在FileSystemObject不尝试发明任何有关 unicode 转换的情况下使用：

With CreateObject("Scripting.FileSystemObject")
  With .CreateTextFile("C:\" & Cells(1).Value & ".txt", , True)
    .Write Cells(1).Value
    .Close
  End With
End With

Note the last parameter that controls Unicode.

请注意控制 Unicode 的最后一个参数。

If you don't want that, you can declare CreateFileWand WriteFilefunctions:

如果你不想那样，你可以声明CreateFileW和WriteFile函数：

Private Declare Function CreateFileW Lib "kernel32.dll" (ByVal lpFileName As Long, ByVal dwDesiredAccess As Long, ByVal dwShareMode As Long, ByRef lpSecurityAttributes As Any, ByVal dwCreationDisposition As Long, ByVal dwFlagsAndAttributes As Long, ByVal hTemplateFile As Long) As Long
Private Declare Function CloseHandle Lib "kernel32.dll" (ByVal hObject As Long) As Long
Private Declare Function WriteFile Lib "kernel32.dll" (ByVal hFile As Long, ByRef lpBuffer As Any, ByVal nNumberOfBytesToWrite As Long, ByRef lpNumberOfBytesWritten As Long, ByRef lpOverlapped As Any) As Long

Private Const CREATE_ALWAYS As Long = 2
Private Const GENERIC_WRITE As Long = &H40000000

Dim hFile As Long
hFile = CreateFileW(StrPtr("C:\" & Cells(1).Value & ".txt"), GENERIC_WRITE, 0, ByVal 0&, CREATE_ALWAYS, 0, 0)

Dim val As String
val = Cells(1).Value

WriteFile hFile, &HFEFF, 2, 0, ByVal 0&  'Unicode byte order mark (not required, but to please Notepad)
WriteFile hFile, ByVal StrPtr(val), Len(val) * 2, 0, ByVal 0&

CloseHandle hFile

回答by CuberChase

You are on the right track with the FileSystemObject. As Morbo mentioned you can late bind this so no reference is set. The FSO has a CreateTextFile function which can be set in unicode so the characters will appear as '??????' in VBA but will write correctly to the filename. Note the second parameter of the CreateTextFile function specifies a unicode string for the filename. The following will do the trick for you:

您在 FileSystemObject 的正确轨道上。正如 Morbo 提到的，您可以后期绑定它，因此没有设置参考。FSO 有一个 CreateTextFile 函数，它可以在 unicode 中设置，因此字符将显示为 '??????' 在 VBA 中，但会正确写入文件名。请注意 CreateTextFile 函数的第二个参数指定文件名的 unicode 字符串。以下内容将为您解决问题：

Sub test()
    Dim strCRLF As String, strSpecialchars As String, strFilename As String
    Dim oFSO As Object, oFile As Object

    strCRLF = StrConv(vbCrLf, vbUnicode)
    strSpecialchars = StrConv(Cells(1, 1), vbUnicode)
    strFilename = "C:\" & Cells(1, 1).Value & ".txt"

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFile = oFSO.CreateTextFile(strFilename, , True)

    oFile.Write strSpecialchars & strCRLF

    oFile.Close

    Set oFile = Nothing
    Set oFSO = Nothing
End Sub