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

Question

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.

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.

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.

score 4 · Accepted Answer · answered Dec 01 '16 at 04:04

4

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

answered Dec 01 '16 at 04:04

Comintern

21,855
5
33
80

Probably your right the image will occupy not just one cell. I will try this and inform you once its done :) – Shadow Fiend Dec 01 '16 at 04:51
sir can i ask you one more question? – Shadow Fiend Dec 01 '16 at 05:10
@ShadowFiend - Knock yourself out. – Comintern Dec 01 '16 at 05:15
It makes me look quite idiot to search what the meaning of "Knock yourself out" because I understand it quite bad but it means go for it. Sir i will edit my post so you can understand furthermore. TYSM – Shadow Fiend Dec 01 '16 at 05:20
2

@ShadowFiend - Sorry, U.S. English idiom for "absolutely, try your hardest." ;-) – Comintern Dec 01 '16 at 05:23
Sir pls see the edited question and feel free to ask for clarification – Shadow Fiend Dec 01 '16 at 05:29
Sir what im trying to do is to get also the label of the image and transfer it in textbox the label `Gondola Type` and `No of. Unit/SQM` transfer there values in textboxes and my problem here sir that even though i have a proper format in excel i cannot guaranteed that the format will be follow (example improper way of putting the image) so i guess the best way to do is to get the label nearest to the image – Shadow Fiend Dec 01 '16 at 05:33
@ShadowFiend - Once you have the `Range`, you can use `.Offset` ([documentation here](https://msdn.microsoft.com/en-us/library/office/ff840060.aspx)) to access cells underneath it. You may have to play around with it a little bit to find the cells with values in them though - it will depend on how the worksheet is structured. – Comintern Dec 01 '16 at 05:33
Sir actually before you said that i already tried it. I will edit my post so you can understand more. – Shadow Fiend Dec 01 '16 at 05:35
@ShadowFiend - `BottomRightCell` and `TopLeftCell` both return a `Range`, so you might want to create a bound using the `.Column` and `.Row` properties. – Comintern Dec 01 '16 at 05:35
Sir pls see the updated and I think the final post. TYSM for a bigbig help – Shadow Fiend Dec 01 '16 at 05:44
@ShadowFiend - See what you get for `xlsSheet.Cells(x.BottomRightCell.Row + 1, x.TopLeftCell.Column).Value`. My guess based on the screenshots is that it should be pretty close to what you're looking for - it might need some tweaking from there though. – Comintern Dec 01 '16 at 05:47
The code below sir is what Im currently using to display the data below the current image – Shadow Fiend Dec 01 '16 at 05:47
@ShadowFiend - Shapes aren't tied directly to column and row boundries - you can always test to see if you get `Image Name` as the value, and then get the cell to the right if you do. – Comintern Dec 01 '16 at 05:51
Your right sir If I want, I will resize the image so it will perfectly fit in just one cell and because of that the right label will be extracted also but im sure in the future is that the user will not do that because it will consume more time . I tried your code sir and happy to say 7 matches out of 9 images – Shadow Fiend Dec 01 '16 at 05:57
i try to adjust the image location perfectly below each label and the result is 100% match on all name but it will not be happen when user use it – Shadow Fiend Dec 01 '16 at 06:05
are you online today? – Shadow Fiend Dec 02 '16 at 04:30

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

1 Answers1