VBA Insert images

[AkCKq]

Hi All,

Been looking around the site, tried a few things and can't find a way to get this working
So time to ask for some help / establish if what I'm trying to do is even possible


So, I am looking for a VBA Macro that will insert the latest image from a camera directly into excel - will be taking a lot of images and just a way of speeding the process up

Basically hook a camera up
Take a picture,
Run the macro and get the latest photo straight into the excel file


For the moment id just use the standard my docs / image folder to test that everything works - I can amend to suit the camera folder later on and attach the macro to a button to get it running

Any help that can be provided would be greatly appreciated!

 

[AkCKq]

Ended up getting this far with the code




Sub GetMostRecentFile()

Dim FileSys As FileSystemObject
Dim objFile As File
Dim myFolder
Dim strFilename As String
Dim dteFile As Date



'set path for files - change for your folder
Const myDir As String = "C:\Desktop\Test"

'set up filesys objects
Set FileSys = New FileSystemObject
Set myFolder = FileSys.GetFolder(myDir)


'loop through each file and get date last modified. If largest date 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

With xlApp.ActiveSheet.Pictures.Insert("C:\Desktop\Test" + "\objFile")
With .ShapeRange
.LockAspectRatio = msoTrue
.Width = 75
.Height = 100
End With
.Left = xlApp.ActiveSheet.Cells(i, 20).Left
.Top = xlApp.ActiveSheet.Cells(i, 20).Top
.Placement = 1
.PrintObject = True
End With

Set FileSys = Nothing
Set myFolder = Nothing

End Sub





Keep getting an
error "424" Object required
on line
With xlApp.ActiveSheet.Pictures.Insert("C:\Desktop\Test" + "\objFile")
(I have removed part of the file location so its not that its not looking in the correct place)


The code before this point is identifying the file - it just doesn't want to identify it in the second half to then insert it

Again any help greatly appreciated!

 

[Stiuart_W]

This line With xlApp.ActiveSheet.Pictures.Insert("C:\Desktop\Test" + "\objFile")

Should be With xlApp.ActiveSheet.Pictures.Insert("C:\Desktop\Test" & objFile)

 

[AkCKq]

Thanks Stiuart - Now encountering
"run time error 91 - object variable or With block variable not set"
on the same line