Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



How To Create A Utf-8 Text File From Excel Vba

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

HI,

How do I create a UTF-8 encoded file in excel VBA?

I have tried using filesystem obeject with TriState property to true. This creates the file in Unicode format but not in UTF-8 format.

Thanks

View Answers     

Similar Excel Tutorials

Export an Excel File to a CSV File
How to export an Excel file to a CSV file (comma separated values file).  This allows you to turn any Excel spread ...
Excel Function to Remove All Text OR All Numbers from a Cell
How to create and use a function that removes all text or all numbers from a cell, whichever you want. To do this, ...
Make Your Macros Available in All Workbooks in Excel
In Excel, you can make it so all of your macros can be easily accessed by any Excel workbook. To do this we need t ...
Simple Excel Function to Extract a Word or Text from a Cell
Excel function that makes it easy to extract a word or text from a cell in Excel. This is a single function! It doe ...

Helpful Excel Macros

Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Save the Current Worksheet as a New File in the Current Folder
- This Excel Macro saves the currently visible worksheet into the SAME folder as the current file. It is different from
Open any Program from Excel
- This free excel macro allows you to open any program on your computer from excel. You can open a media player, file vie
Create a 12 Month Calendar With The Current Day Highlighted in Excel
- Create a 12 month formatted calendar on a new tab in Excel with the current day highlighted. This macro creates a nicel
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth

Similar Topics







I have been trying to determine the format Excel 2002 uses when you save a file there as unicode (I am on XP). When selecting save as "unicode," is it saving in UTF-8 format or UTF-16 format? I have been told I must provide a unicode file in UTF-8 for use by another program.
Any guidance provided would be appreciated.


We have an Access database that has VBA code to export the data in the
tables to a CSV file. We have been using Excel to open these files and
it recognized all the columns correctly including the ones that had
commas because the file put double quotes around all the text in that
column. We recently started using File Scripting objects to create
Unicode files in the same CSV format. Now when you open up the files in
Excel everything appears to be in one column. You can use text to
columns, select comma as delimiters and double quote " as a text
qualifier however it does not always recognize the double quotes and
creates two columns when there should be one. Here is an example of two
rows from a file the first will be formatted correctly the second will
not.

00251736889001,Austria,20.00 % Tax,1,EUR,1.54,1.54
"103425,103422",Austria,20.00 % Tax,1,EUR,5.02,5.02

The second row will create two columns one containing 103425 and the
other containing 103422. Prior to using Unicode files this was not a
issue. The files opened up in Excel and were already in the proper
column format. Since we went to Unicode we now have the extra step of
doing text to columns. Does anyone have any ideas as to what we could
do to resolve this.

Doug




Does anyone know what format is used when you save an excel file as "unicode." I am using excel 2002 on XP.

I have been asked to provide a UTF-8 formatted unicode file for use by another program but I am not sure which formatting excel uses.

Thanks in advance for your kind response.


Hi there. I maintain a tool that reads text files. I recently got this message from a user. The file is now "UTF-16 (Unicode) format instead of UTF-8." Now the text files have a chr(255) and chr(254) at the beginning of the file that throws off the fixed column length of everything else on the record. I currently open the file as Open Input. Should I change it to use Open as Binary or something like that? I have never run into a text file like this and am wondering if VBA has a way to interpret these files...? Any advise would be appreciated.


Hi Alll

I am using Excel 97 SR 1 Japanese version (OS is Windows XP SP2 japanese)

My problem is Excel 97 cannot open Unicode file (.csv or .txt)
The file cotains Japanese as well as English characters. But when i change it to ANSI it open properly. The file can be opened with other programs like open office or Excel 2003



It gives me an error when i try to open it with Excel(in Japanese)

Meaning in English for Error Contents:
The form of this file cannot be recognized.
Please click the cancellation, and open at the file made by other programs that cannot be read with Microsoft Excel by the application that makes this file. Please open after it preserves it by the file format that can be read with the Microsoft Excel of the text form etc. when opening with Microsoft Excel.
When help is clicked, information on the method for straightening out that problem is displayed when there is a possibility that the file is broken.
OK must be clicked, and the text import wizard must click completion when you display the text that exists in the file. Please answer

When i click Ok the document is opened but certain Japanese characters appear a junk and the .csv file is opened with comas (,)......


I want to know whether Excel 97 has a problem with Unicode format because it opens the same file when file is ANSI.


Please suggest on this..... I could not find any article or relevant information that Excel 97 has such a problem...


Thanks in advance .....


Hi,

I need to convert a unicode file in unicode text. Can any one help me in performing this conversion. I am not aware what's the difference between unicode file and a normal text file.

Rgds,
Ashish


I have a problem that is most likely not possible to solve by a macro, but I will ask the question since excel seems to have endless possibilities, so who knows, I might be lucky

I have several web links in an excel document that a few hundred people in the organisation use everday. The user click on an link, and the browser opens and display the webpage.

The thing is that the webpages have characters that will not be displayed properly unless they are encoded with Unicode (UTF-8). So the users will be confused if the characters are displayed incorrectly. Not everyone understands that they need to press F5 in order to refresh the webpage so it will be encoded correctly.

So, the question I have is if it is possible to create a macro, or change a setting in excel, that in a magical kind of way forces the Web browser to display the webpage in Unicode (UTF-8). So when the user clicks on a link in the excel file, excel will send the information to the browser. We use IE6 in my organisation as default browser. I know that IE7 is better in selecting the correct encoding.

I have tried to change the setting in the excel file under options/general/web options/encoding to UTF-8, but that did not solve the problem. I guess that setting has only to do with if the excel file itself will be displayed in a browser.


Hi Guys

Not sure whether this is the right place to post this or not. I apologise if its not.

I'm trying to create datasource for a word file to look to an excel file, dependant on the filename of the particular excel file. For example

If the excel file was called 1.xls then the datasource would be created based on that file. All this would be done from within Excel.

The final output I am trying to achieve is that you press a button from witin excel, then this goes and creates a new word file connecting it to a datasource created based on the excel file its reading from and then creates a mail merge based on the data in that excel file.

Hope somebody out there can help with this problem.

Thanks

Matt


Hello,
I have searched this site for the answer, but could not find any.
I do have a excel **** with Russian text. I am trying to safe it as a Text (tab delimited) format. Yet, I am getting warning that "file may contain features that are not compatible with Text (Tab delimited). If I proceed and safe the file than all I can see in notepad is question marks (?) instead of text that I have saved. I understand that the problem is with encoding. Normal English uses ASCI encoding and Russian language probable designed for UNICODE. In fact I can safe it as a text (UNICODE), but I really need Text ( Tab delimited).
Any ideas on how to do it, so I will not loose my data?


Hello,

In my excel file I have chinese and some other special characters and many cells have text with commas.

My problem starts when I try to save my file as a Text.
When I Save As with the Unicode option I can see the chinese characters in my text file but also I see a lot of quotes """ because I have commas in my xls file.
When I Save As with Tab Delimited option to solve the comma problem, the chinese characters become ????

It seems that cannot be possible to do it manually cause there is no option to Save As with Unicode and Tab option together.

So I would deeply appreciate someone could give me a solution with VBA code to save my file as Text with Unicode and Tab Delimited option.

thanks in advance for your kind help.
regards,

PS. I searched the forum but I haven't found a situation similar to mine.


When I go to create and save an Excel file, of what character encoding format is being used for that file? (i.e. ANSI, UNICODE, etc.)


Hi Guys

Not sure whether this is the right place to post this or not. I apologise if its not.

I'm trying to create datasource for a word file to look to an excel file, dependant on the filename of the particular excel file. For example

If the excel file was called 1.xls then the datasource would be created based on that file. All this would be done from within Excel.

The final output I am trying to achieve is that you press a button from witin excel, then this goes and creates a new word file connecting it to a datasource created based on the excel file its reading from and then creates a mail merge based on the data in that excel file.

Hope somebody out there can help with this problem.

Thanks

Matt


Hi,

I have a list of saved queries which import one table of data from an external website.
An example of the text from one of the queries.

Code:

web
1
http://www.gbgb.org.uk/RaceCard.aspx?dogName=Doc Ricketts

Selection=5
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False


This query runs fine if the saved query .iqy file is created either with my Excel application, or even if I create a new text file then change the file extension to .iqy.

However, when I create the .iqy file from a VB 2010 application I am building, when I run it Excel returns an error message - 'The address of this site is not Valid. Check the address and try again.'

The text within the .iqy file is exactly the same regardless of the method of creation and I can't understand why it returns an error for only one of them.


This is the VB code I am using to create the .iqy file.

Code:

Dim DogQuery as String
Dim Runner as String
Runner = "Doc Ricketts"
DogQuery = "C:\Betfair\Q\1.iqy"

My.Computer.FileSystem.WriteAllText(DogQuery, "web" & vbCrLf & "1" & vbCrLf &  "http://www.gbgb.org.uk/RaceCard.aspx?dogName=" & Runner & vbCrLf, False)

My.Computer.FileSystem.WriteAllText(DogQuery, vbCrLf & "Selection=5" & vbCrLf & "Formatting=None" & vbCrLf & "PreFormattedTextToColumns=True" & vbCrLf & "ConsecutiveDelimitersAsOne=True" & vbCrLf & "SingleBlockTextImport=False" & vbCrLf & "DisableDateRecognition=False", True)



The only clue that I have of any difference between the failing and successful .iqy files is that when I look at the file properties the failing one is two bytes larger than the successful one.

I have even tried creating a working query, removing all the text from inside the file, then appending the text from VB 2010 into the file. But this still fails.

Any and all help would be greatly appreciated.


Hi, I need to create something that takes a sheet or range in Excel and creates a specifically formatted text file.
Can you please advise how can I set following particular position in Excel and then export in text format with the following particular location and spaces etc should not be changed after exporting in text file. Please help and advise. Thanks.

My data is:
Column 1 - Employee Number, position 1-10
Column 2 - Surname , position 11-30
Column 3 - SSN, position 40-48
Column 4 - Designation, position 55-67


I used Access to produce an .xls file (call it June2011.xls) with three sheets in it (call these NoFunds, Formatted, and FundNames). I need to create a module in Excel that creates more sheets (pivot tables, summaries, etc.) that read from the Formatted sheet as well as format the data so it's easier to read.

The way I see doing this, unless someone has a different solution (such as something I should do in Access), is to create another Excel file that's code imports June2011.xls and then continues to manipulate the spreadsheets for the desired results.

So how does one Excel file import sheets from another Excel file?

Thanks,
Alex


Hi all,

I have been searching all over to resolve this problem and cannot find any help.

I have creates an Excel Object in VFP to access a Excel file but VFP dose only reads Excel 5.0 format. So I then save the excel file to the new format.

This works well I get a popup which the user has to select "No" for the Excel to save in the required format.

This is the message:
Saving this workbook's VBA project in Excel 5.0/95 file format requires a component that is not currently installed. Do you want to save in the latest Excel format? ...etc

As I said, the answer will be "No" but what parameter would I use to do this.

SCRIPT:

x=GETFILE("xlsm","Select Agent excel file","Select")
oExcel=CREATEOBJECT("Excel.Application")
oExcel.EnableEvents=.f.
oExcel.workbooks.open(x)
SET SAFETY OFF
NewXLS="L:\ExcelToSun\VFP\new.xls"

IF FILE(NewXLS)
DELETE FILE &NewXLS
ENDIF

SET SAFETY OFF

oExcel.WorkBooks(1).SaveAs(NewXLS,39) &&& Save the excel file in a Microsoft Excel 5.0/95 Workbook format
oExcel.quit()

Thanks very much for your help in advance.


I would like to save a sql script that I have worked on Excel Sheet as text file (Unicode).

Situation: If I copy and paste this sheet in SQL Server Analyzer or notepad it is coming right.

But if I try to save as Text (Unicode) or (DOS) text, some " (double quotes) get inserted in many parts of text file.

Does anyone know how to solve this problem?

Thanks,
Mark




Friends , i need a help.

I attach a sample

I have a work sheet containing two columns
Column A - File Name
Column B - Data in Unicode
I want each row in Column B to be saved with the FIle name in respective file name in A.

A1 -> A_1
B1 -> Unicode TEXT

A1.TXT -> Text file containing the B1 content.

I don't have problems with ASCII Text - i had pasted the macro also in the work sheet itself for reference.

Also the DOUBLE QUOTES appear in the text file.

My data sample provided is single line - by actual data is multiline text in B1.

The macro should convert all the content [~10000) in B1 as individual file names using the FILE NAME given A1.

Need assistance.



Please Login or Register  to view this content.



In Office 2003 (and I suppose it's true for Office XP and 2000):

When we save an Excel file to CSV file, Excel doesn't ask if we have to care
about encoding, in particular, I wanted to save Unicode in UTF-8 but I can't.
This is very annoying for multi-national companies which has Excel files
coming from different part of the world.

On the other hand, I tried to edit a CSV to add some Chinese characters in
it and save it in UTF-8 and double-click to open it. Excel is then launched
but it doesn't care file encoding either. The only way to get around
"opening" is to change the file extension from .csv to .txt. Now, if we open
this text file, a wizard will present us the option to choose the file
encoding!!!

Only a little change in file extension and the behaviour is changed. What a
pity that Excel doesn't care about UTF-8.



I password protected an important file. My husband downloaded the Office
2003 upgrade last night. Now when I try to access the file I get this
message...This is an Excel file, I don't know why it looks encrypted or why I
can't access it.

This file is not in a recognizable format.

If you know the file is from another program which is incompatible with
Microsoft Office Excel, click Cancel, then open this file in its orginal
application. If you want to open the file later in Microsoft office Excel,
save it in a format that is compatible, such as text format.

If you suspect the file is damaged, click Help for moare information about
solving the problem.

If you still want to see what text is contained in the file, chlick OK.
Then clieck Finish in the Text Import Wizard.



Hi,

Wonder if anyone can help.

I have a excel file with english terms, which I sent to translators to translate into Tamil language.
When the file comes back, it shows rubbish characters:
e.g.:
Col A: Col B:
Acting nFtftlf

I was told to install a mylaiplain font set on my PC to be able to view it. I did it and is fine.
However, I'm actually using the terms in this excel file to be import into a system/server, which will publish the words on html web page. But with the import, it does not work - on the system end, it does not show the Tamil characters.

I was told that I need to save the file and preserved it's unicode. I'd like to ask how to do that. Cos, if I told the translators to click save as and select the file type as unicode, it will be saved as a *.txt file. However, the import process into this server requires a .csv or .xls or any properly delimited file.

can someone advise how can the unicode be preserved in the excel file? The software that the translator used is called mylaiplain.

Please help..


For some reason my .xla add-in file containing forms and code (saved as an .xla when created) has now saved itself as a .xls file. I cannot open it as a .xls file via Excel and re-convert to a .xla file format and I cannot save it as a .xla in the VB editor. I cannot seem to change the file extension to .xla in explorer either... Anyone know why this is happening and how I can get it back to an .xla file format?


Hi,

I am fairly new to creating macros in excel & recently I picked up a macro online a Chip Pearson macro from http://www.cpearson.com/excel/ImportBigFiles.html. Basically I need to convert a large text file into excel delimited by comma. The macro works fine, except the date format displayed in a column converts into various formats. If it is dd-mm-yy format it displays correctly, but if in dd/mm/yy it converts as mm/dd/yy & sometimes converts as text only. I basically cannot understand why that is the case, as the text file is in the format dd-mm-yy or dd/mm/yy. Why does this macro transpose the day & month field & also have text & not date format?
I have tried to correct this, manually takes a lot of time & prone to errors. I also tried, with my limited knowledge, to create a macro, unsuccessfully. This is very frustrating as I need to complete the conversion urgently

Any help will be appreciated.

Thanks

Attached is a sample of the text file used.




How to create a excel file with 0 KB ? Because when the user creates a new excel file, it is created with 9 KB by default. But when creating a word file its created with 0 KB. Why its happening ? I want to test uploading a excel file with 0 KB.. Can any one please help me on this .?

Hello,

I have an xls file which is 500kb in size and after a small change it becomes 1MB size. Why is that?

Details:
1. From MySQL DB I export a DB table with the option: Export to Microsoft Excel 2000 as xls (not zip) format.
2. I open the file with Excel 2003. I mentioned a 'strange' thing - the cells are formated with 2 different font types - Arial and Arial Unicode MS, Size 10.
3. When I select all the cells with data in them and format them as Arial 10, or give them a border and click save the file becomes from 500Kb to 1Mb. If I format with border/font only several cells and click save the file size doesn't change. I have 480 Rows and A:Z columns in the file and only 1 sheet.

Why the file size changes so much?