PDA

View Full Version : Copy/Parse Data from MS Word to MS Excel



Straxus162
10-23-2013, 11:31 AM
Hey everyone, This is something that I have researched, but it seems most solutions rely on the word document to have form fields. Unfortunatly, the documents I am working with (which number in the hundreds) do not. They are, however, completely identical short of date/time and a unique ID number. What I would like to do is have a vba code that, for a selected word document: looks for "X min:", copies the number string positioned to the right of it, and pastes it into column A in excel. Looks for "Y min:", copies the number string to the right, pastes into column B in excel. Looks for "whatever:", copies, pastes into column X, and so on and so fourth. Information that may or may not be useful: Some "whatever"s are not left justified and are tabulated after other "whatever"s but not all - as such, not all the data has its own line. The data I am looking to copy is always numerical. All "whatever"s are unique... I have a looming feeling I am overlooking a simple solution. Any help/suggestions/hints are accepted with gratitude!

snb
10-24-2013, 01:20 AM
It would help if you posted 1 word file and a sample workbook to illustrate where you want which information to be written.
And no, there's no simple solution, unless you provide enough information to design one with you.

Kenneth Hobs
10-24-2013, 05:58 AM
Welcome to the forum!

What you ask is not simple and bound to fail. It will fail because the structure is not there. That is why we create databases. That said, you can make the attempt. For us to help you, as snb said, you need to help us by supplying at least one MSWord file and an example Excel file with the data manually entered as you expect the macro to import. The first task is to make it process one file. A batch routine is then used to process all files.

To give you some ideas in the meantime, see the FindReplace routines where the find part is part of what you need.

'TypeText method
' http://www.excelforum.com/excel-programming/650672-populate-word-document-from-excel.html#post1946784
' http://www.excelforum.com/showthread.php?p=1946784
' http://vbaexpress.com/forum/showthread.php?p=169877
' http://vbaexpress.com/forum/showthread.php?t=24693
' http://www.excelforum.com/excel-programming/791302-excel-to-word-paragraph-and-page-setup.html

'Copy from Excel, paste to Word
'Lucas, http://vbaexpress.com/forum/showthread.php?p=178364

'FormFields
' http://www.mrexcel.com/forum/showthread.php?p=1639696
' http://www.mrexcel.com/forum/showthread.php?t=333200
' http://www.excelforum.com/excel-programming/799070-import-text-fields-from-word.html
' Content Controls
' http://www.vbaexpress.com/forum/showthread.php?t=39654

'Add Hyperlink to Bookmark
' http://www.excelforum.com/excel-programming/664078-use-excel-vba-to-add-a-hyperlink-to-a-word-document.html#post2006430
'Steiner, http://www.vbaexpress.com/kb/getarticle.php?kb_id=126
'Colin_L, http://www.mrexcel.com/forum/showthread.php?t=358054

'Save OLEObject as MSWord Document
' http://vbaexpress.com/forum/showthread.php?t=21619
' http://vbaexpress.com/forum/showthread.php?t=24292
' http://www.excelforum.com/excel-programming-vba-macros/940687-excel-export-to-ole-object-with-user-prompted-saveas-help.html?p=3336342

'Add Table to MSWord
' http://vbaexpress.com/forum/showthread.php?t=23975
' http://vbaexpress.com/forum/showthread.php?p=168731

'Import Word Tables
'vog, http://www.mrexcel.com/forum/showthread.php?t=382541
'Ruddles, http://www.mrexcel.com/forum/showthread.php?t=524091

'snb, Word Tables
' http://www.vbaexpress.com/forum/showthread.php?t=45520
' http://www.vbaexpress.com/forum/showthread.php?t=46472


'Get Optionbutton info from MSWord DOC
' http://vbaexpress.com/forum/showthread.php?t=22454

'FindReplace Text
' http://www.excelforum.com/excel-programming/682014-replace-word-in-ms-word-with-varable-from-ms-excel.html
' http://www.vbaexpress.com/forum/showthread.php?t=38958
' http://www.vbaexpress.com/forum/showthread.php?p=250215
' http://www.vbaexpress.com/forum/showthread.php?t=42833
' http://support.microsoft.com/kb/240157
' http://word.tips.net/T001833_Generating_a_Count_of_Word_Occurrences.html

' http://www.excelforum.com/excel-programming/794297-struggling-with-a-find-replace-macro-to-word.html

'Bookmarks
' http://vbaexpress.com/forum/showthread.php?p=185718
'Colin_L, http://www.mrexcel.com/forum/showthread.php?t=358054
' http://www.vbaexpress.com/forum/showthread.php?p=253277

'Mail Merge
' http://www.excelforum.com/excel-programming/796614-mail-merge-from-excel.html
' http://www.excelforum.com/excel-programming/798299-print-mail-merge-document.html
'Word 's Catalogue/Directory Mailmerge facility (the terminology depends on the Word version). _
To see how to group records with any mailmerge data source supported by Word, _
check out my Microsoft Word Catalogue/Directory Mailmerge Tutorial at:
' http://lounge.windowssecrets.com/index.php?showtopic=731107
' or
' http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip
' Mail Merge from Excel to MSWord: ElephantsRus
' https://app.box.com/s/0zlydxinl10t23mifkrr

Straxus162
10-24-2013, 09:27 AM
Thank you both for the prompt responses! I will have to sift through those links later today after my classes.

After posting, I was tinkering with some code and was able to get what could be a step in the right direction, but I will let you professionals decide that... I wll be able to post said code sometime tomorrow afternoon (the machine I was working on is not remotely accessible). Same goes for the sample documents.

Straxus162
10-25-2013, 09:13 AM
Sorry for my complete forum newbie incompetence, but...Im trying to post the sample code in the VBA brackets, but when I preview the post all the code is on one line. The same thing happened to my original post when asking the question (sorry about that). What am I doing wrong? Also, when I attempt to attch the sample documents I click on "Manage Attachments" but get a "Error on Page" message. This may be due to my workplace network security, in which case I will have to replicate samples later, at home. Just previewed again, and my line breaks are not showing up so to me this reply looks like a mess.

Kenneth Hobs
10-25-2013, 09:46 AM
Try pasting to a worksheet and then copy and paste that.

Straxus162
10-25-2013, 10:26 AM
Unfortunatly that did not work, but thank you for the suggestion. I believe it is problem with the browser I am using. I will continue posting form my home, where this issue should not be a problem. ...and now my reply with a URL is being rejected, so I cannot even show you the thread I recently started to work with (not my post). I will pick this back up at home, too many problems from this machine...

Straxus162
10-25-2013, 03:02 PM
Finally at home! Woooooo Friday! Anyways,
I attached the xl sheet, but Ill need to replay again with the doc (limit one per post).

Below is the code I've been tinkering with... it seems close, but what do I know.

Sub ParseWord_WIP()
Dim appExcel As Object
Dim objSheet As Object
Dim appWord As Object
Dim objDoc As Object


Dim aRange As Range
Dim xmax As Range


Set appWord = GetObject(, "Word.Application")
Set objDoc = appWord.ActiveDocument


Set appExcel = GetObject(, "Excel.Application")
Set objSheet = appExcel.ActiveWorkbook


Set aRange = objDoc.Content
'With aRange
aRange.Execute FindText = "X: Max,1N-mils:"
If aRange.Found Then
aRange.Expand Unit:=wdSentence 'I want to end the expandtion to a tab
xmax = Mircosoft.VisualBasic.Right(aRange, 9)
xmax.Copy
aRange.Collapse wdCollapseEnds 'Dont know if this is needed
objSheet.Paste _
Destination:=Range("D" & ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row + 1)
End If
'.Execute FindText = "Y: Max,1N-mils:"
'If...
'same thing
'Paste in Column E
'End If
'Repeat for all wanted data
'End with
End Sub


I've added comments as a preview of what I want to change or add...


Set aRange = objDoc.Content

Is the line thats been giving me grief. As it is, I get "Mismatched Types" whilst debugging, but if I change it to, say, Application.Content, it say "Requires Object"
Perhaps I've been staring at this too long, and am missing something simple? I'm also not experienced with objects in vba At. All.

Thanks for baring with me thusfar!

Straxus162
10-25-2013, 03:05 PM
sample Data attached, sorry for the extra post...

P.S. it is wonderful to not be stuck posting blocks of text.

snb
10-25-2013, 03:56 PM
Sub M_snb()
With GetObject("G:\OF\0_docsample.docx")
sp = Split(.storyranges(7).Text)
sn = Split(Replace(Replace(.Content, Chr(9), " "), vbCr, " "))
.Close 0
End With
Sheet1.[A3:I3] = Array(sp(0), sp(UBound(sp) - 1), sn(27), sn(2), sn(5), sn(8), sn(11), sn(15), sn(19))
End Sub

Straxus162
10-25-2013, 04:10 PM
Thank you for the response. I appreciate you taking the time to work out a code.
I do have a few questions though... First, I should have mentioned this, the actual data document looks very,very different. My sample document only has the relevant information from said document, and it is in the format I showed you. There is a large chart of data on the sheet, all superfluous - but its there.

Does your code depend on the location of the data?
TBH, Im not sure what its doing since my understanding of vba is fairly low,

MSDN's description and examples for .storyranges is... lacking, in my opinion.

snb
10-26-2013, 03:02 AM
May I remind you to my post http://www.vbaexpress.com/forum/showthread.php?48007-Copy-Parse-Data-from-MS-Word-to-MS-Excel&p=299267&viewfull=1#post299267

If you do not post exact samples what are we doing here ?

Straxus162
10-28-2013, 03:27 PM
What I am looking for is the code I posted to function. The samples I provided are more than sufficient for testing that code, as it should not require any specific formatting or configuration. (If my code is unreasonable, please let me know)

If you can help me figure out what it wrong with that one line, I will be able to replicate the syntax for the rest of my criteria. I hope to get this form of code working because it will also then become universal to any of my future parsing needs, just in case.

As a recap, here is the order of operations for what I am looking for:
Document is OPEN; Workbook is OPEN
*Run macro from Excel
-> Macro Finds X in MS Word, gets the numerical string to the right of X
-> Copy numerical string, Paste into Excel under column A
-> Repeat for all criteria
-> END

I'll admit, it wont be the most graceful code - but I really don't need it to be.

Any help with what I've been working with would be greatly appreciated.