0

I'm trying to export a Cell to a Text file. but the Cell also includes Emotes.

This is a Test

I have tried the following simple VBA Script (mapped to a button):

Sub CelltoFile()

Dim myFile As String
Dim strTemp As String

myFile = "C:\tmp\test.txt"

Range("O17").Select

strTemp = ActiveCell.Value

Open myFile For Output As #1

Print #1, strTemp

Close #1
MsgBox ("The data has been exported to txt!!!.")
MsgBox strTemp

End Sub

But when testing, all the Emotes have been replaced with Question Marks.

??? This is a Test ???

I have a similar script that Copies the Cell to Clipboard, and this works - in respect that it includes the emotes when I manually paste into a text tile. However, I have also tried using this script to "GetFromClipboard" and save into a text file, but I get the same results as above.

Any Help? Ideas? Can it even be done?

EDIT:

Thanks to Panagiotis Kanavos for that answer.! Your a super Star

For reference (if someone should stumble across this), the final code I used is this:

Sub CelltoFile()

Dim myFile As String
Dim strTemp As String
Dim fsT As Object
Set fsT = CreateObject("ADODB.Stream")

myFile = "C:\tmp\test.txt"

Range("O17").Select

strTemp = ActiveCell.Value

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 strTemp
fsT.SaveToFile myFile, 2 'Save binary data To disk

MsgBox ("The data has been exported to txt!!!.")

End Sub
  • Don't use the ancient file functions for text. I doubt they were ever updated to work with Unicode. The File Scripting Objects are used to write files since before 2000 (not a typo). By 2002 VB (and VBA) were essentially abandoned as .NET was introduced. This won't work on a Mac. In this case you'll have to use the ADODB.Stream object – Panagiotis Kanavos Jan 12 '22 at 12:25
  • As Panagiotis Kanavos writes, use the FileSystemObject to write the file. Note that `strTemp` will hold the correct characters, but `MsgBox` (or the immediate window) will not be able to show the content correctly. – FunThomas Jan 12 '22 at 12:35

0 Answers0