1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92
| Sub LinkToImage()
For Each cel In Selection
cel.Offset(0, 1).Select
cel.Offset(0, 1).RowHeight = 100
cel.Offset(0, 1).ColumnWidth = 20
If URLValid(cel.Value) = 0 Or HttpExists(cel.Value) = 0 Then
cel.Offset(0, 1).Value = "Photo non dispo"
Else
Set Image = ActiveSheet.Pictures.Insert(cel.Value)
With Image
.ShapeRange.LockAspectRatio = msoTrue
.Width = cel.Offset(0, 1).Width
.Height = cel.Offset(0, 1).Height
.Left = cel.Offset(0, 1).Left
.Top = cel.Offset(0, 1).Top
End With
End If
Next cel
ActiveSheet.Shapes.SelectAll
Selection.Placement = xlMoveAndSize
End Sub
Function URLValid(url As String) As Boolean
If InStr(url, "png") > 0 Then
URLValid = True
ElseIf InStr(url, "jpg") > 0 Then
URLValid = True
ElseIf InStr(url, "jpeg") > 0 Then
URLValid = True
ElseIf InStr(url, "bmp") > 0 Then
URLValid = True
Else
URLValid = False
End If
End Function
Function HttpExists(ByVal sURL As String) As Boolean
Dim oXHTTP As Object
Set oXHTTP = CreateObject("MSXML2.XMLHTTP")
On Error GoTo haveError
oXHTTP.Open "HEAD", sURL, False
oXHTTP.send
HttpExists = IIf(oXHTTP.Status = 200, True, False)
Exit Function
haveError:
Debug.Print Err.Description
HttpExists = False
End Function
Sub LinkToImage()
For Each cel In Selection
cel.Offset(0, 1).Select
cel.Offset(0, 1).RowHeight = 100
cel.Offset(0, 1).ColumnWidth = 20
If URLValid(cel.Value) = 0 Or HttpExists(cel.Value) = 0 Then
cel.Offset(0, 1).Value = "Photo non dispo"
Else
Set Image = ActiveSheet.Pictures.Insert(cel.Value)
With Image
.ShapeRange.LockAspectRatio = msoTrue
.Width = cel.Offset(0, 1).Width
.Height = cel.Offset(0, 1).Height
.Left = cel.Offset(0, 1).Left
.Top = cel.Offset(0, 1).Top
End With
End If
Next cel
ActiveSheet.Shapes.SelectAll
Selection.Placement = xlMoveAndSize
End Sub
Function URLValid(url As String) As Boolean
If InStr(url, "png") > 0 Then
URLValid = True
ElseIf InStr(url, "jpg") > 0 Then
URLValid = True
ElseIf InStr(url, "jpeg") > 0 Then
URLValid = True
ElseIf InStr(url, "bmp") > 0 Then
URLValid = True
Else
URLValid = False
End If
End Function
Function HttpExists(ByVal sURL As String) As Boolean
Dim oXHTTP As Object
Set oXHTTP = CreateObject("MSXML2.XMLHTTP")
On Error GoTo haveError
oXHTTP.Open "HEAD", sURL, False
oXHTTP.send
HttpExists = IIf(oXHTTP.Status = 200, True, False)
Exit Function
haveError:
Debug.Print Err.Description
HttpExists = False
End Function |
Partager