Import picture from Word 2007 image content control to access attachment field

[gasmanpopey]

Hi all,

As the thread title suggests, i'm struggling Importing a picture from a Word 2007 image content control to access 'attachment' field.

I've created a form in word with text fields and checkboxes (using legacy controls) and a single picture content control. I've created a routine to open a .docx file and import all the fields into the database as a new record. Text fields are copied straight across, checkboxes are concatenated into a string and stored in single fields.

I want the picture to be stored as an attachment rather than meta, to keep the database size sensible.

i've cut the code down a bit for legibility, but in general my approach has been:


Private Sub ImportPDS_Click()
Dim appword As Word.Application
Dim doc As Word.Document
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strDocName As String
Dim blnQuitWord As Boolean
Dim fd As FileDialog
Dim vrtSelectedItem As Variant
Dim filepath As String
Dim frm As Form, pb As Control, sta As Control
Dim objcc As ContentControl

On Error GoTo ErrorHandling

Set fd = Application.FileDialog(msoFileDialogFilePicker) 'launch the file open dialog and set path / filename
With fd
.AllowMultiSelect = False 'one file at a time
.Filters.Clear
.Filters.Add "PDS Forms", "*.docx" 'only docx files
.Title = "Select PDS to Import"
.InitialView = msoFileDialogViewDetails
If .Show = -1 Then
For Each vrtSelectedItem In .SelectedItems
filepath = CStr(vrtSelectedItem) 'convert to string and store path & filename in this variable
Next
Else
End
End If
End With

'insert data validation code here!!!!

Set appword = GetObject(, "Word.Application")
Set doc = appword.Documents.Open(filepath)

'DAO section for database access

Set db = CurrentDb()
Set rs = db.OpenRecordset("Projects", dbOpenTable)

DoCmd.OpenForm ("prog")
Set frm = Forms!prog
Set pb = frm!Progress

pb = 0
frm!LblStatus.Caption = "Opening File"

With rs 'Import data from word file

frm!LblStatus.Caption = "Adding New Record"
.AddNew 'add new record
pb = 0

!ProjectNumber = doc.FormFields("ProjectNumber").Result
pb = 5

frm!LblStatus.Caption = "Importing Client Name"
!Client = doc.FormFields("Client").Result
pb = 10

frm!LblStatus.Caption = "Importing Services"
svc = "" 'routine to create the services string
If doc.FormFields("SP_OE").Result = True Then svc = svc & "Owners Engineer, "
If doc.FormFields("SP_LE").Result = True Then svc = svc & "Lenders Engineer, "
If doc.FormFields("SP_DD").Result = True Then svc = svc & "Due Diligence, "
If doc.FormFields("SP_ED").Result = True Then svc = svc & "Engineering and Design, "
If doc.FormFields("SP_PM").Result = True Then svc = svc & "Project Management, "
If doc.FormFields("SP_CM").Result = True Then svc = svc & "Construction Management, "
If doc.FormFields("SP_RS").Result = True Then svc = svc & "Research, "
If doc.FormFields("SP_CT").Result = True Then svc = svc & "Consultancy, "
!Services = svc
pb = 35

frm!LblStatus.Caption = "Importing Photo" 'last bit (to fix).....

Set objcc = doc.SelectContentControlsByTag("Photo")

!PrjPhoto = objcc.Range.InlineShapes

pb = 95

End With
frm!LblStatus.Caption = "Saving Entry"
pb = 100
rs.Update
rs.Close
Set rs = Nothing
Set db = Nothing

doc.Close
If blnQuitWord Then appword.Quit
cnn.Close
MsgBox "Contract Imported!"

Cleanup:
Set rst = Nothing
Set cnn = Nothing
Set doc = Nothing
Set appword = Nothing
Exit Sub

ErrorHandling:
Select Case Err
Case -2147022986, 429
Set appword = CreateObject("Word.Application")
blnQuitWord = True
Resume Next
Case 5121, 5174
MsgBox "You must select a valid Word document. " _
& "No data imported.", vbOKOnly, _
"Document Not Found"
'Case 5941
'MsgBox "The document you selected does not " _
'& "contain the required form fields. " _
'& "No data imported.", vbOKOnly, _
'"Fields Not Found"
Case Else
MsgBox Err & ": " & Err.Description
End Select
GoTo Cleanup

End Sub



Now please bear with me - this isn't finished and i've only de-bugged as far as the image import section. The line:

!PrjPhoto = objcc.Range.InlineShapes

returns a type mismatch error, which I assume is because the image stored in the word document is not in a format suitable for import to the database as an attachment.

Can anybody give me any pointers on how to proceed? I've been working nonstop on this problem for 3 days now and i'm flat out of ideas.

Thanks in advance

James