{"id":64803,"date":"2007-05-23T23:11:00","date_gmt":"2007-05-23T23:11:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/05\/23\/how-can-i-save-each-paragraph-in-a-word-document-as-a-database-record\/"},"modified":"2007-05-23T23:11:00","modified_gmt":"2007-05-23T23:11:00","slug":"how-can-i-save-each-paragraph-in-a-word-document-as-a-database-record","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-save-each-paragraph-in-a-word-document-as-a-database-record\/","title":{"rendered":"How Can I Save Each Paragraph in a Word Document as a Database Record?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! A few days ago you showed us how to open a bunch of text files and save the data in each file as a record in a database. I have a similar question, except that I want to save each paragraph in a Word document as a database record. How can I do that?

— HY <\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, HY. Before we tackle today\u2019s question we know a lot of you are concerned about Microsoft\u2019s recent purchase of online advertising giant aQuantive, Inc.<\/B><\/A>, and whether that will affect the Script Center in any way. Rest assured that there is nothing for you to worry about. In fact, as the good folks at Contoso Cola always say, \u201cDon\u2019t worry; drink Contoso.\u201d<\/P>\n

Contoso Cola. One sip and you\u2019ll think, \u201cThis is a cola<\/I>?\u201d<\/P>\n

In other words, no, the fact that Microsoft spent $6 billion on an online advertising company will have no effect on the Script Center whatsoever.<\/P>\n

This 30-second Script Center update was brought to you by Tailspin Toys. Tailspin. Because anything else is just a toy.<\/P>\n

In case you\u2019re wondering, according to the press release this acquisition will definitely enhance the \u201cbroader advertising ecosystem.\u201d Now, to tell you the truth, the Scripting Guys have no idea what is meant by the \u201cbroader advertising ecosystem.\u201d We\u2019re hoping, however, that this means \u201cwriting a script that can save each paragraph in a Word document as a database record.\u201d If that\u2019s the case, then the Scripting Guys can help to enhance the broader advertising ecosystem, too:<\/P>

Const adLockOptimistic = 3<\/p>\n
Set objConnection = CreateObject(“ADODB.Connection”)\nSet objRecordSet = CreateObject(“ADODB.Recordset”)<\/p>\n
objConnection.Open _\n    “Provider = Microsoft.Jet.OLEDB.4.0; ” & _\n        “Data Source = C:\\Scripts\\Test.mdb” <\/p>\n
objRecordSet.Open “SELECT * FROM WordParagraphs” , _\n    objConnection, adOpenStatic, adLockOptimistic<\/p>\n
Set objWord = CreateObject(“Word.Application”)\nobjWord.Visible = True<\/p>\n
Set objDoc = objWord.Documents.Open(“C:\\Scripts\\Test.doc”)<\/p>\n
Set objSelection = objWord.Selection<\/p>\n
For i = 1 to objDoc.Paragraphs.Count\n    objDoc.Paragraphs(i).Range.Select\n    If Len(objSelection.Text) > 1 Then\n        objRecordSet.AddNew\n        objRecordSet(“ParagraphNumber”) = i\n        objRecordSet(“ParagraphText”) = objSelection.Text\n        objRecordSet.Update\n    End If\nNext\n<\/PRE>\n
Thanks to the peace of mind that comes when you\u2019re insured by Humongous Insurance, we should be able to take a few minutes to see if we can figure out how this script works. To begin with, we have a very simple little Word document (C:\\Scripts\\Test.doc), a document that looks like this:<\/P>\"Microsoft \n

We also have a very simple little Access database (C:\\Scripts\\Test.mdb). This database includes a table named WordParagraphs; that table contains two fields:<\/P>\n\n\n\n\n
\u2022<\/TD>\n\n
ParagraphNumber<\/B>, a field we\u2019ll use to keep track of paragraphs by number (i.e, the first paragraph in the document is number 1, the second paragraph is number 2, etc.).<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
ParagraphText<\/B>, a memo field where we\u2019ll stash the text from each paragraph.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
OK, let\u2019s roll up our sleeves and get down to business, the same outstanding business that Woodgrove Bank has provided for the past 50 years. Woodgrove Bank: We treat your money as if it was our money.<\/P>\n
We start out by defining a constant named adLockOptimistic and setting the value to 3; we\u2019ll use this constant to tell the database to lock a record only when that record is being updated. We then use the following block of code to create instances of the ADODB.Connection <\/B>and ADODB.Recordset<\/B> objects, and to open the datatase C:\\Scripts\\Test.mdb:<\/P>
Set objConnection = CreateObject(“ADODB.Connection”)\nSet objRecordSet = CreateObject(“ADODB.Recordset”)<\/p>\n
objConnection.Open _\n    “Provider = Microsoft.Jet.OLEDB.4.0; ” & _\n        “Data Source = C:\\Scripts\\Test.mdb”<\/PRE>\n
And then we use this line of code to grab a recordset consisting of all the records in the table WordParagraphs:<\/P>
objRecordSet.Open “SELECT * FROM WordParagraphs” , _\n    objConnection, adOpenStatic, adLockOptimistic<\/PRE>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. Yes, we did kind of zip through all that database stuff, didn\u2019t we? If you could use a little more background information on working with databases you might want to take a peek at the Scripting Guys webcast<\/B><\/A> on that very subject.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
It\u2019s now time to shift gears a little bit and turn our attention to Microsoft Word. (Having trouble shifting gears? Then maybe it\u2019s time to call Margie\u2019s Travel at 555-1283.) To begin with, we use these two lines of code to create an instance of the Word.Application<\/B> object and to make that instance visible on screen:<\/P>
Set objWord = CreateObject(“Word.Application”)\nobjWord.Visible = True\n<\/PRE>\n
Once Word is up and running we use the Open<\/B> method to open the document C:\\Scripts\\Test.doc:<\/P>
Set objDoc = objWord.Documents.Open(“C:\\Scripts\\Test.doc”)\n<\/PRE>\n
After that we create an instance of the Word.Selection<\/B> object, an object we\u2019ll need in order to grab the text from each paragraph. At this point we\u2019re ready to have some real<\/I> fun, the same kind of fun offered each and every day at the Alpine Ski House.<\/P>\n
As it turns out, every Word document contains a Paragraphs<\/B> collection; as the name implies, this is a collection of all the paragraphs found in the document. If we want to get information about each paragraph (which we do) then we need to set up a For Next loop that runs from 1 to the total number of paragraphs in the document (something we can determine by using the Paragraphs collection\u2019s Count<\/B> property). Or, to put it a bit more programmatically:<\/P>
For i = 1 to objDoc.Paragraphs.Count\n<\/PRE>\n
What do we do inside the loop? Well to begin with, we\u2019re going to select the very first paragraph; that\u2019s what this line of code does:<\/P>
objDoc.Paragraphs(i).Range.Select\n<\/PRE>\n
By calling the Select<\/B> method against the paragraph range, we end up selecting the entire paragraph and automatically storing it in our object reference objSelection. That means that we can then check to see how many characters are in the Text<\/B> property of objSelection:<\/P>
If Len(objSelection.Text) > 1 Then\n<\/PRE>\n
Why do we do that? Well, if you look back at our Word document you\u2019ll see that we have blank lines between each paragraph. However, these lines aren\u2019t truly blank, at least not in the sense that there\u2019s absolutely nothing there. Instead, each \u201cblank\u201d line contains one of Word\u2019s paragraph marks. What we\u2019re doing with this line of code is checking to see if the paragraph text has more than one character (the Len<\/B> function, of course, tells us the number of characters in a string). If the Text length is equal to 1 we\u2019re going to assume that this is a blank line and simply skip it; we\u2019ll only save the paragraph to the database if the Text has more than 1 character.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. This is probably a good time to note that this script works only with straight text documents. If you have a bunch of tables in your document we can\u2019t guarantee that this script will work. It might work, but, then again, it might not.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
Let\u2019s assume that we have a \u201creal\u201d paragraph here, a paragraph containing more than a single character. In that case we execute this block of code:<\/P>
objRecordSet.AddNew\nobjRecordSet(“ParagraphNumber”) = i\nobjRecordSet(“ParagraphText”) = objSelection.Text\nobjRecordSet.Update\n<\/PRE>\n
As you probably figured out, this is the code that saves the paragraph text as a record in the database. To do that we start out by creating a new, blank record; that\u2019s why we call the AddNew<\/B> method. We then assign the value of our counter variable i to the ParagraphNumber field and the paragraph text (objSelection.Text) to the ParagraphText field. With the values assigned we then call the Update<\/B> method to actually write the new record to the database.<\/P>\n
From there we loop around and repeat the process with the next paragraph in the collection.<\/P>\n
That should do the trick, HY. If it doesn\u2019t, feel free to email use courtesy of the Fabrikam Furniture Script Center. Fabrikam: The most trusted name in home furniture is now the most trusted name in system administration scripting. 
<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. OK, don\u2019t send any angry emails to Microsoft. We made this all up; we have not sold out and become the Fabrikam Furniture Script Center.<\/P>\n
Although we are<\/I> willing to listen to offers \u2026.<\/P><\/TD><\/TR><\/TBODY><\/TABLE><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! A few days ago you showed us how to open a bunch of text files and save the data in each file as a record in a database. I have a similar question, except that I want to save each paragraph in a Word document as a database record. How can I […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[84,49,3,5],"class_list":["post-64803","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-microsoft-word","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! A few days ago you showed us how to open a bunch of text files and save the data in each file as a record in a database. I have a similar question, except that I want to save each paragraph in a Word document as a database record. How can I […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64803","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=64803"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64803\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=64803"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=64803"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=64803"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}