Macro with FOR - IF Loop

[Ruth]

Hi,

The idea with the macro below is to take several cells from a row in EXCEL
and use then to fill in a form previously defined in a WORD file. The
approach below in principle is working fine but I guess there should a more
efficient way to do the loop(I use a main "For" loop with several "IF"
nested, one for each potential field to be filled in the form). As I said, I
checked it out and it works but the form has around 30 fields and I would
like to know whether there is a more elegant way to program it.

Thanks!!


Dim wordApp, wdFF As Object, wordFile As Object, myFile As String, I,
fila As Integer
myFile = "NEW_FORM.doc"
Set wordApp = CreateObject("Word.Application")
Set wordFile = wordApp.Documents.Open(myFile)
.......
fila = 3
I = 1
For Each wdFF In wordFile.Formfields
If wdFF.Name = "Text1" Then
wdFF.Result = Range("H" & fila)
End If
If wdFF.Name = "Dropdown2" Then
wdFF.DropDown.Value = Left(Range("A" & fila), 1) + 1
End If

If wdFF.Name = "Text3" Then
wdFF.Result = Range("T" & fila)
End If
...................
I = I + 1
Next
wordApp.Quit
Set wordFile = Nothing
Set wordApp = Nothing
End Sub

 

[Per Jessen]

Hi,

The idea with the macro below is to take several cells from a row in EXCEL
and use then to fill in a form previously defined in a WORD file. The
approach below in principle is working fine but I guess there should a
more
efficient way to do the loop(I use a main "For" loop with several "IF"
nested, one for each potential field to be filled in the form). As I
said, I
checked it out and it works but the form has around 30 fields and I would
like to know whether there is a more elegant way to program it.

Thanks!!


Dim wordApp, wdFF As Object, wordFile As Object, myFile As String, I,
fila As Integer
myFile = "NEW_FORM.doc"
Set wordApp = CreateObject("Word.Application")
Set wordFile = wordApp.Documents.Open(myFile)
.......
fila = 3
I = 1
For Each wdFF In wordFile.Formfields
If wdFF.Name = "Text1" Then
wdFF.Result = Range("H" & fila)
End If
If wdFF.Name = "Dropdown2" Then
wdFF.DropDown.Value = Left(Range("A" & fila), 1) + 1
End If

If wdFF.Name = "Text3" Then
wdFF.Result = Range("T" & fila)
End If
...................
I = I + 1
Next
wordApp.Quit
Set wordFile = Nothing
Set wordApp = Nothing
End Sub

Hi Ruth

With "Select Case" your code will be like this:

Sub BuildForm()
fila = 3
I = 1
For Each wdFF In wordFile.Formfields
Select Case wdFF
Case wd.Name = "Text1"
wdFF.Result = Range("H" & fila)
Case wd.Name = "Dropdown2"
wdFF.DropDown.Value = Left(Range("A" & fila), 1) + 1
Case wdFF.Name = "Text3"
wdFF.Result = Range("T" & fila)
End Select
Next
End Sub

Regards,

Per

 

[Ruth]

Hi Per

Thanks for your help. My idea was to get something like the code you propose
however, I tried with your code and it doesn't work ... and I don't know why.
In fact, it executes fine but the CASE selection doesn't seem to work as it
doesn't execute any of them, so af the end the document is empty and no data
was copied.

Any idea of why this might happen?

Note: Just to make sure, I corrected a spelling mistake in your code as it
should be Case wdFF.Name for Text1 and Dropdown2. Isn't it?.

 

[Andrew Taylor]

The Select Case code should go like this:

Select Case wdFF.Name
Case "Text1"
wdFF.Result = Range("H" & fila)
etc....

 

[Ruth]

Thanks Andrew!
It is perfect like that. It worked fine. Any thought maybe about the problem
I explained in the previous post?

 

[Andrew Taylor]

Yes - see my reply in the other thread
(String vs LONG format - if that's the "previous post" you mean)

A