Results 1 to 3 of 3

send attachment selected in userform to a specific cell

Threaded View

  1. #1
    Registered User
    Join Date
    03-13-2013
    Location
    Dallas, Texas
    MS-Off Ver
    Excel 2010
    Posts
    6

    send attachment selected in userform to a specific cell

    I found some great code that I attached to a button on my userform. It allows the user to browse for a document, and attach it to the selected cell in the worksheet. Is there any way I could have the attachment display in a certain cell depending on what option was selected in a combobox also on the userform? Below is the code I found. Below that is where I made some minor adjustments to try and send the attachment to a specifc cell, with little success. with my adjustment a label "TRUE" shows up in the correct cell, but the attachment still falls into whatever the active cell is.

    Sub InsertObjectAsIcon()
    'lets user browse for a file to insert into the
    'current active worksheet.
    'all are inserted as icons, not "visible" objects, so
    'to view they will need an appropriate viewer/reader
    'at the recipient end.
    '
    'This one shows how you could set up to use
    'several different icons depending on the type of file
    'inserted.  You'll have to experiment by recording
    'macros while inserting various file types to build
    'up a list to use, just add new Case Is = statements
    'do deal with the file types.  Be sure to enter the
    'file type in all UPPERCASE.
    '
      Dim iconToUse As String
      Dim fullFileName As String
      Dim FNExtension As String
      fullFileName = Application.GetOpenFilename("*.*, All Files", , , , False)
      
      If fullFileName = "False" Then
        Exit Sub ' user cancelled
      End If
    'choose an icon based on filename extension
      'get all after last "." in filename
      FNExtension = Right(fullFileName, Len(fullFileName) - _
       InStrRev(fullFileName, "."))
      
      'select icon based on filename extension
      Select Case UCase(FNExtension)
        Case Is = "TXT"
          iconToUse = "C:\Windows\system32\packager.dll"
        Case Is = "XLS", "XLSM", "XLSX"
          iconToUse = "C:\Windows\Installer\{91140000-0011-0000-0000-0000000FF1CE}\xlicons.exe"
        Case Is = "PDF"
          iconToUse = "C:\Windows\Installer\{AC76BA86-1033-F400-7761-000000000004}\_PDFFile.ico"
        Case Else
          'this is a generic icon
          iconToUse = "C:\Windows\system32\packager.dll"
      End Select
      ActiveSheet.OLEObjects.Add(Filename:=fullFileName, Link:= _
        False, DisplayAsIcon:=True, IconFileName:= _
        iconToUse, IconIndex:=0, IconLabel:=fullFileName).Select
    End Sub

    MY ATTEMPT:

    Sub InsertObjectAsIcon()
    'lets user browse for a file to insert into the
    'current active worksheet.
    'all are inserted as icons, not "visible" objects, so
    'to view they will need an appropriate viewer/reader
    'at the recipient end.
    '
    'This one shows how you could set up to use
    'several different icons depending on the type of file
    'inserted.  You'll have to experiment by recording
    'macros while inserting various file types to build
    'up a list to use, just add new Case Is = statements
    'do deal with the file types.  Be sure to enter the
    'file type in all UPPERCASE.
    '
      Dim iconToUse As String
      Dim fullFileName As String
      Dim FNExtension As String
      fullFileName = Application.GetOpenFilename("*.*, All Files", , , , False)
      
      If fullFileName = "False" Then
        Exit Sub ' user cancelled
      End If
    'choose an icon based on filename extension
      'get all after last "." in filename
      FNExtension = Right(fullFileName, Len(fullFileName) - _
       InStrRev(fullFileName, "."))
      
      'select icon based on filename extension
      Select Case UCase(FNExtension)
        Case Is = "TXT"
          iconToUse = "C:\Windows\system32\packager.dll"
        Case Is = "XLS", "XLSM", "XLSX"
          iconToUse = "C:\Windows\Installer\{91140000-0011-0000-0000-0000000FF1CE}\xlicons.exe"
        Case Is = "PDF"
          iconToUse = "C:\Windows\Installer\{AC76BA86-1033-F400-7761-000000000004}\_PDFFile.ico"
        Case Else
          'this is a generic icon
          iconToUse = "C:\Windows\system32\packager.dll"
      End Select
      
    With Sheets("TestResults").Columns(1)
        Set rFind = .Find(What:=Me.ComboBox1.Value, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
        If Not rFind Is Nothing And Not Me.ComboBox1.Value = "" Then
            rFind.Offset(, 9) = Sheets("TestResults").OLEObjects.Add(Filename:=fullFileName, Link:= _
                False, DisplayAsIcon:=True, IconFileName:= _
                iconToUse, IconIndex:=0, IconLabel:=fullFileName).Select
        End If
    End With
    
    End Sub
    Last edited by arlu1201; 03-22-2013 at 03:52 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1