PDA

View Full Version : excel template error handling



Adonaioc
09-21-2009, 07:18 AM
Here is my code thus far, it is fine as long as all of the files are there, but if there is a Path that does not exist it swaps the blank in the photo column with the pic in the previous row

example
parta data picture a
partb data picture b
partc data Blank
Partnopic data picture c
parte data Picture e

I am pretty sure that my error handeling is wrong but I cant figure out what I am doing wrong.

Dim Row As Integer
Dim Col As Integer
Dim Path As String
Dim Pic As Picture

On Error Resume Next

'set initial row col location and value
Row = 3
Col = 3
X = ActiveSheet.Cells(Row, Col).Value


'do until cells are blank
Do While X <> vbNullString

'path to file
Path = "C:\Pictures\" & X & ".jpg"

'goto picture cell
Col = Col + 2

'insert the picture
With ActiveSheet.Cells(Row, Col)
Set Pic = .Parent.Pictures.Insert(Path)
Pic.Top = .Top
Pic.Width = .Width
Pic.Height = .Height
Pic.Left = .Left
Pic.Placement = xlMoveAndSize
End With

'increment row and go back to col c
Col = Col - 2
Row = Row + 1

X = ActiveSheet.Cells(Row, Col).Value


Loop

End Sub

Bob Phillips
09-21-2009, 07:24 AM
Not tested, but try this



Dim Row As Integer
Dim Col As Integer
Dim Path As String
Dim Pic As Picture

On Error Resume Next

'set initial row col location and value
Row = 3
Col = 3
X = ActiveSheet.Cells(Row, Col).Value


'do until cells are blank
Do While X <> vbNullString

'path to file
Path = "C:\Pictures\" & X & ".jpg"

'goto picture cell
Col = Col + 2

'insert the picture
With ActiveSheet.Cells(Row, Col)

Set Pic = Nothing
Set Pic = .Parent.Pictures.Insert(Path)
If Not Pic Is Nothing Then

Pic.Top = .Top
Pic.Width = .Width
Pic.Height = .Height
Pic.Left = .Left
Pic.Placement = xlMoveAndSize
End If
End With

'increment row and go back to col c
Col = Col - 2
Row = Row + 1

X = ActiveSheet.Cells(Row, Col).Value

Loop