0 votes
in VBA by Beginner (3 points)

I have written a macro to search through a document for any acronyms listed in an array and if it finds one, to insert it into a glossary table in the document template along with the corresponding definition in a second array.

It works perfectly when I have both arrays in the code, but it is 237 items long now and I would like to have the ability for people to add items to the list without going into the code.

I have created an Excel workbook with the acronyms in one column, the definitions in the second column and a formula to generate each item into the syntax that would be needed to list them as an array in the code in columns three and four.

eg: Column 3 - astrAcronym(1)="AC", Column 4 - astrDef(1)="Alternating Current"

I have tried to define the array variables to the column ranges in the workbook that contain the acronymn and definition lists, but when I run the macro I get the error message 'Can't assign to array'

Here is the code up to the point where I start looping through the arrays, as I am pretty sure the error is in defining the arrays as the loop works when they are pasted into the code! I have removed all the Word and looping variables for clarity. Let me know if it would be useful to see the whole code.

Thanks in advance for any help - this is the first time I have tried to reference a different application via a Word macro so I am still learning the correct syntax!

Sub GlossaryTable()
    Dim astrAcronym() As Variant
    Dim astrDef() As Variant
    Dim objExcel As New Excel.Application
    Dim objWorkbook As Excel.Workbook

'Open Excel application and definition workbook and assign to variable
Set objWorkbook = objExcel.Workbooks.Open("C:(Filepath)\(Excel Filename).xlsx")

'Define Array for acronyms
Set astrAcronym = objWorkbook("Sheet 1").Range("E1:E237").Value2

'Define Array for definitions
Set astrDef = objWorkbook("Sheet 1").Range("G1:G237").Value2

 

1 Answer

+1 vote
by Beginner (64 points)
edited by

This is revision 1 of my previous answer:

I recommend Range.Value instead of Range.Value2, but that's not the problem. Range.Value2 is a property, not an object. 'Set' is only used with an object. You could try this:

Sub GlossaryTable()
    Dim astrAcronym() As Variant
    Dim astrDef() As Variant
    Dim objExcel As New Excel.Application
    Dim objWorkbook As Excel.Workbook

'Open Excel application and definition workbook and assign to variable
Set objWorkbook = objExcel.Workbooks.Open("C:(Filepath)\(Excel Filename).xlsx")

'Define Array for acronyms
With objWorkbook("Sheet 1").Range("E1:E237")
    ReDim astrAcronym(1 To .Cells.Count)
    For i = 1 To .Cells.Count
        astrAcronym(i) = .Cells(i).Value2
    Next i
End With

'Define Array for definitions
With objWorkbook("Sheet 1").Range("G1:G237")
    ReDim astrDef(1 To .Cells.Count)
    For i = 1 To .Cells.Count
        astrDef(i) = .Cells(i).Value2
    Next i
End With

 

by Beginner (3 points)
Thanks for taking the time to answer!

I tried the method above, but I got the error 'Run-time error '438': Object doesn't support this property or method' on the line: With objWorkbook("Sheet 1").Range("E1:E237")

Is there anything else I need to add to allow Word VBA to read Excel references? I have added the Excel Object Library.
by Beginner (64 points)
+1

Change from   With objWorkbook("Sheet 1").Range("E1:E237") 
to   With objWorkbook.Sheets("Sheet 1").Range("E1:E237") 

Ditto for the other column.

by Beginner (3 points)

I thought we had it there! That stopped the error and it is definitely searching through a list now, but it is not finding any items, whereas when I ran the version with the arrays pasted in the code on the same report it added 12 items to the glossary table. 

Here is the whole code in case you can spot something I haven't noticed:

Sub GlossaryTable()
    Dim docReport As Document
    Dim tblGlossary As Table
    Dim rowNew As Row
    Dim bFound As Boolean
    Dim astrAcronym() As Variant
    Dim astrDef() As Variant
    Dim i As Integer
    Dim objExcel As New Excel.Application
    Dim objWorkbook As Excel.Workbook
        
Application.ScreenUpdating = False

'Open Excel application and definition workbook and assign to variable
Set objWorkbook = objExcel.Workbooks.Open("H:\MEP Definition Table.xlsx")

'Define Array for acronyms
With objWorkbook.Sheets("Sheet 1").Range("E1:E237")
    ReDim astrAcronym(1 To .Cells.Count)
    For i = 1 To .Cells.Count
        astrAcronym(i) = .Cells(i).Value2
    Next i
End With

'Define Array for definitions
With objWorkbook.Sheets("Sheet 1").Range("G1:G237")
    ReDim astrDef(1 To .Cells.Count)
    For i = 1 To .Cells.Count
        astrDef(i) = .Cells(i).Value2
    Next i
End With

'Define Word document and table variables
Set docReport = ActiveDocument
bFound = True
Set tblGlossary = docReport.Tables(3)
With tblGlossary
    .Rows(1).HeadingFormat = True
End With

'Loop through arrays searching for acronyms
For i = LBound(astrAcronym) To UBound(astrAcronym)
Selection.WholeStory
    With Selection.Find
        .MatchWholeWord = True
        .MatchCase = True
        .Text = astrAcronym(i)
        bFound = .Execute
            
            'If acronyms found, paste into Glossary table, adding new rows as needed
            If bFound Then
                With tblGlossary
                    Set rowNew = tblGlossary.Rows.Add(BeforeRow:=tblGlossary.Rows(2))
                        With rowNew
                            .Cells(1).Range.Text = astrAcronym(i)
                            .Cells(2).Range.Text = astrDef(i)
                        End With
                End With
            End If
    End With
Next i

'Delete extra row in Glossary table (template starts with two rows in table)
tblGlossary.Rows.Last.Cells.Delete

Application.Selection.EndOf

Application.ScreenUpdating = True

End Sub

 

by Beginner (64 points)

I'm not a Word VBA expert. Maybe   .Cells(1).Range.Text   should be   .Cells(1).Text   and ditto for the other Cell.

by Beginner (3 points)
Got it to work! The .Cells(1).Range.Text was correct. My problem was that I had used a formula in my Excel chart to generate the syntax as if I was putting it in the code as an array (eg astrAcronym(1)="AC"). I tried just referring to the columns with the basic text in them (eg AC) and it worked! So if you are generating an array from an Excel workbook it looks like you can just enter the data as a normal list, which makes things a lot easier!

Thanks for all your help :)

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.

Register

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...