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

mps

New Member
Joined
Feb 7, 2011
Messages
42
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 ]

Code:
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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:

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

for unicode files should actually read:

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top