Excel, VBA, Word..... fancy a challenge?
Posted by Dave on December 28, 2001 3:37 AM
Hi all.
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:
21/07-02
21/07a-01
21/07a-03
21/08-02
21/12-02B
22/05a-06
22/05a-13
22/10-01z
22/10b-06
22/12a-01st1
22/12A-02
22/12a-03
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!
Dave (grizzley20uk@yahoo.co.uk)
Posted by Dank on December 28, 2001 5:59 AM
If you want to encapsulate your code in one place but you still need to use Word then you'll have to use the Word object model. Here is some code which may be of use:-
Option Explicit
Sub ControlWord()
Dim wdApp As Object
'Select list in Excel and copy to clipboard
ActiveSheet.UsedRange.Copy
'Create an instance of Word and add a new document
Set wdApp = CreateObject("Word.Application")
wdApp.Documents.Add
'Paste the data from Excel into Word
wdApp.Selection.PasteSpecial DataType:=2
'Now have Word do all its search and replace stuff and save the file
'Next, save the file as a text file and close it
wdApp.ActiveDocument.SaveAs "C:\temp\mydoc.txt", FileFormat:=2
wdApp.ActiveDocument.Close
wdApp.Quit
'Now open up the text file in Excel.
Application.Workbooks.OpenText "C:\temp\mydoc.txt"
End Sub
If you can't get your code to work then send me an email with the relevant files/code and I'll have a look.
Regards,
Daniel.
Posted by Dave on December 28, 2001 8:20 AM
Cheers!
i'll give it a bash and see how i get on...
Posted by Tom D on December 28, 2001 9:37 AM
You might try running some code that initially puts an apostrophe at the beginning of each well name. That way you can stay in excel without having it translate to a date format.
