Error Handling in VBA

[J3nny]

Hi,

Please excuse my total ignorance in VBA, and I've tried multiple solutions myself but my fixes are making things worse.

I've found a wonderful bit of VBA here - Excel - A macro to insert pictures - which I'm using to import photographs en-mass from a folder into Excel. It works very well, but my problem is that the images can have either jpg, gif or jpeg file extensions. The photo import is working perfectly for jpg files, but what i want it to do is:

Try to import the file as a .jpg extension
If the file is imported correctly, then move on to resizing it, etc. (ResumeFromHere
If the import fails then try to import the file as a .gif extension (ErrorTryGif
If the file is imported correctly, then move on to resizing it, etc. (ResumeFromHere
If the import fails then try to import the file as a .jpeg extension (ErrorTryJpeg
If the file is imported correctly, then move on to resizing it, etc. (ResumeFromHere
If the import fails then go right to the end of the VBA where the 1ThisRow value is updated to +1 to move on to the next record, and go back to the start. (ErrorHandler

I don't think this should be too complicated but i have no idea what to put where to get this to work. Could someone please give me a clue what I need to fix in the VBA below?

JennyB

Sub GetPicture()
 Dim picname As String
    
 Dim pasteAt As Integer
 Dim lThisRow As Long
    
    lThisRow = 5
    
    Do While (Cells(lThisRow, 2) <> "")
       
        'Range("A6").Select 'This is where picture will be inserted
        'pasteAt = Cells(lThisRow, 1)
        Cells(lThisRow, 1).Select 'This is where picture will be inserted
           
        'Dim picname As String
        'picname = Range("B6") 'This is the picture name
         picname = Cells(lThisRow, 17) 'This is the picture name"
        On Error GoTo ErrorTryGif
        ActiveSheet.Pictures.Insert("//location/" & picname & ".jpg").Select 'Path to where pictures are stored
        GoTo ResumeFromHere
ResumeFromHere:
        ' This resizes the picture
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''
        With Selection
            '.Left = Range("A6").Left
            '.Top = Range("A6").Top
            .Left = Cells(pasteAt, 1).Left
            .Top = Cells(pasteAt, 1).Top
               
            .ShapeRange.LockAspectRatio = msoFalse
            .ShapeRange.Height = 100#
            .ShapeRange.Width = 80#
            .ShapeRange.Rotation = 0#
        End With

ErrorHandler:
           Resume Next
        lThisRow = lThisRow + 1
       
    Loop
       
    Range("A10").Select
    Application.ScreenUpdating = True
       
    Exit Sub
       
ErrNoPhoto:
    MsgBox "Unable to Find Photo" 'Shows message box if picture not found
    Exit Sub
    Range("A1").Select
ErrorTryGif:
        On Error GoTo ErrorTryJpeg
        ActiveSheet.Pictures.Insert("//location/" & picname & ".gif").Select 'Path to where pictures are stored
        GoTo ResumeFromHere
ErrorTryJpeg:
        On Error GoTo ErrorHandler
        ActiveSheet.Pictures.Insert("//location/" & picname & ".jpeg").Select 'Path to where pictures are stored
        GoTo ResumeFromHere

End Sub

 

[J3nny]

Re: Help with Error Handling in VBA

I'm basically just trying to get it to try this line of code:
ActiveSheet.Pictures.Insert("//location/" & picname & ".jpg").Select

And if that works, move on. If it doesn't, then try this:
ActiveSheet.Pictures.Insert("//location/" & picname & ".gif").Select

And if that works, move on. If it doesn't, then try this:
ActiveSheet.Pictures.Insert("//location/" & picname & ".jpeg").Select

And if that works, move on. If it doesn't, then jump to this line ( i.e. give up and move onto the next line):
lThisRow = lThisRow + 1

The basic code works beautifully, it's only when it can't find the .jpg file that it has issues, when it hits my dodgy error handling. :/

 

[J3nny]

Re: Help with Error Handling in VBA

This is the last, working code with other bugs fixed, which is fine apart from it not liking the nested error handling. When it gets to a row which has a .jpeg image, it fails with the initial .jpg file upload, then it drops into the ErrorTryGif handler, then it fails with an error on the ActiveSheet.Pictures.Insert("//location/" & picname & ".gif").Select, and the 'On Error GoTo ErrorTryJpeg' doesn't catch the error and drop it into the next set of error handling which would make it try to find a .jpeg file. Which from a quick google it seems like is correct - you can't have an On Error within error handling - but the alternative examples arent making a lot of sense to me. But it's 6pm here and I've been looking at this for too many hours, so I'm going to go home and look at it again in the morning. Any help would be very much appreciated.

Sub GetPicture()
 Dim picname As String
    
 Dim pasteAt As Integer
 Dim lThisRow As Long
    
    lThisRow = 5
    
    Do While (Cells(lThisRow, 2) <> "")
       
        'Range("A6").Select 'This is where picture will be inserted
        'pasteAt = Cells(lThisRow, 3)
         Cells(lThisRow, 1).Select 'This is where picture will be inserted
           
        'Dim picname As String
        'picname = Range("B6") 'This is the picture name
         picname = Cells(lThisRow, 17) 'This is the picture name"
        On Error GoTo ErrorTryGif
        ActiveSheet.Pictures.Insert("//location/" & picname & ".jpg").Select 'Path to where pictures are stored
        ' This resizes the picture
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''
        With Selection
            '.Left = Range("A6").Left
            '.Top = Range("A6").Top
            .Left = Cells(lThisRow, 1).Left
            .Top = Cells(lThisRow, 1).Top
               
            .ShapeRange.LockAspectRatio = msoFalse
            .ShapeRange.Height = 100#
            .ShapeRange.Width = 100#
            .ShapeRange.Rotation = 0#
        End With

        lThisRow = lThisRow + 1
       
    Loop
       
    Range("A10").Select
    Application.ScreenUpdating = True
       
    Exit Sub
       
ErrNoPhoto:
    MsgBox "Unable to Find Photo" 'Shows message box if picture not found
    Exit Sub
    Range("A1").Select
ErrorTryGif:
        On Error GoTo ErrorTryJpeg
        ActiveSheet.Pictures.Insert("//location/" & picname & ".gif").Select 'Path to where pictures are stored
        Resume Next
ErrorTryJpeg:
        On Error GoTo ErrorHandler
        ActiveSheet.Pictures.Insert("//location/" & picname & ".jpeg").Select 'Path to where pictures are stored
        Resume Next
ErrorHandler:
           Resume Next
End Sub

 

[cerfani]

Re: Help with Error Handling in VBA

i went through your code and it seems like it flows correctly

So describe how it is broken. When you step through your code when it is processing a gif file... what exactly happens... be detailed

 

[J3nny]

Re: Help with Error Handling in VBA

Hi!

Well I've managed to get it working, but by doing what everything advises you should never do and just sticking 'On Error Resume Next' at the top of the code. :/ Now this attempts all three of the image uploads (.jpg, .gif and .jpeg), like I wanted it to. I think to do it properly what i really need is a nested error statement, but that didn't work the way I'd written it and i can't see from Google and my very limited knowledge of VBA how the alternatives are supposed to work.

Sub GetPicture3()
 Dim picname As String
    
 Dim pasteAt As Integer
 Dim lThisRow As Long

 Dim ErrNumber As Integer
 
 On Error Resume Next
 
    lThisRow = 5
    
    Do While (Cells(lThisRow, 2) <> "")
       
    ErrNumber = 0

        'Range("A6").Select 'This is where picture will be inserted
        'pasteAt = Cells(lThisRow, 3)
         Cells(lThisRow, 1).Select 'This is where picture will be inserted
           
        'Dim picname As String
        'picname = Range("B6") 'This is the picture name
         picname = Cells(lThisRow, 17) 'This is the picture name"
[COLOR=#ff0000]        'Three attempts to upload the image:
        ActiveSheet.Pictures.Insert("//location/" & picname & ".jpg").Select 'Path to where pictures are stored
        ActiveSheet.Pictures.Insert("//location/" & picname & ".jpeg").Select
        ActiveSheet.Pictures.Insert("//location/" & picname & ".gif").Select[/COLOR]
        ' This resizes the picture
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''
        With Selection
            '.Left = Range("A6").Left
            '.Top = Range("A6").Top
            .Left = Cells(lThisRow, 1).Left
            .Top = Cells(lThisRow, 1).Top
               
            .ShapeRange.LockAspectRatio = msoFalse
            .ShapeRange.Height = 100#
            .ShapeRange.Width = 100#
            .ShapeRange.Rotation = 0#
        End With
NextStep:
        lThisRow = lThisRow + 1
       
    Loop
       
    Range("A10").Select
    Application.ScreenUpdating = True
       
    Exit Sub

End Sub

 

[JazzSP8]

Re: Help with Error Handling in VBA

Out of interest... If there are only pictures in your folder Try changing...

ActiveSheet.Pictures.Insert("//location/" & picName & ".jpg").Select 'Path to where pictures are stored

To

ActiveSheet.Pictures.Insert("//location/" & picName & ".*").Select 'Path to where pictures are stored

The "*" acts as a wildcard so it doesn't matter what the extension part of the file name is.

 

[J3nny]

Re: Help with Error Handling in VBA

Hi JazzSP8,

Thank you very much for that that solves the problem perfectly - I was trying to do something much more complicated, and all it took was that wee star. Thank you so so much!

Jenny

 

[JazzSP8]

Re: Help with Error Handling in VBA

Hi Jenny

No problem at all - I kept staring at it thinking I'd over simplified the answer or missed something in the question so I'm glad it worked for you

 

[nikkibeak27]

Re: Help with Error Handling in VBA

Hi I'm hoping you can help me. I know 0 about VB.
My problem as follow;

Column B contains image name (starts at row 13) (this is actually used to join various tables as it's the common denominator)
Column C has file location including (this includes the image name i.e. "C:\my***\abc.jpg")
I need the image to inserted in column J
If the file is not found I want a generic picture to be inserted file location "C:\my***\not available.jpg"
If there's no error and the image is found and inserted, the image must be resized to width 7cm
Once that's done the process must be repeated for every row.

Also need to be able to refresh / change the data then update the picture accordingly.

Hope someone will be able to help.

Thank you!!!