Copying Range from Excel efficiently to Word array (VBA)

I'm dealing with some performance issues on word-VBA. I've got an excel sheet which is touched every time when I r开发者_开发百科ead out a value from a cell, which leeds to very long execution times. Is there any more efficient way to read out the excel sheet at once and paste it into an (string?) arrayso that only 1 access to the excel sheet is necessary? All other operations shall be done by accessing the values in the array.

Greets, poeschlorn

---

From your description, although I am not sure of the meaning of "touched" in your question, I guess the workbook is being re-calculated upon opening. You could inhibit this behaviour by adding this to the Workbook VBA:

Private Sub Workbook_Open()
    Application.Calculation = xlCalculationManual
End Sub 

Just remember that the same will happen when your users open the workbook manually.

HTH

---

I'm not quite sure, I understand what you want. But on this Microsoft Support Site is an explanation of converting an Excel range into an array. The same thing should be possible from Word.

In short:

   Sub Sheet_Fill_Array()
      Dim myarray As Variant
      myarray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
      Range("a1:a10").Value = Application.WorksheetFunction.Transpose(myarray)
   End Sub