Excel plugin to quickly export selection to text file (csv, tab-delimited, etc.)

[superboyac]

In Excel, whenever you want to convert your data to a text file (csv, tab-delimited, whatever) it asks like 3 questions every single time and it gets really annoying.  You have to do a "save as" and pick the format you want to save it as, then it tells you like 2 times saving it will remove the formatting (duh!).  So freakin annoying.  The worst part is that afterwards, the actualy open excel document is now the exported text file (because it was done as a "save as" it assumes you want to change the current document to the new one).

Is there a plugin or something where I can just highlight a group of cells and export it to a text file?

[bjk]

I'm not sure if it is a pefect solution to your problem but you may look here:

http://www.asap-utilities.com/asap-utilities-excel-tools-tip.php?tip=197&utilities=Export.

I think that the ASAP tools are indispensable for everyone working with Excel. Besides this, these tools
http://www.excel-extools.com/ are very handy, too.

[AndyM]

Try putting this in your personal.xls.  It will save the current .xls file to a .csv file with the same name in the same directory, close the .csv file and re-open the .xls file.

If you do this often, it might be worth assigning the macro to a button, put it on a menu, or assign it to a shortcut key.

Code

Sub SaveCSV()

'Might have to have Tools>References>MicrosoftScriptingRuntime checked

Dim fnamexls As String
Dim fnamecsv As String

fnamexls = ActiveWorkbook.FullName
fnamecsv = Replace(fnamexls, ".xls", ".csv")

    ActiveWorkbook.SaveAs Filename:=fnamecsv, _
        FileFormat:=xlCSV, CreateBackup:=False

ActiveWorkbook.Close SaveChanges:=False
Workbooks.Open Filename:=fnamexls
 
End Sub

(just realized it might discard any changes you made to the .xls file, so either save that first, or add a line in the code to save the .xls file)

I'm no programmer, so ***use this at your own risk!!!***

[superboyac]

I'm not sure if it is a pefect solution to your problem but you may look here:

http://www.asap-utilities.com/asap-utilities-excel-tools-tip.php?tip=197&utilities=Export.

I think that the ASAP tools are indispensable for everyone working with Excel. Besides this, these tools
http://www.excel-extools.com/ are very handy, too.

Awesome!  That is so cool.  What would I do without you guys?

[AndyM]

I didn't read your original post carefully enough.  You want to save
only the highlighted selection to a new .csv file, and the macro I
gave you saves the entire sheet to a .csv file.

This will save the highlighted selection to a .csv file with the same
name as the original .xls file, and will reload the original .xls
file:

Code

Sub SaveSelectionCSV()

Dim fnamexls As String
Dim fnamecsv As String

fnamexls = ActiveWorkbook.FullName
fnamecsv = Replace(fnamexls, ".xls", ".csv")

Selection.Copy
Sheets.Add
ActiveSheet.Paste

    ActiveWorkbook.SaveAs Filename:=fnamecsv, _
        FileFormat:=xlCSV, CreateBackup:=False

ActiveWorkbook.Close SaveChanges:=False
Workbooks.Open Filename:=fnamexls
 
End Sub

In either case, be careful you are not *starting* with a .csv file.  If you answer "yes" to "do you want to overwrite the current file?", that's exactly what will happen.

On the other hand, if you are starting with an .xls file and you already have a .csv file of the same name, you can decide whether or not to overwrite the old .csv file as you see fit.

[superboyac]

Thanks AndyM, but the asap-utilities mentioned above were perfect for my needs.  They add an enormous amount of funcionality to Excel.

[AndyM]

Thanks AndyM, but the asap-utilities mentioned above were perfect for my needs.  They add an enormous amount of funcionality to Excel.

I couldn't do without ASAP utilities (new version just available).  Keep in mind that the functions in ASAP can be used in macros as long as ASAP is active.

[superboyac]

OK, here's another problem I just ran into.  I'm using a bunch of Unicode (UTF- characters in my excel file.  It looks fine in Excel, but when I export it as a tab-delimited file (using excel's "save as" or ASAP utilities) the text file replaces all of the unicode characters with "?".  Is there a way to have a tab-delimited text file with Unicode symbols in it?  If so, is there a way to convert from excel into a unicode text file?

[superboyac]

OK, I found a workaround for this, if anyone is interested:

In ASAP-utilities, if you go into the export selection dialog, there are several options.  One of them is to export to a tab-delimited file, another is to copy the selection to the clipboard as a tab-delimited.  When I exported to a tab-delimited file, the Unicode characters showed up as "?", which didn't work.  When I copied it to a clipboard and pasted it into a text-editor, the Unicode characters showed up fine.  Weird.  They should fix that...whoever "they" is, I'm assuming Excel.

[Curt]

superboyac;
I took the liberty to write the author of ASAP, quoting your first post as if it was my problem. Of course that is not the right way to do it, but I did. The answer came today:

Hello Curt,
Thank you for your email.

This is an Excel-specific problem, by default Excel converts all unicode characters to question marks (?) when you save the file in text format.

In Excel however there is also the option to save the file as a tab delimited text file (Save As and then choose Unicode Text). I will incorporate this in the new version of ASAP Utilities. Can you perhaps send me an example file I can test it with? I just ran some tests with russion text and that worked okay.

Kind regards,
Bastien Mensink
www.asap-utilities.com

If you will "answer" this mail by sending an Excel file with lots of Unicode characters to bastien at asap-utilities com, please - or perhaps send it to me, curt at webspeed dk? I have never used Excel myself, it was just part of this Office thing I thought I would need...

Some Unicode characters from my part of the world: æøå ÆØÅ ß

[superboyac]

superboyac;
I took the liberty to write the author of ASAP, quoting your first post as if it was my problem. Of course that is not the right way to do it, but I did. The answer came today:

Hello Curt,
Thank you for your email.

This is an Excel-specific problem, by default Excel converts all unicode characters to question marks (?) when you save the file in text format.

In Excel however there is also the option to save the file as a tab delimited text file (Save As and then choose Unicode Text). I will incorporate this in the new version of ASAP Utilities. Can you perhaps send me an example file I can test it with? I just ran some tests with russion text and that worked okay.

Kind regards,
Bastien Mensink
www.asap-utilities.com

If you will "answer" this mail by sending an Excel file with lots of Unicode characters to bastien at asap-utilities com, please - or perhaps send it to me, curt at webspeed dk? I have never used Excel myself, it was just part of this Office thing I thought I would need...

Some Unicode characters from my part of the world: æøå ÆØÅ ß

Thanks for doing that, Curt.  I'll email him directly, that's cool, he sounds like a real responsive guy.  By the way, that symbol, Ø, is the music symbol for a half-diminished chord. 

[Curt]

From Copenhagen, Curt writes regarding the annoying features whilst converting to CSV files and the like.

TOL tipster Rohn found a link that may be helpful:

I have used this tip from another site:
  http://exceltips.vitalnews.com/Pages/T1112_Inconsistent_Output_for_Empty_Columns_in_a_CSV_File.html

The tip includes a macro that creates a CSV file.

- quote from The Office Letter - Premium Edition - Vol 6 No 47 - May 21, 2007 

http://www.officeletter.com/subscribe.html