Remove code from a document 1

[GirlKiwi]

Hi All
I have a Word document that uses an input form. When initialized it reads data from a textfile. The document can then be saved. While this part works fine, the problem is that when this document is emailed and the recipient opens it, the file is looking for the data source that is attached to it. I need to know how to detach the data source or even remove the existing code from the document once it's been saved as it has all the values needed.
I would really appreciate any help.
Thanks

 

[PHV]

Why not using a template (.dot) having all the initialising stuff in the Document_New() event procedure ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GirlKiwi]

Thanks for your help. I had tried using a template, which would really solve some problems, but when I go to open up a document using the template, it won't load the form I have. You mentioned about putting it in the Document_New() event procedure; I don't really know how you mean. Could you please explain.

Much appreciation.

 

[fumei]

Put the userform in the .DOT (template) file. Start up the userform with the Document_New procedure of the template.

You find this in the ThisDocument module of the template file.

open up a document using the template

Be careful with terms. You do not open a document using the template. You create, or clone, a new document from the template. Once that document is created, and saved, it is independent (kind of). It takes on the characteristics of the template file - as it was when the new document was cloned. When you open that document, you do not "use" the template.

When the new document is cloned (by way of File > New and selecting the template), the Document_New of the template fires, and the userform is displayed. It gets the data, and the document is saved. Document_New code (in the template) is NOT replicated in the document. The document does indeed have a Document_New, but (unless you put something in it) does not have the code from the template Document_New.

Gerry
My paintings and sculpture

 

[GirlKiwi]

Thanks Gerry
That was put very clearly. And yes it is important to get the wording right there. I actually mean creating a new document and I can see that you know what I mean by your feedback. I feel like I'm getting somewhere although not quite there yet.

I have added a Document_New() procedure in the ThisDocument area. Previously, I could get the form to load by running an auto open macro. I was assuming that I could put the same code into the Document_New() procedure to load the form but it's not happening. It say's file not found. I have Load UserForm1 and then UserForm1.Show in the procedure.

Also, when I get the form to open, will it automatically go to the UserForm_Initialize()procedure as this is where it reads data from a file?

 

[GirlKiwi]

Hi
I have been trying to get my form to run since my last posting and with all your help I have now narrowed my problem down to this: when I run the form I get an error message saying "Object variable or With block variable not set". I don't understand this. Also are the forms linked with the ThisDocument module?
Thanks for your patience

 

[PHV]

Which line of code highlighted when in debug mode ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GirlKiwi]

It is "Load UserForm1" that is sitting in the ThisDocument module.

 

[PHV]

The error should be in the UserForm1's code module.
You may try to add a break point on the first instruction of the UserForm_Initialize procedure and then step the code with the F8 key.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GirlKiwi]

Ok oops don't know what's happened now but when I go to create a document from the template, it says "file not found". When I click debug, it comes up with Load UserForm1. So I set a breakpoint in the initialize procedure, it steps through the code up right up until it tries to open the strPath. It then comes up with "File not Found", when I click debug, it comes back to Load UserForm1. Sorry if I'm making this more confusing, ameteurs should never play around with code unless they know what theyre doing!

 

[PHV]

it steps through the code up right up until it tries to open the strPath
Could you please post the relevant code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GirlKiwi]

Here's the code. Sorry it's a bit long.

Public Sub UserForm_Initialize()
With cmbStaff
.AddItem "Buffy Devlin"
.AddItem "Barrie Woods"
.AddItem "Davina Hogg"
.AddItem "Viviane Mulgrew"
End With

Dim myTemplate As String
'reads data from a file


strPath = ActiveDocument.Path & Application.PathSeparator & "qryApprovalLetter.txt"

Me.cmbClient.Clear
Close 1#

Open strPath For Input As #1
Do While Not EOF(1) ' 1 means #1
ReDim Preserve arrClientName(intCount)
ReDim Preserve arrDOB(intCount)
ReDim Preserve arrACCNO(intCount)
ReDim Preserve arrCM(intCount)
ReDim Preserve arrCMFirst(intCount)
ReDim Preserve arrAddress(intCount)
ReDim Preserve arrCity(intCount)
ReDim Preserve arrClientAdd(intCount)
ReDim Preserve arrClientCity(intCount)
ReDim Preserve arrClientPh(intCount)
ReDim Preserve arrNum(intCount)
Input #1, arrClientName(intCount), arrDOB(intCount), arrACCNO(intCount), arrCM(intCount), arrCMFirst(intCount), arrAddress(intCount), arrCity(intCount), arrClientAdd(intCount), arrClientCity(intCount), arrClientPh(intCount), arrNum(intCount)


intCount = intCount + 1

Loop
Close 1#
For intCount = 0 To UBound(arrClientName())
cmbClient.AddItem arrClientName(intCount)
Next intCount

End Sub

 

[PHV]

I guess that the ActiveDocument.Path is not populated as the new document is not yet saved.
You may consider using ActiveDocument.AttachedTemplate.Path instead.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GirlKiwi]

Before I go off and try figure that one out, could it be anything to do with the fact that the form is going into a template (ThisDocument) because how I originally had it, when the form was attached to a particular document, rather than a template, it seemed to work. But then I ran into other problems as first stated.

I would prefer this in a template as you have originally suggested.

 

[fumei]

No, PH is correct.

When the template creates the new document, if you Debug.Print ActiveDocument.Path, you will get "", nothing. There IS no path for a new document, even one created from a template other than normal.dot. Path is a location on disk. A new document that has not been saved to disk...has no path.

So. Put in a path for qryApprovalLetter.txt.

This is just my opinion, but I would also suggest putting your code for getting the data into its own Sub. Technically it is OK in UnserForm_Initialize, but I tend to separate procedures, and...oh whatever.

Gerry
My paintings and sculpture

 

[GirlKiwi]

Hi again
I can understand a lot more about this now, however putting in a path for qryApprovalLetter.txt is not that easy for me. I have tried all sorts of things including strPath = AttachedTemplate.Path & Application.PathSeparator & "qryApprovalLetter.txt". It doesn't seem to recognise the template that's attached to it, like you both said about it not being saved so therefore there is not a path to it.
I also tried this 'ActiveDocument.AttachedTemplate = "C:\Documents and Settings\My Documents\Work\Word Forms\ACC equipment form.dot", the error message was run-time error, object required.

Would it be ok if I could email the actual template and the txt file to someone? If no, it's ok. I will just go back to the drawing board.

Thanks for your help

 

[fumei]

You can email it to: my handle at telus dot net.

However, I am not sure it will help all that much. If

strPath = AttachedTemplate.Path & Application.PathSeparator & "qryApprovalLetter.txt

does not work, then the path is wrong. Simple as that. IS the file qryApprovalLetter.txt in the folder that has the template??

If it is not, then giving the template path is pointless.
If it is, then that is not really a good thing. Template folders should have template files.

In any case, try putting the actual path. Forget about strPath.

Open "c:\blah\doda\whatever\qryApprovalLetter.txt" For Input As #1

Does that work?

Gerry
My paintings and sculpture