title: vba powerpoint populating an array from excel range
body: <p>I´m trying to set a array with data from a MS Excel range. 
  My VBA Macro replaces the text from an array with text from another array. 
  It works fine with arrays, but now I´m trying to fill these arrays with data from a Excel file. I´m using range and I´ve tried thousands of ways of making  itwork, unsuccessfuly.  I´m not a VBA coder, so maybe I´m missing some basic concepts.... :|</p>
  
  <p>Heres the code. Thanks in advance for any help!</p>
  
  <pre><code>Sub ReplacePT2ES()
  
      Dim oSld As Slide
      Dim oShp As Shape
      Dim oTxtRng As TextRange
      Dim oTmpRng As TextRange
      Dim strWhatReplace As String, strReplaceText As String
      Dim x As Long
  
  
      Dim xlApp As Excel.Application
      Dim xlBook As Excel.Workbook
      Dim rng As range
  
  
      Set xlApp = CreateObject("Excel.Application")
      Set xlBook = xlApp.Workbooks.Open("D:\DOCS\DiccionarioPT2ES.xlsx")
      xlBook.Application.Visible = False
      xlBook.Application.WindowState = xlMinimized
  
  
      Dim findList As Variant
      Dim replaceList As Variant
  
      Set findList = range("A1:A3").Value
  
      Set replaceList = range("B1:B3").Value
      '-- works fine with array
      'findList = Array("falha", "lei", "projeto", "falhas", "leis", "projetos", "falham", "os", "as", "gestor")
      'replaceList = Array("falla", "ley", "proyecto", "fallas", "leyes", "proyectos", "fallan", "los", "las", "gerente")
  
      'MsgBox "Iniciando!"
  
      For x = findList.Count To replaceList.Count
          ' go during each slides
          For Each oSld In ActivePresentation.Slides
               ' go during each shapes and textRanges
              For Each oShp In oSld.Shapes
                   ' replace in TextFrame
                  'If oShp.HasTextFrame And UBound(findList) And UBound(replaceList) &gt; 0 Then
                   If oShp.HasTextFrame Then
  
                      Set oTxtRng = oShp.TextFrame.TextRange
                      Set oTmpRng = oTxtRng.Replace(FindWhat:=findList(x), Replacewhat:=replaceList(x), WholeWords:=True)
  
                      Do While Not oTmpRng Is Nothing
  
                          Set oTxtRng = oTxtRng.Characters(oTmpRng.Start + oTmpRng.Length, oTxtRng.Length)
                          Set oTmpRng = oTxtRng.Replace(FindWhat:=findList(x), Replacewhat:=replaceList(x), WholeWords:=True)
                      Loop
                   End If
              Next oShp
          Next oSld
      Next x
  
   xlBook.Close SaveChanges:=False
   Set xlApp = Nothing
   Set xlBook = Nothing
   'MsgBox "Listo!"
  
  
  End Sub
  </code></pre>