Search code examples
excelvbavb6

Get the Location of an Image from excel and Display it in textbox

I have a code in VB6 that displays image that comes from an excel into picturebox and here is the code for that.

Dim appExcel As Excel.Application
        Dim xlsBook  As Excel.Workbook
        Dim xlsSheet As Excel.Worksheet
        Dim img      As Image
        Set appExcel = New Excel.Application
        Set xlsBook = appExcel.Workbooks.Open(Text1.Text)
        Set xlsSheet = xlsBook.Worksheets("Sheet1")
        Dim x As Excel.Shape

        For Each x In xlsSheet.Shapes
            x.Copy
            Picture1.Picture = Clipboard.GetData(vbCFBitmap)
            Text2.Text = x.Name
        Next

Near the picturebox i have also a textbox, My goal is to display the cell location of the image. How can I achieve that?

Here is the update.

I have an excel format that looks like this.

enter image description here

Based on the code above I can display image from excel to picturebox for every object near to it. My question is it possible to get also the labels below them?

Updated.

enter image description here

based on what format i am using I try to add a new column below in each image called Image Name and put name called IMAGE 1... until IMAGE 9 since i have 9 images now here is my code.

  Dim appExcel As Excel.Application
        Dim xlsBook  As Excel.Workbook
        Dim xlsSheet As Excel.Worksheet
        Dim img      As Image
        Dim rowlocation As Integer
        Dim columnlocation As Integer
        Dim celladdress As String

        Set appExcel = New Excel.Application
        Set xlsBook = appExcel.Workbooks.Open(Text1.Text)
        Set xlsSheet = xlsBook.Worksheets("Sheet1")


        Dim x As Excel.Shape


        For Each x In xlsSheet.Shapes
            x.Copy
            Picture1.Picture = Clipboard.GetData(vbCFBitmap)
            Text2.Text = x.Name
            rowlocation = x.TopLeftCell.Row
            columnlocation = x.TopLeftCell.Column
            celladdress = Cells(rowlocation + 3, columnlocation + 1).Address(RowAbsolute:=False, ColumnAbsolute:=False)
            MsgBox ActiveSheet.Range(celladdress)

        Next

What happens is that when the image is selected the right Image Name does not come up For example in Image 2 instead the Image Name the word Image Name will show.

Solution

  • Assuming that the Shape is contained in a single cell, you can use:

    x.TopLeftCell.Address

    With a picture, it's much more likely that it covers more than one cell, so you can build the complete Range of cells that it occupies with:

    x.TopLeftCell.Address & ":" & x.BottomRightCell.Address