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

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 ...
Pass Values from One Macro to Another Macro
How to pass variables and values to macros. This allows you to get a result from one macro and use it in another m ...
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 ...
Simple Excel Function to Combine Text with a Separator
Excel function that makes combining text very easy. This function is simpler and better than the CONCATENATE functi ...
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 ...

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




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,

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


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.


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.


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?




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 am a VBA newbie trying to make my job easier.

I currently have a file in the following format
AAA BBB CCC DDD EEE FFF GGG 1
2
3
4
5
6
7

How would I writer code to create an excel sheet that looks more like this:

AAA 1
AAA 2
AAA 3
.
.
.
GGG 7

All I want to do is convert the grid format to more of a flat file format and don't know how to do this. I would like the code to be dynamic enough to figure out how many rows and columns I have.

Thanks,
Seth


Here the Issue with no answer so far:

I have several large CSV files I need to work with. File source is Oracle (10g) Query Exports to Comma Separated Values data file.
I need Vlookup capability and Pivot Table functionality when working with these files. I receive new data files each business day.

Rather than open each file, convert to Excel 2007 xlsx format, set up pivot tables and add VLookups each time I get a new file (daily), I want to create a pivot table that connects to the raw CSV file via Data Connections. When I try to connect via Excel 2007 Data Connections I am receiving a message that I can't create a pivot table using data in a csv format.

Is this correct? Or am I missing an Add-In? Any suggestions on how to do this?

Attached is a sample data file (limited to the first 100 records) - however, the data is in xls format which makes the operation possible.
It doesn't work with the csv file.
the exact message I get reads:
The type of connection selected cannot be used to create a Pivot Table.

Thanks for your insight.
joanna.


I have a macro that runs in excel. I would like to be able to run this as an executable.

Right now the macro reads off values in the excel file and creates a text file capable of importing into another program for formatting reasons. Here is what I would like to do.

I would like to create an executable that when ran, does the following

1. Opens or simply reads the created excel file through the Microsoft common dialog
2. Performs the code written to read the values in the excel file and create the text file.

Basically I need a way of associating the macro code in a stand alone executable environment. I can get the common dialog interface to run and select the excel file, but I don't know how to get the code to work outside of the macro.

example code from part of the macro.

Range("C2").Select
topmask = ActiveCell.Value

Obviously the macro knows what excel file, sheet, ect I am referencing since it is opened with the excel file. I need help associating the file chosen through the dialog with this code. Help!!!!


Hi,

I'm trying to import an XML file into excel to format for a colleague unfortunately excel is unable to open the file stating :

XML Parse Error
Reason : A semi colon character was expected
Line : 313
Column : 94
File Offset : 13226

This XML file is generated each month via google analytics so will re generate this problem every time. I'm only looking to extract and format the text of the file so hoped there was a way I can do this.

Is there a way to do this via excel or can I use excel to open the file via notepad and then copy the text ?

Any advise or suggestions would be appreciated as I've never worked with XML before and am not sure where to start !


Hello everyone.

I've created a spreadsheet to help me create a text file which I can use to inport values into another device. Using CONCATENATE etc I end up with a range of cells (1 column / Many rows) that I can then MANUALLY highlight, choose copy, open the text file, paste in the contents and then save it. This works fine but as you can imagine I'd like to automate the proceedure by having excel create and save the text file for me.

From what I've seen on these forums most people are wanting CSV type files but not me. I've already got the cell contents pieced together it just needs writing to a text file, no delimiters, no quote marks, just exactly what's in the one cell on one line, it's just one column remember (H2:H1456).

I'm not bothered about appending data either, a hard coded overwrite of the text file is fine by me.

The actual file type I ultimately need is an .abk file, but like I say this is just a basic text file that's been renamed. Could Excel create this or must it have the .txt file extension?

If anyone could help with the code I would be most grateful as most of my VBA is learn from re-engineering macros, but I can't seem to do this one myself.

Many thanks

Brelin


Is it possible for Excel (any version) to open a tab separated text file
saved in UTF-8 encoding? I have big problems showing swedish characters like
åäö in Excel. If i open the text file in notepad and saves as Unicode (not
sure what Unicode means here but I guess it is UTF-16...) Excel seems to
present characters correct.

Regards
/Hans





Hi,

Is there a way I can convert dates to text in a CSV file (opened via excel) using a macro?

Currently I have excel recognised dates in the first column of the format dd-mm-yyyy and a corresponding values in the second columns.

I know once I have the file open I can enter the following formula in an adjacent coloum "=text (A2, "MMM-YYYY") but I need to change the format in the existing date feild and not create a new coloum. Sure I could copy and paste the new formatted dates into the cell, but in terms of what I need to do, this is not practical as I several of these CSV files that I need to query and extract data from daily using a macro.

The macro is not currently working because the date format is wrong. I need it to appear as MMM - YYYY. When you view the date in excel it must appear like this (as it would appear in say microsoft word) and not with the underlying date format.

Many Thanks

Elvis


I am new to VBA and trying to create a macro that will ask for a date using an input box (format mm/dd/yy), then make a copy of the last file used in the folder since separate files are created for each month, and renaming the file using the date that was entered in the input box with additional text of what the file is (ex. 03.31.07 Excel file.xls) I have the code for the input box but can't figure out the rest. Can anyone help me??

Thanks!


I need to create an excel template with extension .xltx.

The following code creates an xltx file. But when I open, I get an empty excel application with an error message "Excel cannot open the file 'NewTemplate.xltx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file."

code:
/////////////////
workbook = workbooks.Add(System.Reflection, Missing.Value);

workbook.SaveAs("D:\\NewTemplate.xltx", office2007.XlFileFormat.xlTemplate8, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, false, office2007.XlSaveAsAccessMode.xlNoChange, System.Reflection.Missing.Value, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);

workbooks.Close();
excelApplication.Quit();
/////////////////////////

What is wrong with this code?
Any help is very much appreciated.


Hi,
when I save the file, excel creates tmp file in the same dicectory as the
original file is placed. Everytime i click to save this document, it creates
another tmp file.
When I leave Excel application, tmp files are not deleted.
Is it possible to solve this problem?





I am trying to create a MSDOS CSV file anytime the user saves a spreadsheet (i.e. creates a duplicate copy of the data but in a csv file). I used the following command:

ActiveWorkbook.SaveCopyAs "c:\test.csv"

This would allow me to save a CSV file without "modifying" the open workbook\worksheet. But when I attempt to open the csv file in Access, I get garbage. I determined that this is probably do to the file being saved as an "Excel csv" file? What I need is the MS-DOS file type.

The reason behind using the first command was to make sure the user doesn't "know" the file is being created.

Thanks for any help you all can provide.


Hi Folks,

I am dynamically generating and naming an xl file based on some conditions. The use the following code-

Dim newFile As Long
newFile = FreeFile()
If FileName "" Then
Open "C:\imacro_files\" & FileName & ".xls" For Binary Access Write Lock Write As newFile
Close newFile

Application.Workbooks.Open FileName:="C:\vba_files\" & FileName & ".xls"

The problem here is if I try to save and open it as xlsx it gives an error like- Excel cannot open the file because the file format or file extension is not valid. Make sure file extension matches the format of the file.

Next, if I go ahead with using .xls and then format a column to have zeros appended to it the formatting just doesnt work. I get an error like- The file is Text (tab delimited file) and so it cant support the formatting. Now I not sure how do I handle this as I absolutely need to format the column. I tried using all kinds of functions and also storing it as a string but nothing works. All formatting is just removed and at the same time it doesn't even let me open it if in .xlsx format (as stated initially).

So, basically it would help if I could get either problem solved-
1. Be able to save and open the file as .xlsx
2. Be able to format the text tab delimited file or prevent the file from getting saved as text tab delimited
3. Any other workaround?

PS: The funny thing is though I get an error about the file being text tab delimited it still shows the format as .xls (Microsoft Office 97/2003)
Also, it might help to know that in the orgin file I am importing data from an xml file. From this origin file data is then transferred to this new dynamic file.
xml -> xlsm --> xls
Any inputs will be much appreciated.

Thanks
Prajakta


Hi folks,

I have attached a file which throws this error when someone tries to open it "Excel cannot open the file 'a.xlsx' because the file format or file extension is not valid. Verify that the file has not been corurpted and that the file extension matches the format of the file." I will appreciate if any one can get me data from this file or suggest some tools to recover this file. Thanks in advance.




How to save xls/xlsx file as csv with utf8 unicode? When I have little files, I use openoffice and all works ok, but when file is big (more than 100 000 rows) then openoffice can not open file?

So question is: how to save excel file as csv with utf8 unicode? Self MS excel can't do this? if no, what is better way for doung this for big files?

I find the script below online which will save a xls file as a csv file.
However, my file has Japanese and Chinese Character and all that Character got lost. Right now we have to do it manually on 10 to 15 files to get this process done. We would open the xls file save it as uicode text file on excel and then open up notepad save the unicode text file as UTF8 file. So is there a way (perhaps similar to the idea as the script below I can save it as a UTF8 tab delimited file??)

if WScript.Arguments.Count < 2 Then
WScript.Echo "Error! Please specify the source path and the destination. Usage: XlsToCsv SourcePath.xls Destination.csv"
Wscript.Quit
End If
Dim oExcel
Set oExcel = CreateObject("Excel.Application")
Dim oBook
Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))
oBook.SaveAs WScript.Arguments.Item(1), 6
oBook.Close False
oExcel.Quit
WScript.Echo "Done"


I have a CSV File in Japanese which is encoded in UTF-8 encoding.

If i try to open in Notepad , the data is displayed properly
if i open the file in Excel (Office 2000) , the data is not displayed properly

Can you pls tell me how to open this CSV file in Excel.
Is there any setting in excel to support UTF-8 encoding.