Using the EURO symbol in filenames via an excel generated RENAME statement in a batch file ...!

[stripeyuk]

Hi
Sorry if this has long since been discussed and solved, but sadly in searching this and other forums and the web, I can't find a solution! doh! Must be blind?

I guess the fact I'm in the UK might be relevant to code pages etc.

I'm reading in (well cut and pasting from a text file that is generated by a dos piped dir command) a list of file names that include £, $ and € currency symbols within the file name.
an example line might be "c:\test dir\value_€12.23.pdf". ie that is one cell when pasted to excel.
The $ symbol are fine as the text file and excel retain the symbol with no hassle
The £ symbol becomes œ (char 156) when loaded into the text editor (Notepad++), so when I paste into excel I can use "substitute" to convert to char 163, to correct this.
The € symbol becomes a standard ? (char 63) so this can be changed to char 128 and as "?" is not used in the file names elsewhere, there is no chance of extra € appearing!
So the DOS to Textfile to EXCEL can be corrected with this:

SUBSTITUTE(SUBSTITUTE(C2,char(156),CHAR(163)),CHAR(63),CHAR(128))

Reading the file in is fine, but then I need to write it back - that's the issue!
It will become a batch file of rename commands (yes, I could write some VBA rather than creating a batch file, but this is a quick and dirty project - or was quick till I hit this brick wall !)
My destination Cell might be:
REN "c:\test dir\value_€12.23.pdf" "value_paid_€11.95.pdf"
and this cut and past into a batch file looks fine too, but once run the filename in windows/dos no longer has the "€" but something else ! The same is true for teh £ symbol, but that I can fix by swapping back to the char 156, either by using Substitue or formating the value field

TEXT(VALUE(AO2),CHAR(156)&"0.00")

This works great for £, but ...

Sadly the EURO (€) cannot be written as char 63 "?", and I have tried char 250 and char 128, but neither work ...

It's not the excel display that I have an issue with but the writing back into a format that I can use in a DOS batch file to create a € within the file name.

Is there a char that will work, if not can this be done in VBA, if so what char should I use?

Everything else is fine, just the pesky EURO! ... and no I'm not joining UKIP to avoid the issue

thanks in advance

thanks
Richard

 

[mole999]

in Calibri Char € is shown as 20AC unicode

 

[stripeyuk]

thanks for the rapid reply, googled it and tried to use alt-gr 20AC, but it generates a symbol (not euro) after 20A, before I even press C. decimal is 8364, but Char(8364) gives an error

How can I code this into a formulae ?

thanks

 

[mole999]

this worked for me on the worksheet

=TEXT(VALUE(E25),CHAR(128)&" 0.00")

in VBA

ActiveCell.FormulaR1C1 = "=TEXT(VALUE(RC[-2]),CHAR(128)&"" 0.00"")"

Cell IDs need to be what your looking at

 

[stripeyuk]

thanks, I think you've missed the point

I can get a Euro in Excel, just not to write to the filename as a EURO symbol, when you use char 128, and then use a "rename" command, it will not show a € but instead a Ç (char 199)
As an example take this, and paste into a cell in excel:

="REN value.txt value"&CHAR(128)&"123.txt"

You get a nice shiny € symbol.

To demonstrate the issue:
1. create a file called value.txt, just right click in windows explorer, create new text file
2. same thing again, but rename it to action.bat
3. Now paste the text (just hit ctr-c in excel and ctr-v in the text file) into action.bat

you'll have a single statement that reads
REN value.txt value€123.txt
save it and then run it ...
you'll find the file is not called "value€123.txt" as expected but is in fact "valueÇ123.txt"

that's my issue!! I need the filename to show a € in it ...

 

[mole999]

skimming this, WPDOS - How to Display the Euro in MS-DOS and Windows DOS i think the euro is a DOS issue, which is what the bat file is doing

an off the wall thought

Bulk Rename Utility (Freeware) in Repl,(3) obvious when you see on screen
you can bulk rename all the files after conversion from Ç to € as a substitute

I guess you have already tried saving the excel file as a .txt with preformed label ?

 ActiveWorkbook.SaveAs Filename:="C:\path\" & "€" & "Book1.txt", FileFormat:=xlUnicodeText, CreateBackup:=False

 

[stripeyuk]

you can bulk rename all the files after conversion from Ç to € as a substitute

That is a possibility, but a second step, and harder for other users to perform, but a worst case option.

I guess you have already tried saving the excel file as a .txt with preformed label ?

It's not excel files as text, it's actually pdf files, they never get touched by excel, excel is just used to modify their file names (bulk correction), as the file names contain readable fields with invoice number, client, value, vat, date etc etc as part of the file name.

The other choice is to use Excel VBA to do the rename, rather than a batch file.

Any pointers on going that route?
The spreadsheets is set out thus: The worksheet is called "edit". The cells values of note are the old filename including full path are in column BB, a flag for rename or delete is in BA ("delete", or "changed", or "" for no action), and the new file name (without full path - that's how DOS wants it - can easily change to full path if needed) is in column BC, starting at row 2, and can be several thousand rows.

thanks
Richard

 

[mole999]

maybe this from 2009 http://www.mrexcel.com/forum/excel-...asic-applications-rename-files-directory.html will help

 

[stripeyuk]

I saw that in a search for using VBA but have been playing with some FSO Object Routines, which do correctly write the € symbol
mainly from List Files in a Folder and Rename them – Batch Renamer | Struggling To Excel which does something quite similar to my needs, in that it reads in a list of file names, edits then and writes them back
I do not want to rename all the files, so have a few changes to make, most notably that I would like the option to read a directory and ALL Sub Directories (as a switch-able option)
and this frame work assumes no changes to or additions/removal of files between reading and renaming, so that is not suitable for our environment, but that's quite simple to sort.
So if I go to the FSO route, all I need to add is
- the recursive subdirectory file reading - any pointers?
and then a few mods I'm sure I can manage and it's working! Not what I wanted but hey, that's programming, quick and dirty to an fully automated suite of code! ... course I had the spare time to do this!

I might test the code in the link from 2009 Using Excel VBA to rename files in directory to see if this will work, just for fun! (or maybe I'll realise how much time I've lost already and just move on!)

But ... I'd still love to know what character to use within a batch file, I hate being beaten, especially by the EURO !!!

thanks