Imported jpeg document query

[WednesdayC]

Hi All

I suspect the answer to this is No, but just in case....
Can a macro be written to find out the last row of a jpeg document imported into an Excel spreadsheet?

Thank you in advance.

Regards

Wednesday C

 

[Ruddles]

Repeat after me: "Nothing is impossible... nothing is impossible... nothing is impossible...".

Good. Now paste into a new general code module:-

[FONT=Courier New]Option Explicit[/FONT]
 
[FONT=Courier New]Public Function NextRowAfterShape(argSheetName As String,argShapeName As String) As Long[/FONT]
 
[FONT=Courier New]  Dim ws As Worksheet[/FONT]
[FONT=Courier New]  Dim ShapeBottom As Long[/FONT]
[FONT=Courier New]  Dim iRow As Long[/FONT]
 
[FONT=Courier New]  Set ws = ThisWorkbook.Sheets(argSheetName)[/FONT]
 
[FONT=Courier New]  ShapeBottom = ws.Shapes(argShapeName).Top + ws.Shapes(argShapeName).Height[/FONT]
 
[FONT=Courier New]  iRow = 1[/FONT]
[FONT=Courier New]  Do Until ws.Rows(iRow).Top > ShapeBottom[/FONT]
[FONT=Courier New]    iRow = iRow + 1[/FONT]
[FONT=Courier New]  Loop[/FONT]
 
[FONT=Courier New]  NextRowAfterShape = iRow[/FONT]
 
[FONT=Courier New]End Function[/FONT]

Call it from VBA as follows:-

[FONT=Courier New]somevariable = NextRowAfterShape("[COLOR=red][B]Sheet1[/B][/COLOR]","[COLOR=red][B]Picture 1[/B][/COLOR]")[/FONT]

or from the VBA Immediate window (Ctrl-G) like this?:-

[FONT=Courier New]?NextRowAfterShape("[B][COLOR=red]Sheet1[/COLOR][/B]","[COLOR=red][B]Picture 1[/B][/COLOR]")[/FONT]

Obviously you have to put the correct sheet and shape names in.

Note that there's no error checking so if you feed it a duff sheet or shape name, the code will fall over.

Let me know how it goes?

 

[WednesdayC]

Hi Ruddles

A big thank you for your reply. I am amazed this can be done.

However, I am confused by what I use instead of your 'argShapeName' with.

I managed to crop the inserted jpeg file by using the following:-

Sub CropPicture()

 Dim LastRow As Long

   With ActiveSheet.shapes(1)
        
       
        'With .PictureFormat
            .CropLeft = 86.91
            .CropTop = 86.25
           .CropBottom = 81
            .CropRight = 82.09

        End With

I am not sure how to use this Shape name instead of ArgShapeName. I tried ActiveSheet.shapes(1) but didn't seem to work.

Also, should the Dim declaration for this variable be Shape or something else?

Thank you in advance.

Regards
Wednesday

 

[WednesdayC]

Hi Ruddles,

You must think I am dim - I didn't read your reply properly. I now see that 'ArgShapeName' is a string.

I am going to try again and report back.

Regards
Wednesday

 

[Ruddles]

The function expects the name of the worksheet and the name of the shape, so you can either hard-code the names in the function call or, if you're using ActiveSheet and ActiveSheet.Shapes(1), you'd use:-

MsgBox NextRowAfterShape(ActiveSheet.Name,ActiveSheet.Shapes(1).Name)

 

[Ruddles]

Yes, argShapeName is a string because it's expecting to receive the name of the shape (as a string), but if you're assigning the return value of the function to a variable, that variable needs to be a numeric type. In the function definition, the last bit tells you what the return value should be DIMmed as:-

Public Function NextRowAfterShape(argSheetName As String, argShapeName As String) [COLOR=red][B]As Long[/B][/COLOR]

Always use Long to DIM a variable which is intended to hold a row number: you don't need the fractional parts of a Single or Double, and Integer is too small to hold the number of rows possible in a worksheet.

Just use a MsgBox for the moment - just whilst you're checking it's working okay.

 

[WednesdayC]

Ruddles

Me again! I understand what you are saying, but what I do not really understand is how I go about naming the 'Shape'.

I have inserted TestImage.jpeg and as mentioned before, I have managed to do something (cropping)with this document by using ws.Shapes(1).pictureformat.

What I still can't work out is how to assign this shape to your argShapeName string variable. I have now tried everything I can think of, but not working.

Please help one more time.
Thanks

Wednesday

 

[Ruddles]

I'll help you as many times as it takes for you to solve your problem!

The name of Shapes(1) is Shapes(1).Name. If you select the shape on the worksheet, its name will appear in the white area to the left of the formula bar, above cell A1 and below the menu ribbon.

If you know its name and the name of the worksheet, you can call the function from the VBA Immediate window (Ctrl-G) by typing ?NextRowAfterShape("Sheet1","Picture 1") and hitting Enter. (Obviously use the actual worksheet and shape names.)

You can modify your code (temporarily) by adding the bit in red and running it again:-

Sub CropPicture()
 Dim LastRow As Long
[COLOR=red][B]Dim NextRow As Long[/B][/COLOR]
   With ActiveSheet.shapes(1)
 
[COLOR=red][B]NextRow = NextRowAfterShape(ActiveSheet.Name, ActiveSheet.Shapes(1).Name)[/B][/COLOR]
[B][COLOR=#ff0000]MsgBox "Before cropping, next row is " & NextRow[/COLOR][/B]
 
        'With .PictureFormat
            .CropLeft = 86.91
            .CropTop = 86.25
           .CropBottom = 81
            .CropRight = 82.09
        End With
 
[COLOR=red][B][COLOR=red][B]NextRow = NextRowAfterShape(ActiveSheet.Name, ActiveSheet.Shapes(1).Name)[/B][/COLOR][/B]
[B][B][COLOR=#ff0000]MsgBox "After cropping, next row is " & NextRow[/COLOR][/B][/B]
[/COLOR]

Try either - or both - of those suggestions and get back to me.

 

[WednesdayC]

Hi Ruddles

It works!!!! Fantastic!!!

Thank you so much.

I've learnt a few new skills as well.

Regards

Wednesday

 

[Ruddles]

Please help one more time.

You were right then!

Thanks for the feedback.