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!
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