save and close notepad document from excel 1

[LeighAnne]

I have been trawling around the internet for hours trying to work out this one, can anyone help?

I have written some code that transfers a set of data from an excel sheet into *.csv file. I would like to be able to open it in using Notepad and then save it as a *.csv with UNICODE as the 'Encoding:' type.

To open the document in notepad I have used:

Shell "Notepad.exe" & " " & "C:\MyDocs\test.csv"

I can close and save the document by adding 'vbhide' at the end:

Shell "Notepad.exe" & " " & "C:\MyDocs\test.csv", vbHide

Can anyone tell me how I can specify that it showed be saved with 'Encoding:' as UNICODE rather than the default ANSI?

 

[PHV]

Take a look at the OpenAsTextStream method of the File object of the Scripting.FileSystemObject object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[fumei]

Bring it into Word and save as unicode.

ActiveDocument.SaveAs FileName:="filename", Encoding:=7

7 is the constant for Unicode. You have flavours of Unicode (BigEndian LittleEndian) a well.

Gerry

 

[LeighAnne]

Fumei,

Can this code be used in excel? I'm having problems getting "activedocument" to be recognised and having difficulty finding an alternative.

PHV,

I don't realu understand where I use OpenAsTextStream, do I have to always create a file first?

REgards,

Le

 

[LeighAnne]

PHV,

Thanks for your help! Did some more reading and solved it using OpenAsTextStream as a great starter!

Regards,

Le

 

[fumei]

No, the code is for Word. What I was sggesting was, instead of using Shell to have NotePad open the the file, use Word to open the file, and save it as UNICODE. From Excel, you would create an instance of Word to do this.

ActiveDocument is a property in Word, not Excel.

However, TextStream is a viable alternative, and if it works for you, great.

Gerry