I think you have a fundemental problem. You are unprotecting a form, and trying to fill data into the form fields, which are designed to be completed when the form is protected.
MD
I think you have a fundemental problem. You are unprotecting a form, and trying to fill data into the form fields, which are designed to be completed when the form is protected.
MD
BTW I'm using Office 2000 so there may be inconsistencies.
In looking a bit closer, the macro is opening the template, not a new form based on the template. It is then trying to change to form fields in the template into text strings, and then set a dropdown value. These actions are inconsistent with filling in the form.
Whether it's of any use, I submitted a KB item recently to complete a form using a Userform, which collected data from Excel as part of the procedure.
http://www.vbaexpress.com/kb/getarticle.php?kb_id=184
MD
Try the following
[VBA]
Option Explicit
Public Sub ExcelWord()
Dim blnFlag As Boolean
Dim wdmodelclfullname As String
Dim WdApp As Word.Application
Dim WdDoc As Word.Document
Set WdApp = CreateObject("Word.Application")
blnFlag = True ' Set as needed
With WdApp
.Visible = True
Documents.Add Template:="C:\Templates\CHECK LIST.dot", NewTemplate:=False, _
DocumentType:=0
With .ActiveDocument
ActiveDocument.FormFields("Text3").Result = "This is my text3"
ActiveDocument.FormFields("Text4").Result = "This is my text4"
If blnFlag Then
.FormFields("Check10").CheckBox.Value = True
End If
.FormFields("dropdown1").Result = ActiveWorkbook.Worksheets(1).Range("C2").Value
End With
.Quit 'Use if you want to shut down the instance of Word started here
End With
Set WdApp = Nothing
End Sub
[/VBA]
This is filling up on a correct way.
I hope i could now reset the protection and save it.
BTW how should i save it: like a .dot or like a .doc ?
What is the difference between them ?
(I am new to this ...)
Thank you sooooooooooooo much for your help.
Now i have to stop.
I will return tomorrow.( evening)
See you later.
Thanks again !!!!
Really great ....
Hi Howard.
In adjusting the code to open a "completed form" for updating, I have adjusted the open command to
[VBA]
DocName = "C:\Atest\" & InputBox("Saved Form Name") & ".doc"
Documents.Open (DocName)
With Documents(DocName)
etc.......
[/VBA]
However, it only works on alternate attempts, giving the error message
It happens whether Word is open or not, and after a successfull attempt is closed, there is no instance of Word in the Task Manager. Any ideas?
Runtime error 462
The remote server machine does not exist or is unavailable.
MD
Hi JDPO,
A template (.dot file) is an "outline" for your documents. It is normally preserved unchanged for mulitiple uses, unless there is a reason to save the underlying text. It is saved as "protected" to permit changes only to the form fields etc. available for filling.
When you open a new document base on a template , you create a new Word document (.doc file), which will be named as default DocumentX. When you open a form based on a document and fill in the fields, if you wish to preserve the information, you save it to a folder with a suitable name. Forms are not usually protected, unless password protected for security reasons.
It is not clear whether you simply wish to fill in a form, or to amend a previously completed form, nor to the extent that Excel is involved. Are you wishing to enter all the data in Excel and transfer it to the form? If so, a Mail Merge solutuion would be easier to implement.
If you can clarify your objectives, we can find the best solution.
Exactly which line in the code is being highlighed when you get the error message?Originally Posted by JPDO
If you include the code below in a module in the source Excel workbook, then all you should need to do is:
1. Modify the value assigned to WdModelClFullname to identify the file on your system.
2. Make sure the Excel project has a reference to trhe Word Object library.
I'm looking at the code in Office 2003, so I guess it's possible that some piece is not supported in an earlier version. I cannot verify this without checking on another system. What version are you using?
[VBA]Option Explicit
Public Sub RunMe()
ExcelWord True
End Sub
Private Sub ExcelWord(blnFlag As Boolean)
Dim WdModelClFullname As String
Dim WdApp As Word.Application
Dim WdDoc As Word.Document
Set WdApp = CreateObject("Word.Application")
With WdApp
.Visible = True
WdModelClFullname = "I:\HKShared\My Documents\Temp\check list.dot" ' Set for local machine
.Documents.Open WdModelClFullname
With .ActiveDocument
.Unprotect
.Bookmarks("text3").Range.Text = "This is my text3"
.Bookmarks("text4").Range.Text = "This is my text4"
.FormFields("Check10").CheckBox.Value = blnFlag
.FormFields("dropdown1").Result = ActiveWorkbook.Worksheets(1).Range("C2").Value
.Protect Password:="", NoReset:=False, Type:=wdAllowOnlyFormFields
End With
.Quit 'Use if you want to shut down the instance of Word started here
End With
Set WdDoc = Nothing
Set WdApp = Nothing
End Sub
[/VBA]
Hi
What i have to do is filling-up a WORD fill-in form(Checklist.dot) with data coming from:
1. A host-computer (into an Excel-spreadsheet).This works fine.
2. Looking up some other data in different spreadsheets residing on a PC-Lan and fill that data in the Checklist.dot (That's what i have to program now)
3. Permitting the user of my macro to complete manually some cases in the checklist.dot
4. controlling the SAVE- and CLOSE-operation of the Word Template so it should always be saved in the right directory and standard naming of the saved file.
5. Permitting the user to ask a print of the Saved / filled-in Checklist.
So i should greatly appreciate if someone could tell me how i can invite the user to
- enter his information
- ask to SAVE / CLOSE the template
- ask to have a Print
Also how and when .quit will be executed ?
I tought on a commandtoolbar with these options. But the user can still "escape" with the standard Word-toolbar.
Can you help me ?
Thanks in advance
A few queries
You have a large number of fill-ins. What percentage are being filled from Excel?
Do you keep all the fill-in data for all forms in one file in excel, or is there one spreadsheet per form?
Do all forms get saved in the same directory? If so, what is its name?
Is the Folder and File name derived from data used to fill in the form eg a file number or such? If so, which data?
Hi,
Here are the answers:
50% of the data comes from EXCEL. (The other is typed-in by the user)
There is 1 EXCEL-spreadsheet coming from hostcomputer, different EXCEL-spreadsheets give the "accounting"-information.(see lookup-item in my previous post)
The directory to save-in is depending on the value of the dropdownlist1 (procedure).
The file name is the debtor's filenumber stored in the first Excel-spreadsheet coming from the hostcomputer.
Thanks for your help.
It appears that your app is a lot more complex than just the issues you raised in the posting that started this thread.
There are quite a few issues to resolve.
You likely should seek paid assistance, perthaps from one of us.
Hello,
I think there is a big misunderstanding between us.
My app has to do only the filling-up of the Word template.
I described you the workflow process. However it is surely not the aim to set this in my app.
What i would ask was your advice upon the way of saving the .dot file, like a .dot file or like a .doc file.
I ask this because some people told me that Word 2003 saves often .dot files as .doc-files. So i worry about it.
And therefore i described the workflow process.
Hope you can advice me on this.
Thanks anyway for the help i received.
H JDPO,
I'll have a look at this shortly. The files will be saved as doc files, though, as you will see from the new coding.
Regards,
MD
Try the following revised code. A couple of bugs though. It fails to open the document on alternate tests (see earlier post above). If fails if Word is open. I'll explore these further, but let me know if the functioning (when it works) is correct.
Enter a value "AWL" in cell C2 ; This will go into your dropdown and also be the folder for your file.
Enter a number/text into cell C2, this will be the FileName
Adjust Paths etc. in the code as required. I'm using C:\Templates to store the template.
The code should create a new document from your template, enter your Excel data and save it as "C:\AWL\1234.doc", ready for further user input
[VBA] Option Explicit
Public Sub ExcelWord()
Dim blnFlag As Boolean
Dim WdApp As Word.Application
Dim WdDoc As Word.Document
Dim MyFolder As String, MyFile As String, SaveName As String
MyFolder = ActiveWorkbook.Worksheets(1).Range("C2").Value
MyFile = ActiveWorkbook.Worksheets(1).Range("C3").Value
Set WdApp = CreateObject("Word.Application")
blnFlag = True ' Set as needed
With WdApp
.Visible = True
Documents.Add Template:="C:\Templates\CHECK LIST.dot", NewTemplate:=False, _
DocumentType:=0
With .ActiveDocument
ActiveDocument.FormFields("Text3").Result = "This is my text3"
ActiveDocument.FormFields("Text4").Result = "This is my text4"
If blnFlag Then
.FormFields("Check10").CheckBox.Value = True
End If
.FormFields("dropdown1").Result = MyFolder
MyFolder = "C:\" & MyFolder
If Dir(MyFolder & "*.*") = "" Then MkDir MyFolder
SaveName = MyFolder & "\" & MyFile & ".doc"
.SaveAs SaveName
MsgBox "Saved as " & SaveName
End With
'.Quit 'Use if you want to shut down the instance of Word started here
End With
Set WdDoc = Nothing
Set WdApp = Nothing
End Sub
[/VBA]
Hello,
Thank you. This is what i wanted to know. It Works great.
I still have 2 questions about fill-in documents.
In this checklist there are no number-defined fields, but suppose there are.
How can i give them the wanted content so that the editing-rules in the template are respected ?
An other question is what happens when i set a dropdownlist-result to a value that is not present in the dropdownlist ?
Hope you can help me here through. These are my last questions about it.
Thanks in advance for your help.
BTW have i to close this thread ? How ?
Hi JPDO,
While these questions are valid here, it would be better to ask them as new questions to get a broader response. They would have many gereral applications, not only to your particular query.
MD
Whether you save as a template or a document depends on how you wish to use the saved document.
If you wish the changed document to be used as a template by a new document, then save as a template.
If you wish the changed document to be like a normal document, that does not affect new documents created with the template, then save as a document.
Posting Permissions
