Import info from Word 2013 form into Access 2013 via VBA

[oddstan]

I’m trying to make VBA script for Access 2013 which will help to process Word 2013 forms from folder, attach these files of Word 2013 form to relevant record entry and attach pdf file from same folder with the same name.

Word form made with simple "text field" (i.e. not ActiveX text field)

Since I’m not a programmer each try leads me to next error. Can anyone help to make it work.

Thanks.

Dim appWord As Word.Application
Dim doc As Word.Document
Dim db As Database
Dim SourceFolderPath As String, DestinationFolderPath As String, FormFile As String
Dim FSO As Object, FileInSourceFolderPath As Object
Dim rst As dao.Recordset
Dim blnQuitWord As Boolean

    SourceFolderPath = CurrentProject.Path & "\DATA\"
    DestinationFolderPath = CurrentProject.Path & "\TEMP\"
    FormFile = Dir(SourceFolderPath & "\*.docx")

    Set appWord = GetObject(, "Word.Application")
    Set doc = appWord.Documents.Open(FormFile)

    CurrentDb.OpenRecordset "FormData"

    With rst
    .AddNew
    !Question1 = doc.FormFields("Q1").Result
    !Question2 = doc.FormFields("Q2").Result
    !Question3 = doc.FormFields("Q3").Result
    .Update
    .Close
    End With
    doc.Close
    If blnQuitWord Then appWord.Quit

    Set FSO = CreateObject("scripting.filesystemobject")
        For Each FileInSourceFolderPath In FSO.getfolder(SourceFolderPath).Files
            FileInSourceFolderPath.Move DestinationFolderPath
        Next FileInSourceFolderPath

    MsgBox "Forms imported!"

I’ve found a sample of «attach script”, however I don’t have a clue how to insert into logic of whole algorithm.

Dim cdb As DAO.Database, rstMain As DAO.Recordset, rstAttach As DAO.Recordset2, _
        fldAttach As DAO.Field2
Set cdb = CurrentDb
Set rstMain = cdb.OpenRecordset("SELECT MyFilePath, MyFileAttach FROM tblTest", dbOpenDynaset)
Do Until rstMain.EOF
    rstMain.Edit
    Set rstAttach = rstMain("MyFileAttach").Value
    rstAttach.AddNew
    Set fldAttach = rstAttach.Fields("FileData")
    fldAttach.LoadFromFile rstMain("MyFilePath").Value
    rstAttach.Update
    rstAttach.Close
    Set rstAttach = Nothing
    rstMain.Update
    rstMain.MoveNext
Loop
rstMain.Close
Set rstMain = Nothing
Set cdb = Nothing

 

[Cronk]

For a start, you haven't created the rst object.

Replace

CurrentDb.OpenRecordset "FormData"

with

set rst = currentdb.openrecordset("select * from FormData where false")

if indeed your table is named FormData

 

[JHB]

..
Since I’m not a programmer each try leads me to next error. Can anyone help to make it work.

Yeah sorry, for me it looks like you've found some code here and there and have adopted without actually knowing what the code does.
So let start with something simple like open Word with the particular document loading which has the 3 fields and insert the values of them into the Access table, then the code can be expanded to exactly meet your requirements.
Database + Word document attached, try it.
Then you've to explain what you want next and why.

 

[oddstan]

Database + Word document attached, try it.

Thanks.
Yes. I've found many threads on different sites. But they are either for different programs (from Word to Excel; or import from Word simple table; or different versions of Access) or provide solution for part of what i'm looking for.
Give me some time to work with sample you've provided.

 

[isladogs]

there are many examples for transferring data between various Office programs including Word and Access on this site
http://www.helenfeddema.com/Code%20Samples.htm

You may well find various things of use to you...

 

[oddstan]

Then you've to explain what you want next and why.

Hello,

I collect forms from employees in company, many of them. and to process them faster I ask to send me pdf signed and .docx to analize data. I would use email forms, but company uses Lotus and I need verification that forms are filled by certain employee (if i'm not mistaken Access don't work that easy with Lotus Notes).
Our IT guys are too lazy (eg make form in DB that uses Active directory authorisation).

After multiple tryes with what I don't understand)), such modification does 1st task - collect data from all files with .docx extension in folder. If something can be done more correctly please give a comment.

Then not to keep files in folder I would like to save them in DB by the proper record.

How to add code that will attach pdf and docx file to the relevant record, assuming that filename of docx and pdf is the same?

I'll play a little with file attachment script, however I need a clue how to incert it to relevant record or start new part of script. what commands can be used and in what place to start new part of script?

Thanks.

Private Sub ImportFormdata_Click()
  Dim appWord As Word.Application
  Dim doc As Word.Document
  Dim SourceFolderPath As String
  Dim FormFile As String
  Dim strName As String
  Dim rst As DAO.Recordset
    
  SourceFolderPath = CurrentProject.Path & "\"
  FormFile = Dir(SourceFolderPath & "*.docx", vbNormal)
  
  Do While FormFile <> ""
  Set appWord = CreateObject("Word.Application")
  strName = SourceFolderPath & FormFile
  Set doc = appWord.Documents.Open(strName)
  appWord.Visible = False
  Set rst = CurrentDb.OpenRecordset("FormData")
  With rst
     .AddNew
     ![Question1] = doc.FormFields("Q1").result
     ![Question2] = doc.FormFields("Q2").result
     ![Question3] = doc.FormFields("Q3").result
     ![File name] = FormFile
     .Update
     .Close
  End With
  doc.Close
  appWord.Quit
  FormFile = Dir
  Loop

MsgBox "Forms imported!"
End Sub

 

[JHB]

I wouldn't attached the data from the Word and Pdf file into the database.
I Would save the path and file name instead.
But I've made both solution in the attached file, then it is up to you which solution you prefer.

 

[oddstan]

I wouldn't attached the data from the Word and Pdf file into the database.
I Would save the path and file name instead.
But I've made both solution in the attached file, then it is up to you which solution you prefer.

Many thanks.
Spent all morning trying to figure out how to add "attachment" part into code.
You wouldn't attach data because of db size limit or possibility that db may corrupt?

 

[JHB]

The database grow, but you can try importing 100 documents and see the size, remember to "Compact and Repair" before you look at the size.