Convert Text to Number on 4,508 excel files.

[KCHurd]

Hi all.

I'm faced with a rather unique problem.I have a large amount of
improperly formatted excel files. They all have various cells formatted
as text, yet they contain numbers and perform calculations on the
cells. I now need to get these in a proper state.

What I need is:

Conversion of all text boxes containing numbers over to proper format
of number.
Do it automagically. I don't relish the thought of opening each one
individually and fixing it.

Any thoughts, tips or ideas on this?

 

[Cactus]

It could possible if you process that works by Scripting .

In Scripting engine 5.6 Filesystem object it can manage files via
scripting.

relate articles on http://msdn.microsoft.com/scripting


I'm sorry can not give you a example.


Regards.

 

[Ron de Bruin]

Hi

You can open the files in the loop and do your stuff
http://www.rondebruin.nl/copy4.htm

 

[PY & Associates]

As we have no clue of how the sensitive spreadsheet look like, our
suggestion is

put all files in directory ...\01
get first filename - dir(*.xls)
loop for each file
open thisfile
loop for each sheet

you are on your own here
locate cells with the formula which can lead you to likely range of
value formatted as text

multiply this range with +1
next sheet
save thisfile to directory ...\02
close thisfile
get next filename - dir
next file

Regards