This macro is supposed to add the most recent picture from my camera roll and insert it in the designated cell in Excel when a button is pressed on the worksheet. This macro was working at one point, but my guys ran into an issue with it last week, and I can't figure out why. It is giving me an Application-defined or Object-defined error at the "Shapes.AddPicture" line below.
I am working with Excel 2010. Any help would be appreciated.
I am working with Excel 2010. Any help would be appreciated.
Code:
Private Sub GetMostRecentImageTrend()
'When the trend screen button on the "Pictures" tab is pressed, this macro will insert the most recent image.
Dim FileSys As FileSystemObject
Dim objFile As File
Dim myFolder
Dim strFileName As String
Dim dteFile As Date
Dim myDir As String
Dim pic As Object
'Set path for files:
myDir = Environ("USERPROFILE") & "\Pictures\Camera Roll"
'Set up filesys objects:
Set FileSys = New FileSystemObject
Set myFolder = FileSys.GetFolder(myDir)
'Loop through each file & get date last modified. If largest date (most recent) then store Filename:
dteFile = DateSerial(1900, 1, 1)
For Each objFile In myFolder.Files
If objFile.DateLastModified > dteFile Then
dteFile = objFile.DateLastModified
strFileName = objFile.Name
End If
Next objFile
'Insert the image into the designated cell.
With Application.ActiveWorkbook.Sheets("Pictures").Shapes.AddPicture(myFolder & "\" & strFileName, False, True, 1, _
1, -1, -1)
.Top = ActiveWorkbook.Sheets("Pictures").Range("B9").Top
.Left = ActiveWorkbook.Sheets("Pictures").Range("B9").Left
.Height = ActiveWorkbook.Sheets("Pictures").Range("B9:B42").Height
.Width = ActiveWorkbook.Sheets("Pictures").Range("B9:J9").Width
End With
'Delete the picture from the camaera roll directory after pasting:
Kill (myFolder & "\" & strFileName)
End Sub