Problems EXCEL to Word

[JPDO]

hi,

I have to update a few fields in a Word fill-in Form with values from an Excel spreadsheet.
To do this i need to unprotect the Word fill-in document, update the bookmarks in the Word document, re-protect the Word fill-in document,save it and completely close-down the Word-application.

So in my code below everything goes fine until i will re-protect the Word-document. It does nothing in Word 2003 under Windows XP.
Can you help me to find a solution ?

Here goes my coding:
[VBA]Public Sub EXcelWord()

Dim flag As String * 1
Dim WdObj As Object
Dim WdDoc As Object

Set WdObj = CreateObject("Word.Application")
WdObj.Visible = True
wdmodelclfullname = "C:\Data\word\checklist.dot"
WdObj.Documents.Open wdmodelclfullname
WdObj.ActiveDocument.Unprotect
WdObj.ActiveDocument.Bookmarks("text3").Range.Text = "This is my text3"
WdObj.ActiveDocument.Bookmarks("text4").Range.Text = "This is my text4"
If flag = "Y" Then _
WdObj.ActiveDocument.formfields("Check10").CheckBox.Value = True
WdObj.ActiveDocument.formfields("dropdown1").result = ActiveWorkbook.Worksheets(1).Range("C2").Value
WdObj.ActiveDocument.Protect Password:="", NoReset:=False, Type:= _
wdAllowOnlyFormFields
Set WdObj = Nothing
End Sub[/VBA]

THanks for any help .

[Howard Kaikow]

The code as listed cannot be expected to work properly work because flag has not been set.

In any case, I would try the code below, after you correct the flag issue, and decide whether you want to quit the instance of Word started in the procedure.

[VBA]
Public Sub ExcelWord()
Dim flag As String ' Should use a Boolean instead
Dim WdApp As Word.Application
Dim WdDoc As Word.Document
Set WdApp = CreateObject("Word.Application")
With WdApp
.Visible = True
wdmodelclfullname = "C:\Data\word\checklist.dot"
.Documents.Open wdmodelclfullname
With .ActiveDocument
.Unprotect
.Bookmarks("text3").Range.Text = "This is my text3"
.Bookmarks("text4").Range.Text = "This is my text4"
If flag = "Y" Then
.FormFields("Check10").CheckBox.Value = True
End If
.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 WdApp = Nothing
End Sub
[/VBA]

[JPDO]

Hi,

Thanks for your reply.
I encoded it.
I receive a compilation error saying that "As application" is an invalid data-type.
Can this be repaced by dim wdapp as OBJECT in stead of "Application".
or is there something to change to my Excel-software version ?

Thanks for any reply.

[JPDO]

hi,

I have replaced "application" by "Object".
Compilation is OK.
but at execution i receive error 6028: Range can not be replaced.

I am stuck.....

Please help.

[Jacob Hilderbrand]

Hi,

Thanks for your reply.
I encoded it.
I receive a compilation error saying that "As application" is an invalid data-type.
Can this be repaced by dim wdapp as OBJECT in stead of "Application".
or is there something to change to my Excel-software version ?

Thanks for any reply.

If you use[VBA] Dim WdApp As Word.Application [/VBA] Then you need to set a reference to the Word Object Library (Tools | References).

[Jacob Hilderbrand]

hi,

I have replaced "application" by "Object".
Compilation is OK.
but at execution i receive error 6028: Range can not be replaced.

I am stuck.....

Please help.

Can you attach the Word document so we can test it with the code?

[JPDO]

THis my Word-document.
It contains other bookmarks and forlfill in field too.
Please limit this to the first section.

Thanks

[JPDO]

Hi,
With the MicroSoft Word Oject library i obtain a type
mis-match error 13 on the instruction set wdapp = createobject("Word.application")
Hope you received my word-document as attachment in my previous post.

Thanks for your help.

[Jacob Hilderbrand]

There is no attachment. When replying select Go Advanced the scroll down to where is says Manage Attachments. The attachment needs to be zipped.

[JPDO]

Sorry i attached it in .doc
Now I zipped.
The attachment name is Checklist.zip
(Only the first section of it has to be tested).

Thank you so much.

[Howard Kaikow]

Hi,

Thanks for your reply.
I encoded it.
I receive a compilation error saying that "As application" is an invalid data-type.
Can this be repaced by dim wdapp as OBJECT in stead of "Application".
or is there something to change to my Excel-software version ?

Thanks for any reply.

You have to add a reference to the Word object library in the Tools | References menu for the Excel project.

Always explicitly type variables, instead of using the object type, except when you know that you have to use late binding, which is unnecessary in this case.

[Howard Kaikow]

hi,

I have replaced "application" by "Object".
Compilation is OK.
but at execution i receive error 6028: Range can not be replaced.

I am stuck.....

Please help.

Yes, you did not include the names in the Excel spreadsheet.

Take a look at the following code, I made some small changes. You still need to specify blnFlag (I hardcoded below) and decide whether you want to Quit Word:

[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
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"
If blnFlag Then
.FormFields("Check10").CheckBox.Value = True
End If
.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 WdApp = Nothing
End Sub
[/VBA]

[JPDO]

Hi,

Following libraries are checked in tools | references

Visual Basic for Applications
Microsoft excel 10.0 object library
OLE Automation
Microsoft Office 10.0 object library
Microsoft Forms 2.0 object library
Microsoft Word 10.0 object library

These is my actual Code:

[VBA]Public Sub load_cheklist()
Dim Filechoosen As String
Dim i As Integer
Dim path As String
Dim WdApp As Application
path = "C:\Documents and Settings\Jean-Pierre Doeraene\Mijn documenten\DATA\EXCEL"
Filechoosen = "C:\Documents and Settings\Jean-Pierre Doeraene\Mijn documenten\DATA\EXCEL\Checklistdata.xls"
Workbooks.Open Filechoosen
Set WdApp = CreateObject("Word.Application")
With WdApp
.Visible = True
wdmodelclfullname = "C:\Documents and Settings\Jean-Pierre Doeraene\Mijn documenten\DATA\word\check list.dot"
.Documents.Open wdmodelclfullname
With .ActiveDocument
.Unprotect
.Bookmarks("text3").Range.Text = CStr(ActiveWorkbook.Worksheets(1).Range("F2").Value)
.Bookmarks("text4").Range.Text = CStr(ActiveWorkbook.Worksheets(1).Range("I2").Value)
If flag = "Y" Then
.FormFields("Check10").CheckBox.Value = True
End If
.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 WdApp = Nothing
End Sub[/VBA]

On statement set Wdapp = createobject("Word.application") i receive at execution time the error number 13 (Type mismatch)

Please help .

[Howard Kaikow]

Hi,

Following libraries are checked in tools | references

Visual Basic for Applications
Microsoft excel 10.0 object library
OLE Automation
Microsoft Office 10.0 object library
Microsoft Forms 2.0 object library
Microsoft Word 10.0 object library

These is my actual Code:

[VBA]Public Sub load_cheklist()
Dim Filechoosen As String
Dim i As Integer
Dim path As String
Dim WdApp As Application
path = "C:\Documents and Settings\Jean-Pierre Doeraene\Mijn documenten\DATA\EXCEL"
Filechoosen = "C:\Documents and Settings\Jean-Pierre Doeraene\Mijn documenten\DATA\EXCEL\Checklistdata.xls"
Workbooks.Open Filechoosen
Set WdApp = CreateObject("Word.Application")
With WdApp
.Visible = True
wdmodelclfullname = "C:\Documents and Settings\Jean-Pierre Doeraene\Mijn documenten\DATA\word\check list.dot"
.Documents.Open wdmodelclfullname
With .ActiveDocument
.Unprotect
.Bookmarks("text3").Range.Text = CStr(ActiveWorkbook.Worksheets(1).Range("F2").Value)
.Bookmarks("text4").Range.Text = CStr(ActiveWorkbook.Worksheets(1).Range("I2").Value)
If flag = "Y" Then
.FormFields("Check10").CheckBox.Value = True
End If
.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 WdApp = Nothing
End Sub[/VBA]

On statement set Wdapp = createobject("Word.application") i receive at execution time the error number 13 (Type mismatch)

Please help .

You have not defined flag.
I suggest that you use the code I posted in my previous posting, defining the appropriate value for blnFlag and substituting the appropriate path for the .dot file, as well as including the appropriate Names in the worksheet.

Also add a Set wddoc = Nothing, I forgot to add that.

[JPDO]

Hi Howard,

The flag in my original post meant that a cell-value had the value "Y" and in this case i set the formfillField CHECK10 (which is a checkbox) to true.
If the cell-value = "N" i do nothing because the default value of the checkbox is NO (Check10 = False)

Thanks anyway.

[JPDO]

I changed the following, but i still receive err 13 type mismatch on set wdapp-statement.

[VBA]Dim flag As String
Dim WdApp As Application
Dim WdDoc As Word.Document
path = "C:\Documents and Settings\Jean-Pierre Doeraene\Mijn documenten\DATA\EXCEL"
Filechoosen = "C:\Documents and Settings\Jean-Pierre Doeraene\Mijn documenten\DATA\EXCEL\Checklistdata.xls"
Workbooks.Open Filechoosen
flag = "Y"
Set WdDoc = Nothing
Set WdApp = Nothing
Set WdApp = CreateObject("Word.Application")[/VBA]

Sorry, can you check it again ?

[Howard Kaikow]

Hi Howard,

The flag in my original post meant that a cell-value had the value "Y" and in this case i set the formfillField CHECK10 (which is a checkbox) to true.
If the cell-value = "N" i do nothing because the default value of the checkbox is NO (Check10 = False)

Thanks anyway.

The code is incorrect because you did not define a value for the variable flag.

[Howard Kaikow]

I changed the following, but i still receive err 13 type mismatch on set wdapp-statement.

[VBA]Dim flag As String
Dim WdApp As Application
Dim WdDoc As Word.Document
path = "C:\Documents and Settings\Jean-Pierre Doeraene\Mijn documenten\DATA\EXCEL"
Filechoosen = "C:\Documents and Settings\Jean-Pierre Doeraene\Mijn documenten\DATA\EXCEL\Checklistdata.xls"
Workbooks.Open Filechoosen
flag = "Y"
Set WdDoc = Nothing
Set WdApp = Nothing
Set WdApp = CreateObject("Word.Application")[/VBA]

Sorry, can you check it again ?

The Set WdDoc=Nothing MUST be blaced at the end of the sub jusy before the Set WdApp = Nothing.

Setting flag to "Y" means that the If statement will always be True, so the If statement is not needed. In effect, the value of flag serves no purpose in the sub. more appropriate would be:

[VBA]
Option Explicit
Public Sub RunMe()
ExcelWord True
End Sub
Public 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"
If blnFlag Then
.FormFields("Check10").CheckBox.Value = True
End If
.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]

or, maybe you intended

[VBA]
Option Explicit
Public Sub RunMe()
ExcelWord True
End Sub
Public 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]

[JPDO]

Hello,

The aim is like you defined the second way.
I am running EXCEL and start Word with the procedure run.me
I still receive the error user-datatype not defined.
It seems like he does not understand that we are going to work with an other
application (here MWS Word).
The reference libraries are like i mentionned earlier.

What am i missing ?

[JPDO]

hello Howard,

I found that i did not had the MS Word object library in the EXCELWord procedure.
Now it runs but it stucked on the first bookmark (text1).
Error = 6028 range can not replace/delete (?) bookmark.

What means error 6028 ?
Can you figure out how to solve this ?