2

All, I have an Excel Spreadsheet that has a row of Images that were add to the sheet by Insert\Picture From File popup window. Instead of imbedding the image I choose the option to link to File. I’m now moving the sheet to an Access DB but I can’t figure out how to extract the path information for each linked image from the image row?

Does anyone know how I would accomplish this? Any help would be greatly appreciated, thanks in advance - CES

Community
  • 1
  • 1
CES
  • 87
  • 12

2 Answers2

0

The pictures that you insert in Excel through Insert\Picture From File popup window are not saved as paths. They "live" into the Excel application.

In order to "move" them to an Access DB, you have to save them somewhere and thus remember the path. Based on the answer here Export Pictures Excel VBA, this is a way to do it:

  • put the picture to a chart
  • fix the size
  • export the chart
  • delete the chart
Public Sub TestMe()

    Dim pic As Shape
    Dim chrt As Chart
    Dim cnt As Long
    Dim ws As Worksheet
    Dim myPath As String

    Application.ScreenUpdating = False
    myPath = "C:\path\"
    Set ws = Worksheets(1)

    For cnt = 1 To ws.Shapes.Count
        If InStr(ws.Shapes(cnt).Name, "") > 0 Then
            Set chrt = Charts.Add.Location(Where:=xlLocationAsObject, Name:=ws.Name)
            chrt.ChartArea.Width = ws.Shapes(cnt).Width
            chrt.ChartArea.Height = ws.Shapes(cnt).Height
            chrt.Parent.Border.LineStyle = 0

            ws.Shapes(cnt).Copy
            chrt.ChartArea.Select
            chrt.Paste
            chrt.Export Filename:=myPath & ws.Shapes(cnt).Name & ".jpg"
            ws.ChartObjects(ws.ChartObjects.Count).Delete
        End If
    Next cnt

    Application.ScreenUpdating = True

End Sub
Vityata
  • 42,633
  • 8
  • 55
  • 100
0

This is not a vba code answer but it may help you achieve what you need.

You may be able to get the link locations, if your file is saved in an excel compressed format, I think this is only from excel 2010 onwards:

Save a copy of the file and rename the file to filename.zip

Extract the Zip file . Navigate the folder structure: \xl\drawings_rels\

In there you should find the reference files, for example 'drawing1.xml.rels'

<?xml version="1.0" encoding="UTF-8" standalone="true"?>

-<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">

<Relationship TargetMode="External" Target="file:///C:\Users\exampleuser\Pictures\myimage.png" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" Id="rId1"/>

From here you can extract the list of links with their Id's

Navigate to the folder: \xl\drawings\ and open up the drawings xml file, from here you can find the image Ids and their Excel name, also you may be able to work out which row the image is in from the rest of the xml code.

-<xdr:pic> 
-<xdr:nvPicPr>
<xdr:cNvPr name="Picture 3" id="4"/>
-<xdr:cNvPicPr>
<a:picLocks noChangeAspect="1"/>
</xdr:cNvPicPr>
</xdr:nvPicPr>
-<xdr:blipFill>
<a:blip r:link="rId1" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"/>
-<a:stretch>
<a:fillRect/>
</a:stretch>
</xdr:blipFill> 
</Relationships>

With this information, you should be able to workout which picture links to which file and its location.

5202456
  • 966
  • 14
  • 24