what have I done now... (VBA to insert images)

[aelenhart]

through my searching of this and other boards, I came up with the following code that would pick up the file name in E4 and insert the image in E5 and then loop through the worksheet. It works perfectly the first time, but then it seems to get stuck and repeat itself over and over with out end. obviously I am missing something.

please help!


here is the code:

Sub InsertImages()
Dim i As Integer
Dim sFilename As String
Dim bcontinue As Boolean
Dim spath As String

spath = "http://cmspro/stellent/idcplg/webdav/Contribution Folders/Exhibitions_Public/Fast Forward/FFImages/"
bcontinue = True
While bcontinue
sFilename = Worksheets(2).Cells(4, 5).Value
If sFilename = "" Then
bcontinue = False
Else
Cells(4, 6).Select
ActiveSheet.Pictures.Insert(spath + sFilename).Select
Selection.ShapeRange.LockAspectRatio = msoTrue
Selection.ShapeRange.Height = 85
i = i + 1
End If
Wend
End Sub

 

[Norie]

You never change the row or column here, so it will always pick up E4.

sFilename = Worksheets(2).Cells(4, 5).Value

 

[aelenhart]

so how do I make go through all the rows?

 

[Norie]

Maybe use the i variable?

You don't seem to be doing anything else with it.

Sub InsertImages()
Dim i As Integer
Dim sFilename As String
Dim bcontinue As Boolean
Dim spath As String

    spath = "http://cmspro/stellent/idcplg/webdav/Contribution Folders/Exhibitions_Public/Fast Forward/FFImages/"
    bcontinue = True
    i = 4
    While bcontinue
        sFilename = Worksheets(2).Cells(i, 5).Value
        If sFilename = "" Then
            bcontinue = False
        Else
            Cells(i, 6).Select
            ActiveSheet.Pictures.Insert(spath + sFilename).Select
            Selection.ShapeRange.LockAspectRatio = msoTrue
            Selection.ShapeRange.Height = 85
            i = i + 1
        
        End If
    Wend
    
End Sub

 

[aelenhart]

obviously that was the original idea, but when I tried I was given the 1004 error. on pressing "debug" that line comes back highlighted.
the only way I got the error to go away was by changing it to a specific cell.
I thought perhaps it was because there is nothing in column e in the first three lines of the worksheet. is is possible for me to define i as 4 to begin with and run from there?

I don't mean to seem dense, my brain is a little fried.

 

[aelenhart]

wow. you just said that exact thing. my brain is fried. or perhaps freed.

regardless, that works perfectly until I reach the a row that does not contain an entry for the file name. ideally I would like to skip that row and continue through the worksheet until the end.
I'm sure this has something to do with the if/then part of the code.

this is what I get for trying to cheat my way through and copy someone else's code, isn't it?