Excel, VBA, Word..... fancy a challenge?
Posted by Dave on December 28, 2001 3:37 AM
Basiclly all i'm trying to do is reformat a list of well names (as in oil wells) which have been supplied in excel but, it doesn't appear to be that simple... This is what a section of the well name list would look like:
What i want to do is remove the leading zeros and ensure the various suffixes are in the correct case so that the list can be imported into access at a later date.
Now, the problem comes when i do the search and replace operations. I know what they all need to be so that isn't the problem but, the tendancy for excel to change some of the well numbers into dates is! i have tried to stop this but, cant so, the procedure i have at present is:
1. select the list from excel and copy it.
2. open word and paste special (unformated text).
3. run all the search and replace funtions on the text and save it as a .txt file.
4. go back to excel and open the txt file as a delimited file.
Bingo! Bit long winded though...
I have the code for all the steps on their own (ie. the first step in excel, the second in word and the final one in excel) and the only thing i cant get my macro to do is switch between the programs and continue running the code.
I know you can call external macros from other programs from within an excel macro but, i want all the actuall code to be there min the one place.
I'm quite sure there is a nice simple way of doing it but, i cant find it!!!
Good luck and thankyou for your time and energies!!
If you want me to show you the whole VBA Code as it stands send me an email and i will emailit to you as its a bit big to post here!