VBA code to insert images into excel can not recognize images that have letters and special characters in the image name

1

I use the following VBA code to grab images from a folder and insert it into a spreadsheet, but the VBA code can not recognize images that have letters and special characters in the image name, eg: (SP20-TP02), if the name of the image is just number (2020), works perfectly, it follows the code:

Public Function getImage(ByVal sCode As String) As String

    Dim sFile As String
    Dim oSheet As Worksheet
    Dim oCell As Range
    Dim oImage As Shape

    Set oCell = Application.Caller ' Célula onde a função foi chamada
    Set oSheet = oCell.Parent      ' Planilha que chamou a função

    ' Procura por uma imagem existente identificada pelo código (que precisa ser único!)
    Set oImage = Nothing
    For i = 1 To oSheet.Shapes.Count
        If oSheet.Shapes(i).Name = sCode Then
            Set oImage = oSheet.Shapes(i)
            Exit For
        End If
    Next i


    ' Se ainda não existir uma imagem com o código, carrega do arquivo e cria-a.
    ' A imagem já é posicionada na exata posição da célula onde a função foi chamada.
    If oImage Is Nothing Then
        sFile = "C:\macro\" & sCode & ".jpg"
        Set oImage = oSheet.Shapes.AddPicture(sFile, msoCTrue, msoCTrue, oCell.Left, oCell.Top, oCell.Width, oCell.Height)
        oImage.Name = sCode

    ' Caso contrário, se a imagem já existir, garante que ela se posiciona e cabe exatamente dentro da célula
    ' (apenas para o caso do usuário ter movido ou redimensionado manualmente a imagem sem querer)
    Else
        With oImage
            .Left = oCell.Left
            .Top = oCell.Top
            .Width = oCell.Width
            .Height = oCell.Height
        End With
    End If

    ' Retorna nada para a célula (afinal, esta é somente uma função de auxílio)
    getImage = ""

End Function
    
asked by anonymous 09.05.2018 / 14:03

1 answer

0
The problem with its function is that when you enter: =getImage(SP20-TP02) Excel converts SP20-TP02 to SP20-TP2 and understands how a cell range from column SP and line 20 to column TP line 2, if they are empty returns 0.

One way to check this is to insert the code MsgBox "'" & sCode & "'" , which will return as the function is understanding the inserted String.

Solution

1 - Convert the desired value to String format with: =TEXTO("SP20-TP02";"@")

Then the formula is: =getImage(TEXTO("SP20-TP02";"@"))

2 - Enter the value in a cell and reference in the UDF

If for example the desired code is entered in cell B1, the formula is: =getImage(B1)

    
09.05.2018 / 16:24