• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

How do I populate Word controls from an Excel array ?

Hi All,

I have a Word template with a number of ActiveX controls, populated from an Excel user form. I'm trying to include this task in a loop.

How do I code the Word controls to include the variable i, as below ? I've tried numerous permutations in replacing the 1 with an i, but without success.

 Dim i as Integer 
        For i = 1 To 6
            WordDoc.laSession1.Caption = i
            WordDoc.laCourseTitle1.Caption = Me.Controls("coCourseTitle" & i).Value
            WordDoc.laType1.Caption = Me.Controls("coType" & i).Value
            WordDoc.laLevel1.Caption = Me.Controls("coLevel" & i).Value
            WordDoc.laDuration1.Caption = Me.Controls("coDuration" & i).Value
            WordDoc.laDelegates1.Caption = Me.Controls("teNoOfDelegates" & i).Value
            WordDoc.laProposedDate1.Caption = Me.Controls("teTrainingDate" & i).Value
            WordDoc.laPricesInhouse1.Caption = "£ " & Me.Controls("teTotalPrice" & i).Value
            WordDoc.laPricesMaylands1.Caption = "n/a"
         Next i

Open in new window


Many thanks
Toco
0
Tocogroup
Asked:
Tocogroup
  • 5
  • 4
1 Solution
 
Rgonzo1971Commented:
Hi,

pls try to adapt

Sub Macro()
'
'
For i = 1 To 6
    For Each ils In WordDoc.InlineShapes
        Select Case ils.OLEFormat.Object.Name
            Case "laSession" & i
                ils.OLEFormat.Object.Caption = i
            Case "laCourseTitle" & i
                ils.OLEFormat.Object.Caption = Me.Controls("coCourseTitle" & i).Value
            Case "laType" & i
                ils.OLEFormat.Object.Caption = Me.Controls("coType" & i).Value
            Case "laLevel" & i
                ils.OLEFormat.Object.Caption = Me.Controls("coLevel" & i).Value
            Case "laDuration" & i
                ils.OLEFormat.Object.Caption = Me.Controls("coDuration" & i).Value
            Case "laNoOfDelegates" & i
                ils.OLEFormat.Object.Caption = Me.Controls("teNoOfDelegates" & i).Value
            Case "laTrainingDate" & i
                ils.OLEFormat.Object.Caption = Me.Controls("teTrainingDate" & i).Value
            Case "laTotalPrice" & i
                ils.OLEFormat.Object.Caption = "£ " & Me.Controls("teTotalPrice" & i).Value
            Case "laPricesMaylands" & i
                ils.OLEFormat.Object.Caption = "n/a"
        End Select
    Next
Next

End Sub

Open in new window

Regards
0
 
TocogroupAuthor Commented:
Hi,

I defined ils in my Excel VBA procedure as follows:
Dim ils as Object

I'm getting a Run-time error 91 on the Select Case statement after the first pass through.
I'm not sure what's going on here.
Run-time-error-91.jpg
0
 
Rgonzo1971Commented:
Hi,

pls try

Sub Macro()
'
'
For i = 1 To 6
    For Each ils In ActiveDocument.InlineShapes
        If ils.Type = 1 Or ils.Type = 2 Or ils.Type = 5 Then ' wdInlineShapeEmbeddedOLEObject
        Select Case ils.OLEFormat.Object.Name
            Case "laSession" & i
                ils.OLEFormat.Object.Caption = i
            Case "laCourseTitle" & i
                ils.OLEFormat.Object.Caption = Me.Controls("coCourseTitle" & i).Value
            Case "laType" & i
                ils.OLEFormat.Object.Caption = Me.Controls("coType" & i).Value
            Case "laLevel" & i
                ils.OLEFormat.Object.Caption = Me.Controls("coLevel" & i).Value
            Case "laDuration" & i
                ils.OLEFormat.Object.Caption = Me.Controls("coDuration" & i).Value
            Case "laNoOfDelegates" & i
                ils.OLEFormat.Object.Caption = Me.Controls("teNoOfDelegates" & i).Value
            Case "laTrainingDate" & i
                ils.OLEFormat.Object.Caption = Me.Controls("teTrainingDate" & i).Value
            Case "laTotalPrice" & i
                ils.OLEFormat.Object.Caption = "£ " & Me.Controls("teTotalPrice" & i).Value
            Case "laPricesMaylands" & i
                ils.OLEFormat.Object.Caption = "n/a"
        End Select
        End If
    Next
Next

End Sub

Open in new window

regards
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
TocogroupAuthor Commented:
That's great. Finally, is there any way I can clear down (initialise) each Word control in one simple statement prior to the main loop ?
0
 
Rgonzo1971Commented:
What do you mean?
0
 
TocogroupAuthor Commented:
When I run the procedure, the Word document controls are populated from the Excel sheet data as required. Great ! However, not all 6 occurrences of the Word document matrix will always be populated, as my Excel sheet, for example, may only have 4 rows of data.

So, I've added an 'If' statement just before the Select Case statement as follows to test for this instance......

            If Me.Controls("coCourseTitle" & i).Value <> "" Then

This will only populate those items where a Course Title exists for that occurrence.

But if I run the procedure again with different data, some of the previous run's data may still remain in the Word document. hence, why I want to clear out all the Word controls before I perform my main loop.

Does that make sense ?
0
 
TocogroupAuthor Commented:
I should have explained that I'm using the same Word document as a template which is why it needs clearing down before each execution.
0
 
Rgonzo1971Commented:
Hi


For the moment I cannot see any other solution as to clear all first

Sub Macro()
'
'
'Clear First
For i = 1 To 6
    For Each ils In ActiveDocument.InlineShapes
        If ils.Type = 1 Or ils.Type = 2 Or ils.Type = 5 Then ' wdInlineShapeEmbeddedOLEObject
        Select Case ils.OLEFormat.Object.Name
            Case "laSession" & i
                ils.OLEFormat.Object.Caption = ""
            Case "laCourseTitle" & i
                ils.OLEFormat.Object.Caption = ""
            Case "laType" & i
                ils.OLEFormat.Object.Caption = ""
            Case "laLevel" & i
                ils.OLEFormat.Object.Caption = ""
            Case "laDuration" & i
                ils.OLEFormat.Object.Caption = ""
            Case "laNoOfDelegates" & i
                ils.OLEFormat.Object.Caption = ""
            Case "laTrainingDate" & i
                ils.OLEFormat.Object.Caption = ""
            Case "laTotalPrice" & i
                ils.OLEFormat.Object.Caption = ""
            Case "laPricesMaylands" & i
                ils.OLEFormat.Object.Caption = ""
        End Select
        End If
    Next
Next
'Then Populate
For i = 1 To 6
    For Each ils In ActiveDocument.InlineShapes
        If ils.Type = 1 Or ils.Type = 2 Or ils.Type = 5 Then ' wdInlineShapeEmbeddedOLEObject
        Select Case ils.OLEFormat.Object.Name
            Case "laSession" & i
                ils.OLEFormat.Object.Caption = i
            Case "laCourseTitle" & i
                ils.OLEFormat.Object.Caption = Me.Controls("coCourseTitle" & i).Value
            Case "laType" & i
                ils.OLEFormat.Object.Caption = Me.Controls("coType" & i).Value
            Case "laLevel" & i
                ils.OLEFormat.Object.Caption = Me.Controls("coLevel" & i).Value
            Case "laDuration" & i
                ils.OLEFormat.Object.Caption = Me.Controls("coDuration" & i).Value
            Case "laNoOfDelegates" & i
                ils.OLEFormat.Object.Caption = Me.Controls("teNoOfDelegates" & i).Value
            Case "laTrainingDate" & i
                ils.OLEFormat.Object.Caption = Me.Controls("teTrainingDate" & i).Value
            Case "laTotalPrice" & i
                ils.OLEFormat.Object.Caption = "£ " & Me.Controls("teTotalPrice" & i).Value
            Case "laPricesMaylands" & i
                ils.OLEFormat.Object.Caption = "n/a"
        End Select
        End If
    Next
Next

End Sub

Open in new window

Regards
0
 
TocogroupAuthor Commented:
Ok, that's fine. I just wondered if there was a more concise way of clearing down the Word controls in one simple statement.
Many thanks for your help with this. It's been a good learning exercise and it does exactly what I want it to do.
Regards
Toco
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now