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

by

Value retrieved from the cell is already in Unicode.

StrConv(vbUnicode) gives you “double unicode” which is broken because it went through a conversion using the current system codepage.
Then the Print command 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.

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.

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

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.

If you don’t want that, you can declare CreateFileW and WriteFile functions:

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

More Related Contents:

  1. Getting error Procedure too large in VBA Macros (Excel)
  2. Workbooks.Open Method in VBA
  3. Can I Get the Source Range Of Excel Clipboard Data?
  4. VBA – Run Time Error 1004 ‘Application Defined or Object Defined Error’
  5. Consolidating worksheets into one
  6. VBA hash string
  7. Wait for Shell to finish, then format cells – synchronously execute a command
  8. How to error handle 1004 Error with WorksheetFunction.VLookup?
  9. How to compare two entire rows in a sheet
  10. how to detect whether VBA excel found something?
  11. Excel tab sheet names vs. Visual Basic sheet names
  12. Determine if cell contains data validation
  13. vba listbox multicolumn add [duplicate]
  14. Fastest way to delete rows which cannot be grabbed with SpecialCells
  15. Combine multiple Excel workbooks into a single workbook
  16. Excel Macro, inserting internationally valid formula during run-time
  17. Sheet protection: UserInterFaceOnly gone
  18. VBScript – How to make program wait until process has finished?
  19. Procedure Too Large
  20. Add a “CC recipient” to Outlook 2010 VBA
  21. Copy filtered data to another sheet using VBA
  22. Range.Find on a Date That is a Formula
  23. TextJoin UDF For Excel 2013
  24. Range() vs Cells() vs Range.Cells()
  25. Open an Excel file from SharePoint site
  26. Excel VBA to answer Internet Explorer 11 download prompt, in Windows 10?
  27. Reading Web Pages using Excel VBA
  28. Excel VBA: auto click and open file from website
  29. How to have vba execute every 10 minutes?
  30. Read and write from/to registry in VBA

Leave a Comment