I want to embed an OLEObject (text file) in Excel, with the filename being derived from a particular cell. I can do this as a one off action but am now trying to make it work in a loop through all the cells in a column, finishing when it comes across an empty cell.
I can't seem to get the right syntax to make the If/Else loop work:
Sub Insert_Text_File()
Dim ol As OLEObject
Dim path As String
Dim file As String
Dim filenameinvisible As String
Dim rangeA As Range
Dim rangeD As Range
path = ActiveWorkbook.Path
file = Range(i,1).Value & "-Live" & ".txt"
Set rangeA = Range("A" & i)
Set rangeD = Range("D" & i)
For i = 2 To 200
If Range("A" & i) <> "" Then
Set ol = Worksheets("Inventory").OLEObjects.Add (Filename:= path & "" & file, Link:=False, DisplayAsIcon:=True, Height:=10)
ol.Top =Range("D" & i).top
ol.left=Range("D" & i).left
End If
Next i
End Sub
See Question&Answers more detail:os