Astuce: Les autres langues sont Google-Traduction. Vous pouvez visiter le English version de ce lien.
Se connecter
x
or
x
x
S'enregistrer
x

or

Comment convertir les URL d'images en images réelles dans Excel?

Si vous avez une liste d'adresses URL d'images dans la colonne A, et maintenant, vous voulez télécharger les images correspondantes à partir des URL et les afficher dans la colonne B adjacente comme sur la capture d'écran suivante. Dans Excel, comment pouvez-vous extraire les images réelles des URL de l'image rapidement et facilement?

doc url à l'image 1

Convertir les URL de l'image en images réelles avec le code VBA


Masquer le nom du fichier sur les images réelles à la fois dans Excel:

Avec Kutools for Excel's Importer des images importées fonctionnalité, vous pouvez rapidement importer plusieurs images dans les cellules correspondantes en fonction des noms de fichiers.

doc url à l'image 2

Kutools for Excel: avec plus que 200 compléments Excel pratiques, libre d'essayer sans limitation dans les jours 60. Téléchargez et essai gratuit maintenant!


flèche bleue droite bulle Convertir les URL de l'image en images réelles avec le code VBA

Le code VBA suivant peut vous aider à extraire rapidement les images réelles à partir des adresses URL de l'image, procédez comme suit:

1. Maintenez le ALT + F11 clés pour ouvrir le Microsoft Visual Basic pour applications fenêtre.

2. Cliquez insérer > Moduleet collez le code suivant dans le Module Fenêtre.

Code VBA: Convertir les URL de l'image en images réelles:

Sub URLPictureInsert()
'Updateby Extendoffice 20161116
    Dim Pshp As Shape
    Dim xRg As Range
    Dim xCol As Long
    On Error Resume Next
    Application.ScreenUpdating = False
    Set Rng = ActiveSheet.Range("A2:A6")
    For Each cell In Rng
        filenam = cell
        ActiveSheet.Pictures.Insert(filenam).Select
        Set Pshp = Selection.ShapeRange.Item(1)
        If Pshp Is Nothing Then GoTo lab
        xCol = cell.Column + 1
        Set xRg = Cells(cell.Row, xCol)
        With Pshp
            .LockAspectRatio = msoFalse
            .Width = 100
           .Height = 100
            .Top = xRg.Top + (xRg.Height - .Height) / 2
            .Left = xRg.Left + (xRg.Width - .Width) / 2
        End With
lab:
    Set Pshp = Nothing
    Range("A2").Select
    Next
    Application.ScreenUpdating = True
End Sub

Note: Dans le code ci-dessus, A2: A6 est la plage de cellules qui contient les adresses URL que vous souhaitez extraire les images, et vous pouvez également changer la taille de la largeur et la hauteur de l'image à votre besoin dans le Largeur = 100, Hauteur = 100 scripts.

3. Puis appuyez F5 clé pour exécuter ce code, et toutes les images correspondantes ont été extraites des URL de l'image à la colonne adjacente à la fois, voir capture d'écran:

doc url à l'image 1



Outils de productivité recommandés

Office Tab

étoile d'or1 Apportez des onglets pratiques à Excel et à d'autres logiciels Office, tout comme Chrome, Firefox et Internet Explorer.

Kutools for Excel

étoile d'or1 Incroyable! Augmentez votre productivité dans les minutes 5. Ne nécessite pas de compétences particulières, économisez deux heures par jour!

étoile d'or1 300 Nouvelles fonctionnalités pour Excel, rendent Excel facile et puissant:

  • Fusionner des cellules / lignes / colonnes sans perdre de données.
  • Combiner et consolider plusieurs feuilles et classeurs.
  • Comparez les gammes, copiez plusieurs gammes, convertissez le texte en date, l'unité et la conversion de devise.
  • Compter par couleurs, sous-totaux de recherche, tri avancé et super filtre,
  • Plus Sélectionner / Insérer / Supprimer / Texte / Format / Lier / Commenter / Classeurs / Feuilles de calcul Outils ...

Capture d'écran de Kutools pour Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Ed · 9 months ago
    Hi all, This Macro is great. However, it does not really add pictures to the file but links that generate pictures each time I open the file. Thus I cannot compress pictures as they are not really inside the file. Could you please help me to really save pictures inside the file?
    • To post as a guest, your comment is unpublished.
      enrico rinero · 2 months ago
      Hi! I have the same question.
      There is a way to save the image effectively in the excel? (and not linked outside)
  • To post as a guest, your comment is unpublished.
    Shozib · 10 months ago
    Hi skyyang, first for such an informative post. I have a question, could you please help me. Using this code of your's I want to extract just one pic and want to place it in a specific cell. Is it possible??
    • To post as a guest, your comment is unpublished.
      skyyang · 10 months ago
      Hello, Shozib,
      To put the images to any other cells as you want, please apply the following VBA code:

      Sub URLPictureInsert1()
      'Updateby Extendoffice 20180608
      Dim Pshp As Shape
      Dim xRg As Range
      Dim xCol As Long
      On Error Resume Next
      Set Rng = Application.InputBox("Please select the url cells:", "KuTools for excel", Selection.Address, , , , , 8)
      If Rng Is Nothing Then Exit Sub
      Set xRg = Application.InputBox("Please select a cell to put the image:", "KuTools for excel", , , , , , 8)
      If xRg Is Nothing Then Exit Sub
      Application.ScreenUpdating = False
      For I = 1 To Rng.Count
      filenam = Rng(I)
      ActiveSheet.Pictures.Insert(filenam).Select
      Set Pshp = Selection.ShapeRange.Item(1)
      If Pshp Is Nothing Then GoTo lab
      xCol = cell.Column + 1
      Set xRg = xRg.Offset(I - 1, 0)
      With Pshp
      .LockAspectRatio = msoFalse
      .Width = 80
      .Height = 80
      .Top = xRg.Top + (xRg.Height - .Height) / 2
      .Left = xRg.Left + (xRg.Width - .Width) / 2
      End With
      lab:
      Set Pshp = Nothing
      Range("A2").Select
      Next
      Application.ScreenUpdating = True
      End Sub

      Please try it, hope it can help you!
  • To post as a guest, your comment is unpublished.
    SamR · 11 months ago
    I'm using an Intel I5 processor, windows 7 professional 64bit, Office 2016 64 bit. I assume that the problem is a setting in Excel. Any help would be greatly appreciated.
    range is set, filenam = url and changes with each pass, Pshp is always nothing, URL's are verified, Any Ideas
  • To post as a guest, your comment is unpublished.
    Sam Ruppenthal · 11 months ago
    range is set, filenam = url and changes with each pass, Pshp is always nothing. Any Ideas
  • To post as a guest, your comment is unpublished.
    Zafar Khan · 1 years ago
    Thanks for your post. How I can get the pictures in the center of the cell. With the current codes the pictures are showing in the top left corner
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, Khan,
      the following VBA code can help you to extract the picture and put them into the center of the cells, please try it.

      Sub URLPictureInsert()
      Dim Pshp As Shape
      Dim xRg As Range
      Dim xCol As Long
      On Error Resume Next
      Application.ScreenUpdating = False
      Set Rng = ActiveSheet.Range("A2:A6")
      For Each cell In Rng
      filenam = cell
      ActiveSheet.Pictures.Insert(filenam).Select
      Set Pshp = Selection.ShapeRange.Item(1)
      If Pshp Is Nothing Then GoTo lab
      xCol = cell.Column + 1
      Set xRg = Cells(cell.Row, xCol)
      With Pshp
      .LockAspectRatio = msoFalse
      If .Width > xRg.Width Then .Width = xRg.Width * 2 / 3
      If .Height > xRg.Height Then .Height = xRg.Height * 2 / 3
      .Top = xRg.Top + (xRg.Height - .Height) / 2
      .Left = xRg.Left + (xRg.Width - .Width) / 2
      End With
      lab:
      Set Pshp = Nothing
      Range("A2").Select
      Next
      Application.ScreenUpdating = True
      End Sub
      • To post as a guest, your comment is unpublished.
        Vipin Sahu · 11 months ago
        Hello,
        I want to download the images from the URL's (Column A) in a specific folder and rename the same images with a code/number which is there in Column B.
        • To post as a guest, your comment is unpublished.
          skyyang · 11 months ago
          Hello, Vipin,
          To save the URL'S images into a folder and rename them with the new names in Column B, may be the following VBA code can help you:

          Note: Column A contains the URL addresses and Column B has the new names.

          #If VBA7 Then
          Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
          #Else
          Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
          #End If
          Sub URLPictureInsert()
          Dim I As Integer
          Dim xStr As String
          Dim xFd As FileDialog
          Dim xArr, xFdItem As Variant
          Application.ScreenUpdating = False
          Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
          If xFd.Show = -1 Then
          xFdItem = xFd.SelectedItems.Item(1)
          xArr = Range("A2:B5").Value
          For I = 1 To UBound(xArr)
          If xArr(I, 1) <> "" Then
          xStr = Mid(xArr(I, 1), InStrRev(xArr(I, 1), "."), Len(xArr(I, 1)))
          URLDownloadToFile 0, xArr(I, 1), xFdItem & "\" & xArr(I, 2) & I & xStr, 0, 0
          End If
          Next
          End If
          Application.ScreenUpdating = True
          End Sub

          Please try it, hope this can help you!
          • To post as a guest, your comment is unpublished.
            David · 3 months ago
            the renaming part on column B uploads into the folder but is adding a number count next to the name of the picture, how I can omit that?

            Thank you
            • To post as a guest, your comment is unpublished.
              skyyang · 3 months ago
              Hi, David,
              May be the below vba code can help you to extract the iimages and rename them wihtout the subfix number, please try. Hope it can help you!

              #If VBA7 Then
              Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
              #Else
              Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
              #End If
              Sub URLPictureInsert()
              Dim I As Integer
              Dim xStr As String
              Dim xFd As FileDialog
              Dim xArr, xFdItem As Variant
              Application.ScreenUpdating = False
              Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
              If xFd.Show = -1 Then
              xFdItem = xFd.SelectedItems.Item(1)
              xArr = Range("A2:B5").Value
              For I = 1 To UBound(xArr)
              If xArr(I, 1) <> "" Then
              xStr = Mid(xArr(I, 1), InStrRev(xArr(I, 1), "."), Len(xArr(I, 1)))
              URLDownloadToFile 0, xArr(I, 1), xFdItem & "\" & xArr(I, 2) & xStr, 0, 0
              End If
              Next
              End If
              Application.ScreenUpdating = True
              End Sub
              • To post as a guest, your comment is unpublished.
                david · 2 months ago
                Omg!! You just provided a miracle, It works like a charm. Thank you so much.
      • To post as a guest, your comment is unpublished.
        Justin · 1 years ago
        Couldn't get this to work can you show the completed code for this request?