Creating and Editing Word Doc from within Excel; Procedure Randomly Stops

[jasoncw]

I tried posting this on another forum, but did not get a solution yet. Here is the link in case you are interested:


http://www.excelforum.com/showthread.php?t=619234


This is an Excel file, and the reason I am posting here is because the code that is not working is actually Word VBA within the Excel VBA module. I will try to explain a little better for you below.

Using Office 97/Win 2k Pro and Office 2003/Win XP Pro, I am having the same results.

I have an Excel workbook that creates a Word document using a command button and userform. The command button calls the user form, data is entered by the user, and when a command button on the user form is clicked, the document is generated.

Sometimes, I can step through the procedure, and it will work all the way through. Sometimes, it seems as if I step through too fast, the procedure seems to just stop without warning, and will not go any further. If I try to run the procedure by simply clicking the command button, it will usually not run all the way through. The stopping points appear to be somewhat random, but all after the Word App is created and after both Word files are opened.

I am attaching files to make it easier for anyone who is trying to help me replicate the problem. On the 2008 Log.xls file (password is "password" if you need to unprotect it), I changed the name of the files in the code to refer to:

C:\Temp\DM.doc

and

C:\Temp\LH.dot

DM.doc is the letter that is opened and modified. LH.dot is our company's letterhead that is opened, header copied, and closed. Please save both of these to C:\Temp to properly work.

The code to open and edit the Word document is in Module2 of 2008 Log.xls. I did make several changes to the Excel and Word files, since some of the information was proprietary, but it should still work as intended.

If it works for you the first time, please close Word and try it again. Every once in a while it will work for me just fine. But if I try it again, it will not work properly. I moved the visibility and activation of the Word app to the beginning of the procedure, in order to help see the stopping point of the code.

I really appreciate any help on this issue.

Jason

[Dave]

I kicked it around for quite a few hours with several variations and I don't get it... Run time error 462.. server doesn't exist etc. It bombs on 2nd iteration every time on this line....

.LeftMargin = InchesToPoints(1)

Then, in your version, it bombs on this line after that....
.TabStops(InchesToPoints(0.75)).Clear

You need to adjust your userform code to track down the error as follows:


Private Sub cmdLetter_Click()
Dim Answer
'On Error Resume Next
With Me
'etc
If Answer = vbOK Then Call CreateSampleRequestLetter
Else: Call CreateSampleRequestLetter
End If
End With
frmSampleReqLetter.Hide
End Sub

Note: remove frmSampleReqLetter.Hide from CreateSampleRequestLetter sub.

I find it very odd that every version I tried worked once then crashed on the 1st line of code I posted above with the error I indicated. HTH. Dave

[fumei]

Aaiiieeeee!

Why are you doing this?

Why are you opening a template (.dot) file? Why are you copying stuff from the template to a document?

USE the template. Make the template the way you want the document to be, then USE it. Templates are used to clone new documents. You do not need to to do any of that copying stuff.

You do not need to do that delete Exhibit stuff - as it would not be in the template.

Also, using Find and Replace to put text into a document is crazy. Use bookmarks. For example, you could replace this: 'insert salutation
.Find.Execute FindText:="Dear:", ReplaceWith:="Dear " & _
frmSampleReqLetter.cboMrMs & _
frmSampleReqLetter.txtContName2 & ":", Replace:=wdReplaceOne
.HomeKey wdStorywith this: wrdDoc.Bookmarks("Salutation").Range.Text = _
frmSampleReqLetter.cboMrMs & _
frmSampleReqLetter.txtContName2 & ":"
Are you sure you want that ":" at the end???

Dear Mr. Michael Jones: ???

You never unload the userform! Hide does NOT unload a userform.

Oh.....and use Styles. If you used Styles, THIS code could be completely removed - as the style would BE this. 'removes paragraph spacing on subj line
With .Find
.Text = "SUBJECT"
.Execute
End With
.HomeKey wdLine
With .ParagraphFormat
.SpaceBefore = 0
.SpaceAfter = 0
End With
.HomeKey wdStory

'delete extra line before subject
With .Find
.Text = "subject"
.Execute
End With
.HomeKey wdLine
.TypeBackspace
.HomeKey wdStoryYou would not need this at all. None of it.

You would not need to do anything with the headers and footers, as they would already be there.

[jasoncw]

Thanks so much for trying to help me out on this. And yes, fumei, I know you don't like the fact of not using a template. Unfortunately, this is out of my hands. Actually, I could use the template, but I would still need to do all of the formatting of the other file. Both of these are out on our intranet, and I have no way of modifying the files. So unfortunately, I need to work with them, and modify them for my needs.

Since the files already exist, and I need to work with them, any further input you may have for me? Sorry for the frustration.

As for not unloading the userform, that was not really an oversight on my part, as I would like to be able to use it more than once without having to retype the info in the text boxes. Do you think this may be causing the problem?

Thanks again for the help.

Jason

[fumei]

As for not unloading the userform, that was not really an oversight on my part, as I would like to be able to use it more than once without having to retype the info in the text boxes. Do you think this may be causing the problem?Huh? Bad programming.

I will repeat it. You never unload the userform.

If you require the userform to pick up existing data, then DO that.

Both of these are out on our intranet, and I have no way of modifying the files. So unfortunately, I need to work with them, and modify them for my needs.
Let me get this straight....

You have a template (.dot) file that:

1. contains ONLY, repeat ONLY, a graphic
2. is never used as a template

You have a document, NOT formatted the way you want...so you have to change it.

I am not being critical of you, as I understand we don't always have control of things, but this is a very poor use of Word.

Why not make a new template that works properly (ie. uses styles and bookmarks, and is formatted correctly), and use that? You could most certainly make a business case to the powers that be.

[jasoncw]

Let me get this straight....

You have a template (.dot) file that:

1. contains ONLY, repeat ONLY, a graphic
2. is never used as a template

You have a document, NOT formatted the way you want...so you have to change it.
Yes, that is correct. I do plan to discuss this with the "powers at be." However, there are many behind-the-scenes activities going on right now with integration of multiple companies, as well as the need to maintain these as ISO-controlled forms. By the time it gets to the point that others can use it, it will be several months down the road unfortunately. In the mean time, I was attempting to come up with a temporary solution. So can you or anyone else help me at this point?

By the way, I received your criticism for the same template use a few months ago...
http://vbaexpress.com/forum/showthread.php?t=14002

[jasoncw]

Ok, one last bump to the top before I kill this project.