Runtime Error 5 when using a filesystemobject textstream to write a .CSV file

[mps]

Hi, Using code I have put together from what I have read on the net I am trying to export data from a worksheet as a unicode csv file using a filesystemobject textstream. This is so that my CSV can contain non-english characters in the text. The code works if I have english characters on the sheet. To test the code with non-english character I did the following:

Excel 2010 insert menu / ribbon -> Symbol -> Choose subset cyrillic -> Insert some cyrillic characters in a cell

With the cyrillic characters the code gives an error on the WriteLine code (see below) and the error says 'Runtime Error 5 Invalid Procedure call or argument'

Can anyone help me understand what I am doing wrong? Thanks.

[also posted at http://www.ozgrid.com/forum/showthread.php?t=167186 ]

nosalesrows = Sheet2.Range("A" & LTrim(Str(maxrows))).End(xlUp).Row 
 
If trainingmode = False Then 
     'only save sales data if we are not in training mode
    Set fso = New FileSystemObject 
beginagain100: 
    try = 0 
tryagain100: 
    try = try + 1 
    Err.Clear 
    On Error Resume Next 
    Set txtStrm = fso.OpenTextFile(fname, IOMode:=ForAppending, Create:=True) 
     'check we can write to the file
    If Err.Number <> 0 And try <= maxtry Then 
        txtStrm.Close 
        Goto tryagain100 
    ElseIf Err.Number <> 0 And try > maxtry Then 
        answer = MsgBox("Error! Unable to open sales data file to complete sale. It is probably in use by another user. Would you like to try again.", vbCritical Or vbRetryCancel, "") 
        If answer = vbRetry Then Goto beginagain100 
        If answer = vbCancel Then 
            Call MsgBox("Sale cancelled - sale details have not been saved!", vbDefaultButton1 Or vbExclamation, "") 
            UserForm5.CommandButton1.Enabled = True 
            UserForm5.CommandButton2.Enabled = True 
            On Error Goto 0 
            Exit Sub 
        End If 
    End If 
    On Error Goto 0 
    For n = 1 To nosalesrows 
         
        wholeline = vbNullString 
         
        For m = 1 To maxsdcols 'work our way through along the sales data row converting the data to .CSV format
             
            If Sheet2.Cells(n, m) = vbNullString Then 'empty cell
                wholeline = wholeline + "," 
            ElseIf IsDate(Sheet2.Cells(n, m)) And Sheet20.Cells(4 + m, 8) = "Date of Sale" Then 'date
                wholeline = wholeline + Format(Sheet2.Cells(n, m), "Short Date") + "," 
            ElseIf IsDate(Sheet2.Cells(n, m)) And Sheet20.Cells(4 + m, 8) = "Time of Sale" Then 'time
                wholeline = wholeline + Format(Sheet2.Cells(n, m), "hh:mm:ss") + "," 
            ElseIf IsNumeric(Sheet2.Cells(n, m)) Then 'number
                wholeline = wholeline + Str(Sheet2.Cells(n, m)) + "," 
            Else 'assume it is a string
                wholeline = wholeline + Chr(34) + Sheet2.Cells(n, m).Value + Chr(34) + "," 'we surround the string by "" to make sure that any commas in the string are included as text and do not cause us to skip to a new field, this is a quirk of CSV files
            End If 
             
        Next m 
         
        txtStrm.WriteLine wholeline 'THIS LINE OF TEXT CAUSES THE ERROR!!
     
   Next n 
     
    txtStrm.Close 
End If

 

[mps]

Solved it! After further trawling of the net it turns out that I had not opened the file as unicode. The following line of code:

Set txtStrm = fso.OpenTextFile(fname, IOMode:=ForAppending, Create:=True)

for unicode files should actually read:

Set txtStrm = fso.OpenTextFile(fname, IOMode:=ForAppending, Create:=True, format:=TristateTrue)

This stops the error on the writeline statement and also exports the cyrillic characters as unicode.

 

[Cindy Ellis]

Thanks for posting your solution to help others who might have the same problem!