Export embedded .txt or .csv file from Word bookmark to Access table field

eric.okeefe · #1 08-14-2017, 05:12 PM

Good evening, all! Up front, any help...at all...would be greatly appreciated. Thank you in advance. Other than the "Reader's Digest Version" piece I provide below, I apologize for the length of this post. I just wanted to make sure whoever decides to help has all the pertinent information up front and I don't want to waste anyone's valuable time by leaving out something important.

READER'S DIGEST VERSION: Is it possible to send an OLEObject .txt/.csv file that is embedded in a Word 2010 document (contained in a legacy bookmark) to a field in an Access 2010 table that is identified as an attachment field, or any other field type that can store and display the OLEObject in its native form within Access?

CAVEAT: I do not fancy myself a VBA expert, or even adept for that matter, but through much research and persistence I have taught myself how to do quite a bit to reach my automation/coding goals...mostly by cobbling together answers related to others' posts; however, I am frequently humbled by the immense complexity of VBA and the incredible brain-trust often found within these forums, and I have searched high-and-low for a solution to this problem with no success. Perhaps there is none. So, if my question or approach seem stupid, it's probably because I am

BACKGROUND: I've been working on a fairly complicated Word 2010 document template designed to capture specific end-user content in many plain-text contentcontrols throughout said document (which is a user Request Form)(.docx). When that completed document (.docx) is sent to the record manager (via email and who has a duplicate of the original template to be able to utilize macros), he/she reviews it for accuracy and completeness and then selects a macro button I've included in a custom QAT that sends all the information contained within the contentcontrols to corresponding fields in an Access 2010 table (.accdb) where the requests are ultimately managed. All of that works, after almost 1 year of trial and error (and spending huge amounts of time in various Word VBA forums and Superman Greg Maxey's site) figuring out the VBA macros (all digitally signed with a verified PKI certificate) needed to accomplish what I wanted done. This is when my nightmare began. Because of limited physical space on the Request Form, I needed a way for the user to embed either a plain-text file or .csv file into the document itself in lieu of entering text in the form's prescribed, but limited, space (assuming they might need more space then what is available). I have figured out the manner in which to embed the .txt or .csv file as an OLEObject into a bookmark titled "bkmAttachmentPoint", and I have figured out how to declare all the contentcontrols for regular text fields in the document, and I have figured out how to pass the text contained within the plain-text contentcontrols to the Access database (using INSERT INTO). What I cannot figure out is how to declare the embedded .txt/.csv files (the OLEObjects) embedded into the bookmark and pass them to the Access database.

As a side note, I am using a bookmark to "house" the OLEObjects, because when I had it as a plain text contentcontrol I was able to embed the txt/csv files but Word complained of not being able to read the content when subsequently opening the document. The bookmark approach seems to avoid that.

One other note, this form and accompanying database will eventually be on a closed network, so I have no concerns with regards to SQL injection attacks, macro malware, or anything else. Just thought I'd throw that out there.

I am including a sampling of the code I currently have that passes all the plain text-based contentcontrol data from the document to the database in an effort to help make any assistance easier. I say "sampling" because the number of fields within the document that must be passed to the database are many and results in over 100 lines of code and I don't want to blow up the Internet. Again, everything currently written to date does exactly as I want it to. I have no error messages to offer regarding passing the OLEObject embedded .txt/.csv files to the database as I don't even know where to begin.

Code:

Sub SubmitRequestForm()
'This sub-routine will transfer deconfliction request data from this form to
'the tblReviewHolding table in the Deconfliction database.

'Declare all relevant variables from the Request Form
Dim cc As ContentControl
Dim rng As Word.Range
Dim cnn As ADODB.Connection
Dim strConnection As String
Dim strSQL As String
Dim strPath As String

Dim strClassForm As String
Dim strReqAgency As String

Dim bytContinue As Byte
Dim lngSuccess As Long

'Identify all the relevant ContentControl data fields on the Request Form
strClassForm = ActiveDocument.SelectContentControlsByTitle("ClassForm").Item(1).Range.Text
strReqAgency = ActiveDocument.SelectContentControlsByTitle("ReqAgency").Item(1).Range.Text

'Confirm new record.
bytContinue = MsgBox("By selecting YES you will submit this deconfliction request" _
& " to the database request queue.  Please be sure you have properly reviewed" _
& " this request prior to submission.  Once submitted, changes to this data can only" _
& " be made by a database administrator.", vbYesNo + vbExclamation, "Submit Deconfliction Request to Database")

Debug.Print bytContinue

'Process input values and send the results to the database in the appropriate fields
If bytContinue = vbYes Then
strSQL = "INSERT INTO tblReviewHolding([Classification], [RequestingAgency]) VALUES(""" _
    & strClassForm & """, """ _
        & strReqAgency & """)"

Debug.Print strSQL

strPath = "{Placeholder for actual location of database}.accdb" 'Location masked for this posting

strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" _
    & "Data Source = " & strPath
        Debug.Print strConnection
    Set cnn = New ADODB.Connection
        cnn.Open strConnection
        cnn.Execute strSQL, lngSuccess
        cnn.Close

End Sub

Thank you.

~Eric

eric.okeefe · #2 08-16-2017, 03:16 PM

Ok...did some more work on this project and after reading a collaborative post between Greg Maxey and Graham Mayor regarding extraction of data from Word on one of Greg's sites, I learned that mixing legacy (pre-Word 2007) bookmarks and the newer (post-Word 2007) contentcontrols oftentimes causes problems and have subsequently fixed that sticking point. Now, the OLEObjects (the plain-text .txt and .csv files) embedded into the document are contained within a plain-text contentcontrol titled "AttachmentPoint". I no longer receive the error that Word cannot read the content upon subsequent openings of the template-based document (.docx). Hopefully, and I still have my fingers crossed on this one, this will generate some assistance in my endeavor. Several viewings of my post, but largely crickets up to this point.

Although I maintain my issue with transferring the embedded documents to Access lies within the Word VBA coding, if I am somehow barking up the wrong tree then someone, anyone, please tell me so and perhaps provide some advice on where I should be posting this question for resolution...if my goal is even possible. I see the incredibly insightful assistance Greg, Graham and Paul Edstein provide to some of the most eclectic questions within these forums on a regular basis and am hoping I can get the help I need to break free from this conundrum.

Thank you, in advance, to anyone who can get me through this vexing issue.

Cheers!

~Eric

eric.okeefe · #3 08-28-2017, 01:44 PM

Awww, come on...No one, nada, zip, zilch, not a thing? Not even a "dude, you can't do that?!"

macropod · #4 08-28-2017, 05:42 PM

It is recommended you raise Access-related questions in an Access forum such as http://www.accessforums.net/

As it is, we have no idea whether the contents of strClassForm & strReqAgency are suitable for your purpose and I, for one, have little practical experience with Access.

eric.okeefe · #5 08-29-2017, 09:31 AM

Thank you, Paul. I will go next to the Access forums as you recommend. I just figured since all of this vba code is written in a Word template in order to push the data to Access this was the place to start.

If I get an answer there, I will be sure to come back here and let everyone know since it seems to me as though the answer to this problem is going to require vba magic on both sides.

Cheers!

On an aside, how does one mark this thread as done? I don't want it to clutter up the forum.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Export Access report as pdf -- save as .rft -- and Word puts some text into the header	louiseword	Word	1	11-21-2016 04:32 PM
Word file with embedded excel chart	SashikalaB	Word	0	08-11-2016 05:41 AM
Export Word Form Data to Access	pboland	Word VBA	1	06-12-2015 06:53 PM
Adding field in word in header in a bookmark in table with Excel vba Late Binding	Hdr	Excel Programming	6	02-11-2013 02:58 AM
[Word 2007] How to hyperlink directly another word file chapter/paragraph/bookmark?	LeeFX	Word	4	05-05-2011 05:53 PM