Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

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 Video Tutorials

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.


Can someone tell me: what do I need to create an Excel format file?

I want to use Macromedia Flash to create the file.
Through another 3rd party tool: I can use Flash to create text files and possibly more advanced file formats.

Are there some basic constructs that if I make the file be... then I can simply make an Excel file?

Apologies if this is the wrong group to post in - I couldn't find anything more relevant on this forum.

Thanks.


OM


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.


Hello,

So, I tried opening an SQL file in Excel and it works fine. I am able to edit everything OK.

However, the problem comes when saving the file. If I save it in Tab Delimited format or even Unicode text format, it places a bunch of quotation marks all through the file rendering it useless. (I'm guessing that all the quotation marks render the SQL file useless, however I have not tried uploading it to my SQL db. Don't want to mess anything up on my website. )

So, is there a way to properly save a SQL file in Excel so that the saved SQL file is able to be used in an SQL db without any modification?

Thanks,
Dan


I create a file copied over another file.
The file is readonly so before I save I use below example to take off read only.
exp:

FileSystem.SetAttr "C:k2.xls", vbReadOnly = False

After I save the new file I tried

FileSystem.SetAttr "C:k2.xls", vbReadOnly = True

but this doesnt seem to put the read only back on.


Hello,

So, I tried opening an SQL file in Excel and it works fine. I am able to edit everything OK.

However, the problem comes when saving the file. If I save it in Tab Delimited format or even Unicode text format, it places a bunch of quotation marks all through the file rendering it useless. (I'm guessing that all the quotation marks render the SQL file useless, however I have not tried uploading it to my SQL db. Don't want to mess anything up on my website. )

So, is there a way to properly save a SQL file in Excel so that the saved SQL file is able to be used in an SQL db without any modification?

Thanks,
Dan


I am an electrical engineer and the company I work for has written this program to handle all of our electrical calculations. It creates a text file containing all of the data used in the program. My question is if there is a way to create a routine that will allow certain text from this file to be put in a cell. A short example of the text from the file is this:

"EMERG. PANELBOARD:","EHB1","60","MLO","60","35000"
"POWER-LINK W/GRD. BUS","480","277","3","4"
"","SURFACE MTD.","42","42","1","","","",""
"","DRY-TYPE","125","MLO","125","50000","","480","","3"
"3","125","250","5.1","0.10","3","","60","30","10"
"2.5","","","150 Deg","0.61","2.5","1070.6","5.1","500","30 in H./30 in W./20 in D."
"","","","","","","","","42"," 21"

I know that may be meaningless to some, but there is alot of power derived from that text statement. I would like to, for example, be able to place the text "EMERG. PANELBOARD" into one cell and "EHB1" into another. Each file follows the same format so I could just run the routine that would basically put this information into excel format. I know that is a mouthful and I am not really looking for the answer of "HOW" to do it at the moment. I am more interested in "IF" it can be done. I appreciate any input in this matter.

Nick


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.)


I have a macro on a few reports that copies the report data over to another spread sheet. This works great if I creat the new file as a .xls. Since everything is in 2007 now I tried to create the last on as .xlsx and I get the following error. Any suggestions or answers. Thank you



"Excel cannot open the file 'Dalhart balancing.xlsx' because the file format or extention is not valid. Verify that the file has not been corrupted and that the file format matches the format of the file."


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


I wrote an excel macro that saves the output as a .csv file
I need the file to be encoded in a "UTF-8" format which I have not
been successful doing.
I tried opening the newly created .csv file in NOTEPAD and saving it back in UTF-8 but that has not worked.
Thanks in advance any help or suggestions on how to do this.

- Tom


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




Hey All
I need some help

Aim – Create a Macro, which looks at the text file called “Test” which will be located within the file pathway C:\Temp, and manipulate the data into the format which is on the excel spreadsheet

I am hoping the example i have provided on the excel sheet, can give you a clear indication of the what i am trying to achieve

I am really hoping you guys will be able to help

Looking forward to your help

Regards
D

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.


Can someone help me with a code that will :
1) extract data from a excel file to create a formated text file with specific keywords in it

Excel Format:
PartID Location Date QTY
1 abs 1/1/2011 12

Text Format :
PRODUIT P L D NB
1 abs 1/1/2011 12
2) runs a DOS application with the created file. This application will generate some output


Thank You