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 ]
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