heidekabouter
Nieuwe gebruiker
- Lid geworden
- 19 aug 2010
- Berichten
- 3
Ik heb een vba script waarbij ik en afbeelding van mijn pc selecteer en die op 2 tabbladen van een excel document plak. Het moet geen link zijn naar de locatie op mijn pc, maar embed in het document.
Als ik het uitvoer krijg ik een error op ws.Range("B3").PasteSpecial maar als ik foutopsporing doe en het script verder laat lopen, komt de error niet terug.
het script:
Sub AddPictureToCellB3()
Dim imagePath As Variant
Dim pictureTop As Double
Dim pictureLeft As Double
Dim ws As Worksheet
Dim pic As Picture
' Open the file dialog to select an image
With Application.fileDialog(msoFileDialogFilePicker)
.Title = "Select an Image"
.Filters.Clear
.Filters.Add "Images", "*.jpg; *.jpeg; *.png; *.gif"
.AllowMultiSelect = False
If .Show = -1 Then
' Get the selected image path
imagePath = .SelectedItems(1)
' Set the top and left positions of the picture relative to cell B3
pictureTop = Range("B3").Top
pictureLeft = Range("B3").Left
' Add the picture to the active worksheet
Set ws = ActiveSheet
Set pic = ws.Pictures.Insert(imagePath)
' Resize and position the picture
With pic
.ShapeRange.LockAspectRatio = msoTrue
.ShapeRange.Height = Range("B3").Height
.ShapeRange.Width = Range("B3").Width
.Top = pictureTop
.Left = pictureLeft
End With
' Save the picture as an embedded image in the active worksheet
pic.Select
Selection.Copy
ws.Range("B3").PasteSpecial
' Clean up
Application.CutCopyMode = False
pic.Delete
' Add the picture to the "Planning" worksheet
Sheets("Planning").Activate
Set ws = ActiveSheet
Set pic = ws.Pictures.Insert(imagePath)
' Resize and position the picture on the "Planning" worksheet
With pic
.ShapeRange.LockAspectRatio = msoTrue
.ShapeRange.Height = Range("B3").Height
.ShapeRange.Width = Range("B3").Width
.Top = pictureTop
.Left = pictureLeft
End With
' Save the picture as an embedded image on the "Planning" worksheet
pic.Select
Selection.Copy
ws.Range("B3").PasteSpecial
' Clean up
Application.CutCopyMode = False
pic.Delete
End If
End With
Sheets("Inventarisatie").Activate
End Sub
De foutmelding:
Fout 1004 tijdens uitvoeren
Methode PasteSpecial van klasse range is mislukt
Iemand een idee hoe dit op te lossen?
Als ik het uitvoer krijg ik een error op ws.Range("B3").PasteSpecial maar als ik foutopsporing doe en het script verder laat lopen, komt de error niet terug.
het script:
Sub AddPictureToCellB3()
Dim imagePath As Variant
Dim pictureTop As Double
Dim pictureLeft As Double
Dim ws As Worksheet
Dim pic As Picture
' Open the file dialog to select an image
With Application.fileDialog(msoFileDialogFilePicker)
.Title = "Select an Image"
.Filters.Clear
.Filters.Add "Images", "*.jpg; *.jpeg; *.png; *.gif"
.AllowMultiSelect = False
If .Show = -1 Then
' Get the selected image path
imagePath = .SelectedItems(1)
' Set the top and left positions of the picture relative to cell B3
pictureTop = Range("B3").Top
pictureLeft = Range("B3").Left
' Add the picture to the active worksheet
Set ws = ActiveSheet
Set pic = ws.Pictures.Insert(imagePath)
' Resize and position the picture
With pic
.ShapeRange.LockAspectRatio = msoTrue
.ShapeRange.Height = Range("B3").Height
.ShapeRange.Width = Range("B3").Width
.Top = pictureTop
.Left = pictureLeft
End With
' Save the picture as an embedded image in the active worksheet
pic.Select
Selection.Copy
ws.Range("B3").PasteSpecial
' Clean up
Application.CutCopyMode = False
pic.Delete
' Add the picture to the "Planning" worksheet
Sheets("Planning").Activate
Set ws = ActiveSheet
Set pic = ws.Pictures.Insert(imagePath)
' Resize and position the picture on the "Planning" worksheet
With pic
.ShapeRange.LockAspectRatio = msoTrue
.ShapeRange.Height = Range("B3").Height
.ShapeRange.Width = Range("B3").Width
.Top = pictureTop
.Left = pictureLeft
End With
' Save the picture as an embedded image on the "Planning" worksheet
pic.Select
Selection.Copy
ws.Range("B3").PasteSpecial
' Clean up
Application.CutCopyMode = False
pic.Delete
End If
End With
Sheets("Inventarisatie").Activate
End Sub
De foutmelding:
Fout 1004 tijdens uitvoeren
Methode PasteSpecial van klasse range is mislukt
Iemand een idee hoe dit op te lossen?