Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / General Excel Questions / May 2007

Tip: Looking for answers? Try searching our database.

Excel running Word macro

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
rsphorler - 17 May 2007 20:32 GMT
I have a complicated macro which does several things to a series of
imported htm files, however it is not working as expected.

An excel macro produces a list of htm files within a directory and
outputs it to sheet1 and then one by one uses this as a parameter to
start a word macro (ww has been defined as the word application
object)

ww.Documents.Open ThisWorkbook.Path & "\datastage1.doc"
runnow = ww.Run("stage1", file)

This runs the macro stage1 in the word document datastage1.doc using
the file stored in variable "file".

Sub stage1(myfile As String)
file = myfile
folderpath = ActiveDocument.Path & "\inputhtm\"
Documents.Open FileName:=folderpath & file

This word macro then opens the file and is meant to remove all
occurences of a two line text fragment using a word (XXPAEDT) to find
the first of those lines:

'DELETE UNWANTED LINES
   Selection.HomeKey Unit:=wdStory
   Selection.Find.ClearFormatting
  With Selection.Find
   Do While .Execute(FindText:="XXPAEDT", Wrap:=wdFindContinue,
Forward:=True) = True
   Selection.EndKey Unit:=wdLine, Extend:=wdExtend
   Selection.MoveDown Unit:=wdLine, Count:=1, Extend:=wdExtend
   Selection.Delete Unit:=wdCharacter, Count:=1
  Loop
   End With

This does not work and the lines are not removed, yet if i open the
document manualy and then use the macro after removing the above
section that opens the htm file (so it starts with the remove unwanted
lines comment) it works perfectly!!!

Any ideas as to why the different behaviour if the file is opened
manually by the user or automatically by the macro

Thanks

Richard
Doug Robbins - Word MVP - 17 May 2007 21:07 GMT
You should be able to make use of the built in \line bookmark

Try

   Dim delrange as Range
   Selection.HomeKey Unit:=wdStory
   Selection.Find.ClearFormatting
  With Selection.Find
       Do While .Execute(FindText:="XXPAEDT", Wrap:=wdFindContinue,
Forward:=True) = True
       Set delrange = Selection.Bookmarks("\line).Range
       delrange.Delete
       'the selection will now have moved to the following line, which is
also to be deleted so repeat the process
       Set delrange = Selection.Bookmarks("\line).Range
       delrange.Delete   Loop
   End With

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

>I have a complicated macro which does several things to a series of
> imported htm files, however it is not working as expected.
[quoted text clipped - 42 lines]
>
> Richard
Russ - 27 May 2007 01:19 GMT
> Any ideas as to why the different behaviour if the file is opened
> manually by the user or automatically by the macro

If you have Word open more than one document at a time, it is best to assign
each document to a document variable so that you can later distinguish which
document you want to apply your macro.
Dim objDoc1 as Word.Document
Dim objDoc2 as Word.Document
...

Set objDoc1 = Documents.Open ....
...
Set objDoc2 = Documents.Open ....
...
objDoc1.Activate
...run code on objDoc1
objDoc2.Activate
...run code on objDoc2
...
Set objDoc1 = Nothing
Set objDoc2 = Nothing

> I have a complicated macro which does several things to a series of
> imported htm files, however it is not working as expected.
[quoted text clipped - 42 lines]
>
> Richard

Signature

Russ

drsmN0SPAMikleAThotmailD0Tcom.INVALID

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.