Find and Replace: Reference data in a closed workbook

[Raybob]

I'm curious if there is a way I can call for the values of Columns("A") in Sheet1 of a closed workbook, so that the values can be used as an array for a Find/Replace Array? It seems feasible...


Sub Find_Replace_from_Excel()

'PURPOSE: Find & Replace a list of text/values throughout entire PowerPoint presentation from values in a reference workbook
Dim sld As Slide
Dim shp As Shape
Dim ShpTxt As TextRange
Dim TmpTxt As TextRange
Dim FindList As Variant
Dim ReplaceList As Variant
Dim x As Long
Dim i As Long
Dim j As Long
Dim tbl As Table
' FindList from a workbook column "A"
FindList = GetObject("C:\Users\Joe\Desktop\Test.xlxs").Sheets(1).Columns("A")
' ReplaceList from workbook column "B"
ReplaceList = GetObject("C:\Users\Joe\Desktop\Test.xlxs").Sheets(1).Columns("B")
'Loop through each slide in Presentation
For Each sld In ActivePresentation.Slides
For Each shp In sld.Shapes
'for tables
If shp.HasTable Then
'give name to table
Set tbl = shp.Table
'loops on table rows and columns
For i = 1 To shp.Table.Rows.Count
For j = 1 To shp.Table.Columns.Count
'Store cell text into a variable
Set ShpTxt = tbl.Cell(i, j).Shape.TextFrame.TextRange
'Ensure There is Text To Search Through
If ShpTxt <> "" Then
For x = LBound(FindList) To UBound(FindList)
'Find First Instance of "Find" word (if exists)
Set TmpTxt = ShpTxt.Replace( _
FindWhat:=FindList(x), _
Replacewhat:=ReplaceList(x), _
WholeWords:=False)
'Find Any Additional instances of "Find" word (if exists)
Do While Not TmpTxt Is Nothing
Set ShpTxt = ShpTxt.Characters(TmpTxt.Start + TmpTxt.Length, ShpTxt.Length)
Set TmpTxt = ShpTxt.Replace( _
FindWhat:=FindList(x), _
Replacewhat:=ReplaceList(x), _
WholeWords:=False)
Loop
Next x
End If
Next j
Next i
Else
''''for all shapes excluding tables
If shp.HasTextFrame Then
'Store shape text into a variable
Set ShpTxt = shp.TextFrame.TextRange
'Ensure There is Text To Search Through
If ShpTxt <> "" Then
For x = LBound(FindList) To UBound(FindList)
'Find First Instance of "Find" word (if exists)
Set TmpTxt = ShpTxt.Replace( _
FindWhat:=FindList(x), _
Replacewhat:=ReplaceList(x), _
WholeWords:=False)
'Find Any Additional instances of "Find" word (if exists)
Do While Not TmpTxt Is Nothing
Set ShpTxt = ShpTxt.Characters(TmpTxt.Start + TmpTxt.Length, ShpTxt.Length)
Set TmpTxt = ShpTxt.Replace( _
FindWhat:=FindList(x), _
Replacewhat:=ReplaceList(x), _
WholeWords:=False)
Loop
Next x
End If
End If
End If
Next shp
Next sld
End Sub