Search code examples
excelvbafilesystemobject

Excel VBA TextStream.writeline with Chinese characters

I am fairly new to VBA and am stumped on how to resolve the "Run-time error '5': Invalid procedure call or argument" error that I am receiving when executing this code. The cell in question has chinese characters and the code seems to work fine on the english alphabet. The stream is outputting to a text file. (should be an xml file in the future, but I still don't have all the correct formatting implemented)

Dim fso As New FileSystemObject, stream As TextStream    
Set stream = fso.createTextFile("C:\Users\username\XMLs\" _
   & WS_Src.Cells(c.Row, 5).Value & "_" & WS_Src.Cells(c.Row, 4).Value & "_Feature.xml", True)

...

stream.WriteLine "<title>" & vbCrLf & "<![CDATA[ " & WS_Src.Cells(c.Row, 6).Value & "]]>" & vbCrLf   & "</title>" 'error is on this line
stream.Close

Thanks! Chris

Solution

  • Sytax for using CreateTextFile method is something like object.CreateTextFile(filename[, overwrite[, unicode]]) . Where:

    • filename: Required. String expression that identifies the file to create.
    • overwrite Optional. Boolean value that indicates if an existing file can be overwritten. The value is True if the file can be overwritten; False if it can't be overwritten. If omitted, existing files are not overwritten.
    • unicode Optional. Boolean value that indicates whether the file is created as a Unicode or ASCII file. The value is True if the file is created as a Unicode file; False if it's created as an ASCII file. If omitted, an ASCII file is assumed.

    And you have omitted the last param here, but incoming text, being Chinese is not just ASCII. Rather you have to provide a True value for that, I mean for unicode param. This would definitely solve the problem.

    BTW! There are still some factors I can see in the code might cause other run-time errors.

    • As you generating filename by joining cell values, make sure no invalid characters is not present in the path string,
    • Furthermore, only setting overwrite value to true is not enough, but also make sure that the folder already exist. Otherwise the procedure would again caught by run-time errors.

    Hope this helps.