Solved

How do I populate Word controls from an Excel array ?

Posted on 2013-12-17
9
205 Views
Last Modified: 2013-12-18
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
Comment
Question by:Tocogroup
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 39724478
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
 

Author Comment

by:Tocogroup
ID: 39724951
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
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 39726976
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:Tocogroup
ID: 39728495
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
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 39728500
What do you mean?
0
 

Author Comment

by:Tocogroup
ID: 39728539
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
 

Author Comment

by:Tocogroup
ID: 39728547
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
 
LVL 51

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39728551
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
 

Author Closing Comment

by:Tocogroup
ID: 39728562
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question