Results 1 to 13 of 13
  1. #1
    davideitaly is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    44

    Picture from form to word

    Hi all. I have a form (with a table behind) where I put students data, included the picture of each one, uploaded as "attachment".
    I have also a button that allow me to populate a word model file (.dotx) with those data through bookmarks. The only thing I can't manage to export is every related picture. This is the code I use to populate the Word model:

    Code:
    Private Sub schedallievo_Click()
    Dim Wrd As Word.Application, Doc As Word.Document
    Dim Rst As DAO.Recordset
    Dim Modello As String, NomeFile As String, i As Integer
    Dim Record As String, strSQL As String
    Dim Email1 As Hyperlink
    Dim TotRiga As Integer, TOTALE As Integer
    Dim ReplSel As Boolean
    
        Modello = CurrentDb.Name
        Modello = Left(Modello, Len(Modello) - Len(Dir(Modello))) & "schedallievo.dotx"
    
        On Error Resume Next
        Set Wrd = GetObject(, "Word.Application")
        If Err.Number = 429 Then
            Set Wrd = CreateObject("Word.Application")
        End If
        
        On Error GoTo 0
        
        Wrd.Visible = True
        Wrd.Activate
        ReplSel = Wrd.Options.ReplaceSelection
        Wrd.Options.ReplaceSelection = True
        Set Doc = Wrd.Documents.Add(Modello)
        Doc.Activate
        
        Doc.Bookmarks("Nome").Select
        Wrd.Selection.Font.AllCaps = True
        Wrd.Selection.TypeText Me.NOME
    
        Doc.Bookmarks("Cognome").Select
        Wrd.Selection.Font.AllCaps = True
        Wrd.Selection.TypeText Me.COGNOME
       
        Doc.Bookmarks("datanascita").Select
        Wrd.Selection.Font.AllCaps = True
        Wrd.Selection.TypeText Me.Data_di_nascita
       
        Doc.Bookmarks("residenza").Select
        Wrd.Selection.Font.AllCaps = True
        Wrd.Selection.TypeText Me.INDIRIZZO
        
        Doc.Bookmarks("tel").Select
        Wrd.Selection.Font.AllCaps = True
        Wrd.Selection.TypeText Me.Telefono1
    
        Doc.Bookmarks("email").Select
        Wrd.Selection.Font.AllCaps = True
        Wrd.Selection.TypeText Me.Email1
        
        Doc.Bookmarks("compartimento").Select
        Wrd.Selection.Font.AllCaps = True
        Wrd.Selection.TypeText Me.Compartimento
    
        Doc.Bookmarks("matricola").Select
        Wrd.Selection.Font.AllCaps = True
        Wrd.Selection.TypeText Me.Matricola
    
        Wrd.Application.WordBasic.MsgBox "Esportazione terminata", "Esportazione dati da Access"
    
    End sub
    The only missing thing is some instruction to export the picture too. I found that around in the net:



    Code:
        Selection.Goto What:=wdGoToBookmark, Name:="mypicture"
        Selection.Find.ClearFormatting
        With Selection.Find
            .Text = ""
            .Replacement.Text = ""
            .Forward = True
            .Wrap = wdFindContinue
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False
        End With
        Selection.InlineShapes.AddPicture FileName:= _
        "mypicturepath", _
        LinkToFile:=False, SaveWithDocument:=True
    That would work great if I could refer to a single file-picture stored somewhere. But in my case the picture is uploaded in the original table as attachment and shown in the form. I can't manage to find a modification that could work changing the "mypicturepath" with a Me.

    Another soultion would be to collect all the pictures in a folder and refer to them, but how can I tell Access to link a single picture to an Id, and then export that related one to word?

    Thanks in advance for advising.

    Davide

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I was able to copy the image from a bound ole frame to the word box:

    Code:
    Public Sub Export2Word()
    dim Wrd As Word.Application
    
    Set Wrd = CreateObject("word.application")
    
      'copy the image on form to clipboard
    Forms!frmName1!img1.Action = acOLECopy
    
    
    vFile = "C:\Users\user1\Documents\doc2.docx"
    
    With mWrd
        .Documents.Open vFile
        .ActiveDocument.Shapes("Text Box 2").Select
    
            'paste image
        .Selection.Paste
    End With
    
    Set Wrd = Nothing
    End Sub

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Davide,

    Storing picture in the table is not recommended as it will bloat your back-end and soon you might reach the 2 GB limit. If you do some searching in the forums and internet you'll soon find lots of info regarding this. The usual solution is to store the pictures in a folder then store the path to the picture in a simple short text field in the table. If done right you don't even need to store the path to each picture, just store the path to the folder in a local settings table in the front-end and use a naming convention to name your files (in your case StudentNumber.Jpg or StudentID.jpg - don't use student names as you might run into duplicates and or problems with apostrophes).

    Also, the method you use to create the Word document is kinda' clunky, I think you should look at replacing it with mail-merge which is much more robust.

    As for your initial question just look at using SaveToFile method to extract the picture to a temporary file then use the code you have to add it to Word.
    https://msdn.microsoft.com/en-us/lib.../ff191852.aspx

    Cheers,
    Vlad

  4. #4
    davideitaly is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    44
    Quote Originally Posted by ranman256 View Post
    I was able to copy the image from a bound ole frame to the word box:

    Code:
    Public Sub Export2Word()
    dim Wrd As Word.Application
    
    Set Wrd = CreateObject("word.application")
    
      'copy the image on form to clipboard
    Forms!frmName1!img1.Action = acOLECopy
    
    
    vFile = "C:\Users\user1\Documents\doc2.docx"
    
    With mWrd
        .Documents.Open vFile
        .ActiveDocument.Shapes("Text Box 2").Select
    
            'paste image
        .Selection.Paste
    End With
    
    Set Wrd = Nothing
    End Sub
    Thank you. I will try the code, but before I have to set things as Vlad suggested me, not to overweigh the database...

  5. #5
    davideitaly is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    44
    Quote Originally Posted by Gicu View Post
    Davide,

    Storing picture in the table is not recommended as it will bloat your back-end and soon you might reach the 2 GB limit. If you do some searching in the forums and internet you'll soon find lots of info regarding this. The usual solution is to store the pictures in a folder then store the path to the picture in a simple short text field in the table. If done right you don't even need to store the path to each picture, just store the path to the folder in a local settings table in the front-end and use a naming convention to name your files (in your case StudentNumber.Jpg or StudentID.jpg - don't use student names as you might run into duplicates and or problems with apostrophes).

    Also, the method you use to create the Word document is kinda' clunky, I think you should look at replacing it with mail-merge which is much more robust.

    As for your initial question just look at using SaveToFile method to extract the picture to a temporary file then use the code you have to add it to Word.
    https://msdn.microsoft.com/en-us/lib.../ff191852.aspx

    Cheers,
    Vlad
    Hi Vlad.
    I am in the first part of your suggestions. The problem in storing just the folder path in the table is that the picture won't show in the form. If I use the path, in the frame dedicated to the picture it shows... the path...

    Click image for larger version. 

Name:	sample.jpg 
Views:	21 
Size:	165.8 KB 
ID:	34128

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Davide,

    There is no point to save the (same) path to the folder in the main table with the rest of the student records. And you need to add code to the OnCurrent event of the form to refresh the picture.

    Here is how I use to do it, I am sure there are many other suggestions:

    Code:
    Private Sub Form_Current()
    Call vcShowPicture 'refresh the picture for the current student
    '.......any other code you might have
    End Sub
    
    Private Sub vcShowPicture()
    '= = = = = = = = = = = = = =
    On Error Resume Next
    Dim PicturePath As String
    
    
    On Error Resume Next
    PicturePath = Forms![Main Switchboard Form]!PicturesFolder & "\" & Me.Student_Number & ".jpg"
    
    
    Application.Echo False
    Me.MiniImageStudent.Visible = False
    
    
    On Error GoTo NoPicture
    
    
    Me.MiniImageStudent.Picture = PicturePath
    Me.MiniImageStudent.Visible = True
    
    
    Application.Echo True
    Exit Sub
    
    
    NoPicture:
    On Error Resume Next
    Me.MiniImageStudent.Visible = False
    Application.Echo True
    
    
    End Sub
    As you can see the full path is made out of two components: the green one is the path the folder containing ALL the pictures; that path is stored in a local front-end table that contains various settings (user name, path to picture folder, path to Word mail-merge templates, path to Excel export folder, etc.) and that table is the record source of a "main switchboard" that stays open all the time (also acts as login form). You don't need to follow this setup, simply create a local table, add the folder path and replace the green part with a dlookup: dlookup("[PictureFolder]","[tblSettings]"). The red part is the student number or studentid from the form concatenated with the file extension you use for the pictures: Me.Matricola & ",jpg". Now you put the two together and you have the full path.

    If the pictures are stored with various names in various folders you need to store the full path in the student table and modify the sub I posted to take it from there instead of combining the pieces.

    Cheers,
    Vlad

  7. #7
    davideitaly is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    44
    Quote Originally Posted by Gicu View Post
    Davide,

    There is no point to save the (same) path to the folder in the main table with the rest of the student records. And you need to add code to the OnCurrent event of the form to refresh the picture.

    Here is how I use to do it, I am sure there are many other suggestions:

    Code:
    Private Sub Form_Current()
    Call vcShowPicture 'refresh the picture for the current student
    '.......any other code you might have
    End Sub
    
    Private Sub vcShowPicture()
    '= = = = = = = = = = = = = =
    On Error Resume Next
    Dim PicturePath As String
    
    
    On Error Resume Next
    PicturePath = Forms![Main Switchboard Form]!PicturesFolder & "\" & Me.Student_Number & ".jpg"
    
    
    Application.Echo False
    Me.MiniImageStudent.Visible = False
    
    
    On Error GoTo NoPicture
    
    
    Me.MiniImageStudent.Picture = PicturePath
    Me.MiniImageStudent.Visible = True
    
    
    Application.Echo True
    Exit Sub
    
    
    NoPicture:
    On Error Resume Next
    Me.MiniImageStudent.Visible = False
    Application.Echo True
    
    
    End Sub
    As you can see the full path is made out of two components: the green one is the path the folder containing ALL the pictures; that path is stored in a local front-end table that contains various settings (user name, path to picture folder, path to Word mail-merge templates, path to Excel export folder, etc.) and that table is the record source of a "main switchboard" that stays open all the time (also acts as login form). You don't need to follow this setup, simply create a local table, add the folder path and replace the green part with a dlookup: dlookup("[PictureFolder]","[tblSettings]"). The red part is the student number or studentid from the form concatenated with the file extension you use for the pictures: Me.Matricola & ",jpg". Now you put the two together and you have the full path.

    If the pictures are stored with various names in various folders you need to store the full path in the student table and modify the sub I posted to take it from there instead of combining the pieces.

    Cheers,
    Vlad
    Thanks Vlad.
    I am trying to apply your suggestion, but encountering some issue. I created the dedicated table with picture folder path, and I connected to the form source one in a one-to-one relation, based on the main ID ("ID_imbarchi_anagrafica"):
    Click image for larger version. 

Name:	sample.jpg 
Views:	13 
Size:	75.6 KB 
ID:	34147
    As you may see, the second table has just one field called "Percorso" (path). In the first table, the field "Foto" is a short text with the name of the related jpg file (example "smithj.jpg").
    If I got all well, your code should recall the picture related to the student ID and make it visible in the form, while "OnCurrent" event occurs.
    At the moment it shows only a blank field while opening the form. The string of reference for the path is the following, in my case:

    Code:
    PicturePath = DLookup("[Z:\02_Condivisione\Database di gestione FAIMM\fotoallievi]", "[tblSettings]") & "\" & Me.ID_imbarchi_anagrafica & ".jpg"
    What I don't understand is what is "MiniImageStudent". I don't have any field named as such.
    I tried to change it with "Foto" field, but it always give me an error as "Me.Foto.Picture" doesn't exist.
    The same changing it into "Picture".
    I can't find where the mistake is...

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Davide,

    Are all the pictures stored in the Z:\02_Condivisione\Database di gestione FAIMM\fotoallievi folder with file names like smith.jpg, davide.jpg, etc.? Are the file names stored in the Photo field in the Imbarchia_anagraphica table with each record?

    If yes you need to to the following:
    • Edit the new table to remove the first two fields and leave just the path field (you can make it the primary key or you could add an autonumber for that); that table should only have one record and that should be the folder path ending in backslash("Z:\02_Condivisione\Database di gestione FAIMM\fotoallievi")
    • Your Photo field should contain the file names (smith.jpg)
    • You now put the two together: PicturePath = DLookup("[Percorso]", "[Immagini]") & Me.Photo 'this must be the name of the control on your form that has the Photo field as its control source (you can probably hide it)
    • Modify the vcShowPicture() to replace the MiniImageStudent with the name of your Image control where you want your picture to be displayed (I believe you have it in the upper left, kinda' square); if that is not an image control but a text box (maybe linked to Photo field) you need to replace it with one as a text box cannot display pictures.
    • Call the edited vcSHowPicture from the OnCurrent event of the form as previously shown

    Cheers,
    Vlad

  9. #9
    davideitaly is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    44
    Quote Originally Posted by Gicu View Post
    Hi Davide,

    Are all the pictures stored in the Z:\02_Condivisione\Database di gestione FAIMM\fotoallievi folder with file names like smith.jpg, davide.jpg, etc.? Are the file names stored in the Photo field in the Imbarchia_anagraphica table with each record?

    If yes you need to to the following:
    • Edit the new table to remove the first two fields and leave just the path field (you can make it the primary key or you could add an autonumber for that); that table should only have one record and that should be the folder path ending in backslash("Z:\02_Condivisione\Database di gestione FAIMM\fotoallievi")
    • Your Photo field should contain the file names (smith.jpg)
    • You now put the two together: PicturePath = DLookup("[Percorso]", "[Immagini]") & Me.Photo 'this must be the name of the control on your form that has the Photo field as its control source (you can probably hide it)
    • Modify the vcShowPicture() to replace the MiniImageStudent with the name of your Image control where you want your picture to be displayed (I believe you have it in the upper left, kinda' square); if that is not an image control but a text box (maybe linked to Photo field) you need to replace it with one as a text box cannot display pictures.
    • Call the edited vcSHowPicture from the OnCurrent event of the form as previously shown

    Cheers,
    Vlad
    Thanks indeed Vlad, it works perfectly. Little complicated, but I get it...
    Now next step: transfer every student related picture to the word file...
    I'll work on it following the previous suggestions!
    Cheers!

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Davide,
    Glad to hear you got it working. For Word your initial code should work, just replace myfilepath with your own (if you do it one student at the time you could probably do it like this:

    Code:
    Selection.Goto What:=wdGoToBookmark, Name:="mypicture"
        Selection.Find.ClearFormatting
        With Selection.Find
            .Text = ""
            .Replacement.Text = ""
            .Forward = True
            .Wrap = wdFindContinue
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False
        End With
        Selection.InlineShapes.AddPicture FileName:= Me.YourImageControl.Picture, _
        LinkToFile:=False, SaveWithDocument:=True

  11. #11
    davideitaly is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    44
    Quote Originally Posted by Gicu View Post
    Davide,
    Glad to hear you got it working. For Word your initial code should work, just replace myfilepath with your own (if you do it one student at the time you could probably do it like this:

    Code:
    Selection.Goto What:=wdGoToBookmark, Name:="mypicture"
        Selection.Find.ClearFormatting
        With Selection.Find
            .Text = ""
            .Replacement.Text = ""
            .Forward = True
            .Wrap = wdFindContinue
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False
        End With
        Selection.InlineShapes.AddPicture FileName:= Me.YourImageControl.Picture, _
        LinkToFile:=False, SaveWithDocument:=True
    Holy crap, you're a genius, Vlad.
    It's perfect but I still have one small need. I tried to work on the code but didn't get rid of it.
    I'd need the picture floating on the .docx (non "in line") and resizing it to 300 x 375.
    How could I do that?
    Cheers!

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Have a look at the third method in this link:https://www.datanumen.com/blogs/3-wa...word-document/
    Maybe try:

    Code:
    '.......
    
    With Selection.InlineShapes
    
    .Height=375
    .Width=300
    .ConvertToShape
    
    
    end with

  13. #13
    davideitaly is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    44
    Quote Originally Posted by Gicu View Post
    Have a look at the third method in this link:https://www.datanumen.com/blogs/3-wa...word-document/
    Maybe try:

    Code:
    '.......
    
    With Selection.InlineShapes
    
    .Height=375
    .Width=300
    .ConvertToShape
    
    
    end with
    Hi! I got it working, editing the code this way:

    Code:
    Selection.Goto What:=wdGoToBookmark, Name:="foto"
        Selection.Find.ClearFormatting
        With Selection.Find
            .Text = ""
            .Replacement.Text = ""
            .Forward = True
            .Wrap = wdFindContinue
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False
        End With
        Selection.InlineShapes.AddPicture FileName:=Me.fotoallievo.Picture, _
        LinkToFile:=False, SaveWithDocument:=True
        With ActiveDocument.InlineShapes(1)
        .ScaleHeight = 30
        .ScaleWidth = 30
        End With
    The .Scale parameters allows me to reduce the picture size, which was bigger than needed. I didn't use .ConvertToShape as it made the picture floating on the word page, forcing me to make the code more complicated to have it in the position I wanted. So I keep it "in line" forcing the related word bookmark into a table.
    The result is perfect.
    Thank you all for the support, especially Vlad.
    Cheers!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 12-17-2017, 12:33 PM
  2. Replies: 5
    Last Post: 01-15-2017, 09:53 AM
  3. Take a picture from a form with usb camera **need help
    By lwilson2k1 in forum Programming
    Replies: 4
    Last Post: 11-15-2013, 12:37 PM
  4. Export Microsoft Query with Picture Fields to a Word Document
    By KSReynolds in forum Import/Export Data
    Replies: 2
    Last Post: 10-15-2013, 02:36 PM
  5. Displaying a picture on a form
    By kydbmaster in forum Forms
    Replies: 4
    Last Post: 04-04-2009, 07:54 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums