CODEWITHSUNDEEP
excelvba
MILESOFF
MILESOFF

Reputation: 31

How to set OLEObject name using a variable?

How do I rename an OLEObject?

The object is embedded and the oname variable works when used in the other lines but the .name command will not work. There is no error.

Public Sub insertFiles()
Dim newObject As Object
Dim oname As String
Dim CheckName As String
 
CheckName = UserForm1.MultiPage2.SelectedItem.Caption
oname = CheckName & "_" & "Evidence" & "_" & UserForm1.ProjectName.Value & "_" & Format(Date, "ddmmmyyyy")

Worksheets("Emails").Activate
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Set Rng = ActiveCell
Rng.RowHeight = 70

On Error Resume Next

fpath = Application.GetOpenFilename("All Files,*.*", Title:="Select file")
If LCase(fpath) = "false" Then Exit Sub
    
If UserForm1.ProjectName.Value <> Empty Then
       
    ActiveCell.Value = "."

    ActiveSheet.OLEObjects.Add(Filename:=fpath, _
      Link:=False, _
      DisplayAsIcon:=True, _
      IconFileName:="Outlook.msg", _
      IconIndex:=1, _
      IconLabel:=extractFileName(fpath)).Name = oname
    
    ActiveCell.Offset(0, 1).Value = oname
    UserForm1.Attached1.Value = oname
    ThisWorkbook.Worksheets("Output").Range("B35").Value = oname

    Call UserForm1.Tickbox

    UserForm1.LablePIA.Visible = True
    UserForm1.Attached1.Visible = True
    UserForm1.View.Visible = True
    UserForm1.Deleteemail.Visible = True

    MsgBox "Attachment uploaded"
  
Else
    MsgBox "Project Name must be input before emails can be uploaded"
End If

End Sub

Public Function extractFileName(filePath)
    For i = Len(filePath) To 1 Step -1
        If Mid(filePath, i, 1) = "\" Then
        extractFileName = Mid(filePath, i + 1, Len(filePath) - i + 1)
        Exit Function
        End If
    Next

End Function

Solution:
The string variable contained too many characters, apparently the max is 35.

Upvotes: 1

Views: 1765

Answers (2)

MILESOFF
MILESOFF

Reputation: 31

OLEObject names cannot exceed 35 characters (presumably unless you use a class module etc!).

Upvotes: 2

Thomas G
Thomas G

Reputation: 10216

Try like this

Dim Obj As OLEObject

set Obj = ActiveSheet.OLEObjects.Add(Filename:=fpath, _
    Link:=False, _
    DisplayAsIcon:=True, _
    IconFileName:="Outlook.msg", _
    IconIndex:=1, _
   IconLabel:=extractFileName(fpath))

Obj.name = oname

Upvotes: 1

Related Questions