Welcome Guest.   Make a donation to an author on the site October 02, 2014, 03:28:01 AM  *

Please login or register.
Or did you miss your validation email?


Login with username and password (forgot your password?)
Why not become a lifetime supporting member of the site with a one-time donation of any amount? Your donation entitles you to a ton of additional benefits, including access to exclusive discounts and downloads, the ability to enter monthly free software drawings, and a single non-expiring license key for all of our programs.


You must sign up here before you can post and access some areas of the site. Registration is totally free and confidential.
 
The N.A.N.Y. Challenge 2014! Download dozens of custom programs!
   
   Forum Home   Thread Marks Chat! Downloads Search Login Register  
Pages: [1]   Go Down
  Reply  |  New Topic  |  Print  
Author Topic: Excel plugin to quickly export selection to text file (csv, tab-delimited, etc.)  (Read 12088 times)
superboyac
Charter Member
***
Posts: 5,688


Is your software in my list?

see users location on a map View Profile WWW Read user's biography. Give some DonationCredits to this forum member
« on: May 05, 2007, 01:09:10 AM »

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?
Logged

bjk
Charter Member
***
Posts: 12

View Profile Give some DonationCredits to this forum member
« Reply #1 on: May 06, 2007, 11:48:03 AM »

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

http://www.asap-utilities...=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.


Logged
AndyM
Charter Member
***
Posts: 616


see users location on a map View Profile Give some DonationCredits to this forum member
« Reply #2 on: May 06, 2007, 02:13:26 PM »

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.

[copy or print]
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!!!***
Logged
superboyac
Charter Member
***
Posts: 5,688


Is your software in my list?

see users location on a map View Profile WWW Read user's biography. Give some DonationCredits to this forum member
« Reply #3 on: May 07, 2007, 12:35:05 PM »

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

http://www.asap-utilities...=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?
Logged

AndyM
Charter Member
***
Posts: 616


see users location on a map View Profile Give some DonationCredits to this forum member
« Reply #4 on: May 07, 2007, 02:08:24 PM »

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:


[copy or print]
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.
Logged
superboyac
Charter Member
***
Posts: 5,688


Is your software in my list?

see users location on a map View Profile WWW Read user's biography. Give some DonationCredits to this forum member
« Reply #5 on: May 07, 2007, 04:02:14 PM »

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

AndyM
Charter Member
***
Posts: 616


see users location on a map View Profile Give some DonationCredits to this forum member
« Reply #6 on: May 08, 2007, 10:29:24 AM »

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.
Logged
superboyac
Charter Member
***
Posts: 5,688


Is your software in my list?

see users location on a map View Profile WWW Read user's biography. Give some DonationCredits to this forum member
« Reply #7 on: May 09, 2007, 01:10:20 PM »

OK, here's another problem I just ran into.  I'm using a bunch of Unicode (UTF-8) 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?
Logged

superboyac
Charter Member
***
Posts: 5,688


Is your software in my list?

see users location on a map View Profile WWW Read user's biography. Give some DonationCredits to this forum member
« Reply #8 on: May 09, 2007, 03:42:33 PM »

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

Curt
Supporting Member
**
Posts: 6,338

see users location on a map View Profile Give some DonationCredits to this forum member
« Reply #9 on: May 10, 2007, 08:07:28 AM »

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:

Quote
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: æøå ÆØÅ ß
Logged
superboyac
Charter Member
***
Posts: 5,688


Is your software in my list?

see users location on a map View Profile WWW Read user's biography. Give some DonationCredits to this forum member
« Reply #10 on: May 10, 2007, 10:20:37 AM »

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:

Quote
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. 
Logged

Curt
Supporting Member
**
Posts: 6,338

see users location on a map View Profile Give some DonationCredits to this forum member
« Reply #11 on: May 20, 2007, 03:41:57 AM »

Quote from: The Office Letter
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.vitalnew...olumns_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   Wink

http://www.officeletter.com/subscribe.html
Logged
Pages: [1]   Go Up
  Reply  |  New Topic  |  Print  
 
Jump to:  
   Forum Home   Thread Marks Chat! Downloads Search Login Register  

DonationCoder.com | About Us
DonationCoder.com Forum | Powered by SMF
[ Page time: 0.044s | Server load: 0.32 ]