A bit OT: Excel grief.

Hi,

I use Excel to edit Symbol list, alarm list etc. This to speed up making HMI and PLC projects.
But a small thing is irritating me beyond sanity:

When I have a symbolic name that starts with a reference text like this: "+FG-K11" or "=AN-S01" (ignore the quotes), Excel tries to treat the symbolic name as a formula.
I have to do a search-and-replace for all "+" to change them to " '+" and for all "=" to change them to " '=". The '-character makes Excel treat the cell as text and stops it from trying to calculate the formula. So far so good, I can then proceed with my editing.

However, when I save the file, and later open it again, all the '-characters are gone, and I have to do it all over again. Every #¤+*$§ time.
It would save me a lot of time if I could make Excel either,
1: Stop trying to calculate the cells alltogether.
or 2: Stop stripping the leading '-characters when opening a file.

To anyone who have a bright idea: THANKS !

Have you tried formatting the cell as text? It should remain exactly as you typed it in.

I should have stated that the files have either CSV or DIF format.
Excel strips any "special" formatting when saving, including the information that it is text only.
Excel asks if I want to save with or without "additional information". I have to select without additional information, otherwise the files cannot be imported into the various PLC and HMI softwares.

At the risk of sounding like a smartass, Jesper, why not simply omit using "+" and "=" and so on in the names?

I could do that but it would cause an uproar.
"+", "=" and "-" permeate through all our documentation, diagrams, manuals, component marking etc. etc.
The "=", "+" and "-" comes from an IEC standard that defines how components must be identified ("referenced").
The "=" denotes the functional reference (what does it do).
The "+" denotes the positional reference (where is it).
The "-" is the individual "name" for each compoment that also must follow certain standards.

You would be =PLCsnetmember+Milwaukee-TomJenkins.

Yours truly,
=PLCsnetmember+Copenhagen-JesperPedersen.

Can you write a macro to handle the reformatting after import of the .csv. In that way you would just run the macro or macros to do all the work for you.

I think you are out of luck. The only thing I can suggest is format as text, and keep in .xls format as long as possible. But from my experience, I expect that will be impossible.

Can you write a macro to handle the reformatting after import of the .csv. In that way you would just run the macro or macros to do all the work for you.

Click to expand...

I guess I could, but I am totally inexperienced with Excel macros (there are so many other programming problems/techniques that draw my attention).

I was hoping that there is a small setting somewhere that just needs clicking.

Jesper not sure if this will do it or not, will check after class this morning. With Excel open goto Tools then Options then Calculations, set it to manual and uncheck recalculate before save.

Jesper,


I am not very experienced with macros either so take this with a grain of salt.

There is a macro record button that once activated will record the steps you a using to correct the sheet. The search and replace functions you are using would be recorded. When finished performing the replace functions you can stop the macro record. Then it will save the macro for use in future worksheets. You will probably need to experiment a little bit.





Good luck

I have tested what you have outlined and you are absolutely right. CSV files will not support leading characters such as "+" or "=".

The only way I found you can get around it is if you enclose the contents of the cell in [ ].

For example [+123] or [=345] will come back exactly the same way.

Unfortunatelly now you have to parse cells and CSV file to get rid of "[" "]".

You can do that by using Mid() function in VBA.

Thanks to rsdoran for your suggestion.
But the result is that the cells in question will display "0" (zero) in stead of the result of the calculation. Its an improvement, but not exactly what I wanted

I will tinker with the macro recorder suggested by Glenn. If it is as easy as it sounds it will be the way to go.

Thanks to Jiri for your suggestion.
But I am afraid to fumble about with VBA. Glenns suggestion sounds to be easier.

It's a CSV problem, Changes will be saved if you did it in XLS files.
Anyway, I know it's important to have it in a CSV format so you can use it in your HMI software.

Here is a solution:
Open your CSV file using Notepad (Open with Notepad ) and use Search and Replace and then save it.
If you ever opened this CSV file again using the Excel, You 'll find that all the changes are still there.
I tried it my self.

Best regards.

Update: I tried it again and have other problem, I'll see what is it!

Just tried the Excel macro recoder.
It works, and it really is quite easy.
I can now write "Excel Macro Programmer Expert" on my CV. Great.

Again thanks.

Ayman,
yes I can use Notepad or another word processor.
But Excel is much more productive and easier.
Thanks anyway.