inserting pic/image from excel file to access table using vba

[Ashank4vba]

Hello,

I am trying to load from pictures/images from an excel file into an access table called 'inspection_temp' using the function below. I have 2 fields in that table - Pic_Num(text) and Pic (OLE Object). For each pic in the excel sheet, I am trying a add a new record in the table and copy the image into the OLE Object field.

However, when I try to run this code, I get a run-time error 3265 - Item not found in this collection at this line - "pic_rs.Fields(2) = obj_pic"

Please help.

Also, I don't know the property names for accessing the values in excel controls such as option buttons and check boxes. Please help in this regard too. Thank you very much.

Function tempfn9()

Dim obj_XL_App As Excel.Application
Dim obj_XL_Wkbks As Excel.Workbooks
Dim obj_XL_Wkbk As Excel.Workbook
Dim obj_XL_Wksht As Excel.Worksheet
Dim obj_pic

Dim my_db As Database
Dim pic_rs As DAO.Recordset
Dim prp As property 

Set obj_XL_App = CreateObject("Excel.Application")
Set obj_XL_Wkbks = obj_XL_App.Workbooks

Set obj_XL_Wkbk = obj_XL_Wkbks.Open("C:\work\Michiko\Inspection Report Samples\sample1.xls", , True)

'Debug.Print obj_XL_Wkbk.Sheets("Inspection Form").[Option Button 204].Value - returns '-4146'
'Debug.Print obj_XL_Wkbk.Sheets("Inspection Form").[Option Button 204].Checked
Debug.Print obj_XL_Wkbk.Sheets("Inspection Form").TextBox3.text
'For Each prp In obj_XL_Wkbk.Sheets("Inspection Form").[Check Box 1118]
'
'Debug.Print prp.Name
'
'Next

Set my_db = CurrentDb
Set pic_rs = my_db.OpenRecordset("inspection_temp")

For Each obj_pic In obj_XL_Wkbk.Sheets("Insert Property Images 1 to 15").Pictures

If left(obj_pic.Name, 3) = "Pic" Then
    pic_rs.AddNew
    pic_rs.Fields(1) = obj_pic.Name
    pic_rs.Fields(2) = obj_pic
    pic_rs.update
End If

Next


pic_rs.Close
my_db.Close
Set my_db = Nothing


Set obj_XL_Wksht = Nothing
Set obj_XL_Wkbk = Nothing
Set obj_XL_Wkbks = Nothing
Set obj_XL_App = Nothing

End Function

 

[jadams0173]

You may want to try something like:

pic_rs.Fields("TableFieldName")

If you use the actual field name then if you would move the order of the columns then the data will still go tot he proper columns. HTH