Passing other language data into text file through excel VBA

Question

Problem A

I have other language data which I am trying to print it in debug window however it excludes the special character

For eg :

"Aragac̣otn" is taking as "Aragac?otn".
"Geġark'unik'" as "Gegark'unik'"

In short what can be the appropriate data type for this? I tried with string/variant but no success.

Problem B

My ultimate goal is to pass it into txt file. when I am trying to pass the value "Aragac?otn" it throws error. (Invalid Procedure call or argument)

I am using FileSystemObject to this.

Here is image of Problem A

The VBE's Immediate window cannot display those characters. That does not mean that you have collected them incorrectly. The same holds true for a prompt thrown into a MsgBox. If you plan on building a TXT out of the results, you will likely have to use UTF-8 encoded though. The string type vars should be sufficient. Try putting them back into the worksheet in a different location. — , Oct 29 '15 at 07:32
@Jeeped actually I would like to build .sql file out of it. Not exactly txt. I am not sure how to make UTF-8. Can you please help me out — pranav012, Oct 29 '15 at 07:37
You can build an XLSX for import or use write to SQL directly. — , Oct 29 '15 at 07:39
@Jeeped I am writing it to SQL using filesystemobject. It throws error as stated in Problem B — pranav012, Oct 29 '15 at 08:24
The phrase *"writing it to SQL"* makes no sense to me. I don't even know if this is MS SQL, mySQL or some other derivative. Maybe [this](http://stackoverflow.com/questions/2524703/save-text-file-utf-8-encoded-with-vba) will help. — , Oct 29 '15 at 08:51
Possible duplicate of [VBA Output to file using UTF-16](http://stackoverflow.com/questions/9092548/vba-output-to-file-using-utf-16) — GSerg, Oct 29 '15 at 09:03
@Jeeped I meant I am writing it to .sql file (extension). Anyways UTF - 8 resolution should work. WIll try and let you know. Thanks a lot — pranav012, Oct 29 '15 at 09:26

score 0 · Answer 1 · answered Oct 29 '15 at 08:58

Try this below code. It will take values from excel and paste it in txt file.

Sub try()
Dim A1 As Variant
Dim A2 As Variant
Dim sFileName As String
Dim fsT As Object

A1 = Worksheets("Sheet2").Range("A1").Value
A2 = Worksheets("Sheet2").Range("A2").Value


sFileName = "C:\Documents and Settings\vnpasump\Desktop\test1.txt"


Set fsT = CreateObject("ADODB.Stream")
fsT.Type = 2 'Specify stream type - we want To save text/string data.
fsT.Charset = "utf-8" 'Specify charset For the source text data.
fsT.Open 'Open the stream And write binary data To the object

fsT.WriteText A1
fsT.WriteText A2

fsT.SaveToFile sFileName, 2 'S


End Sub

Passing other language data into text file through excel VBA

1 Answers1