Hi everyone!

I hope this is a simple task for many of you.  If any clarifications
are required, please write back!  If it's more complex, please don't
hesitate to let me know, too.

I have several Word 2003 forms that consist of about 10 tables.
In each table, I have several calculated fields, dropdowns, and
numeric text that the users type in (the forms are, of course,
protected.)

We need to transfer this data into Excel (not as a link, but rather
just a straight-ahead copy and paste.

Right now, we accomplish this in a backwards-ass manner, as follows:

At the bottom of my all my forms, I have a table of about 100 cells
wide, one column high.  (Yes, I know Word's limit is 64 cells, so I
actually pasted two tables side-by-side in another table!)

Now, very simply, in this table, I simply paste each bookmark, so it
looks like this:

JAN 2003 M.G. (555) 444-1212 $50.00 $20.00 0 5 5 5 5 4 1 1 

Then, to get it into Excel, I recorded a keystroke macro that just
jumps to the bottom of the table, and does a Shift-Up Arrow, Copy,
Reprotect the doc.

Voila!

In the Windows clipboard, we how have the data that we need.

I then simply head over to Excel, and hit paste!

In comes the data.

Then I will open up the next word form, hit my macro.  Back to Excel. 
Move a line down, and paste!

So, in the end, my Excel table, from 5 forms, would look like this:

JAN 2003 M.G. (555) 341-1252 $20.00 $20.00 1 5 3 5 5 4 9 1 
AUG 2005 E.V. (555) 443-1215 $54.00 $20.00 0 3 5 5 5 5 1 1 
JAN 2003 M.E. (555) 444-1232 $80.00 $20.00 0 5 5 1 5 4 1 6 

...and we're done.

But this is ridiculous, I think, especially since that table at the
bottom adds another 70K to the file.  And it's just not cool.

If I could just press a button on my button bar, and have it scoop up
all the bookmark values into the clipboard (or something) allowing me
to get them to Excel, that would be great.

I have about 30 different Word forms, but all based on the same one. 
Therefore, all the bookmarks are similar (DATE / AMOUNT_1 / INITIALS)
etc.

The answer should be relatively flexible.

I don't wnat it to just work for ONE form.

Rather, it must be universal to copy 'em, and allow me to then paste
them.  Alphabetical A-Z is fine...  That way, I could take any form,
and use the same macro.

(Maybe there is some type of linking that would be done...  After all,
the copy-table-in-Word/Paste-Table-in-Excel was only my silly
concoction)


I hope there's a way to copy all the bookmark values in a row to
Excel.  (and, I guess along with it, should be another macro button
that spits out the bookmark NAMES in the same way, so that I can align
everything in my Excel scoresheet.

ie.
Date-Year-Initials-Phone_number-Price_1-Price_2- Q_1,Q_2,Q_3
JAN 2003 M.G. (555) 341-1252 $20.00 $20.00 1 5 3 5 5 4 9 1 
AUG 2005 E.V. (555) 443-1215 $54.00 $20.00 0 3 5 5 5 5 1 1 

Thanks!

Mike

PS I've never really used Google Answers before.  I hope I am clear,
and get a solution that consists of two buttons that work:  One to
paste the bookmark names into Excel cells/Windows clipboard, and
another macro to copy in the Values!

---

Clarification of Question by mike514-ga on 17 Jan 2005 00:14 PST

Hi there,

I should just clarify one thing...

I'm really hoping for some flexibility.

I mentioned that it's okay if the macro spits out the bookmarks in
alphabetical order...

I'd have to take that back. 

That would give me the absolute messiest data set.

I would need to sort the way the data comes out.

It would be wonderful if there was a list of bookmarks
DATE
DAY
TIME
YEAR

and then I select the order in a column next to it.

Of course, that may take some work to code that.. so we could arrange
for a tip or however that works... (but maybe it's simple...!?)

Thanks!

---

Request for Question Clarification by hammer-ga on 31 Jan 2005 18:49 PST

Mike514,

Could you possibly upload one of these Word documents to someplace
where we could download it and see exactly what form your bookmarks
take? They may be accessible through VBA.

Also, you say you have lots of Word forms. Are these named in such a
way that we could automatically find them in a batch, or do you have
to open each one by hand to get one row out of each?

- Hammer

---

Clarification of Question by mike514-ga on 01 Feb 2005 12:12 PST

Hi Hammer,

Thanks for the reply.  Great question.

Right now we open up each form one by one.  

We run the macro that jumps to the bottom of the forms, and copies the
table to the clipboard.

If we could have something that copies each bookmark of every file in
a folder, then I think I'd cry out of joy.

I KNOW there is a way to so something like this.

I recently discovered the option in Word to "Save Data Only for forms"

I ended up with:
"The Leather Company","","January","24","2005","11","50","A","M","First
Pro Etobicoke","ON","Monday","12","10","A","W","0","3","0" etc.

WOW... this is really on the right track.

I can gladly paste a few word documents, as well as the Excel sheets
up on our FTP site for you.

Is there a place where I can E-mail you a password?  I'd rather not
put it up for the world to see!

...I know there's a simple solution somewhere! :)  We can't be the
first company that wants to copy a bunch of Bookmarks from Word to
Excel.

And again, to reiterate, I am am more than willing to increase the
price of this question if programming is involved.

Thanks!
Mike

---

Request for Question Clarification by hammer-ga on 01 Feb 2005 13:18 PST

Mike514,

We are not allowed to contact you privately, so you will need to
upload the documents to a public area.

- Hammer

---

Clarification of Question by mike514-ga on 01 Feb 2005 19:34 PST

Hey Hammer,

Okay, what I will do is create a dummy Word document with a handful of
bookmarks and a sample table at the bottom.  Therefore there will be
no issue of confidientiality.  I have no idea where I can post Word
and Excel files.

My FTP site has my company name in it, and I don't want the world to see. Hmmm..

If you have any ideas, please let me know.  If not, I will simply sign
up for an MSN group.. that should solve it.

I'll do it later.  If you can think of an easier solution, I'm all ears!

Thanks!

Mike

---

Clarification of Question by mike514-ga on 02 Feb 2005 23:09 PST

Hi again,
I seem to be having trouble here.
I am not sure how to upload files to MSN, and allow you access,
without giving out my MSN password.

Do you know of any places where I can put something up that will allow
people to download from it anonymously?

I'd love to get this underway!

Thanks!

Mike

---

Clarification of Question by mike514-ga on 10 Feb 2005 20:59 PST

Hey CK!

My knowledge of this stuff us pretty lousy.

First of all -> Good advice with creating a macro and then cutting and
pasting your code in.  That is what I usually do in these situations.

Second -> Great comment about the line breaks.  I figured that out
before I read your latest post!

Now...

1 - In tools, references, I did include MS WOrd 11.0 Object Library.
2 - I do have Office 2003
3 - I wrote over my temporary macro, so now GetWordValues is in my library.
4 - When I run it, I receive the following:

No Cells were Found.

Where should I place the Word docs in question?

I tried putting them into (1) My Documents (2) the default Excel
folder (3) Copied the files to the clipboard.  (4) left a file open.

Same message.

Help, please! :)

Hi,

There are more than a few ways to accomplish this. I've made multiple
programs to pull values from Word into Excel and vice versa. I've done
this using Macros and also from within VB.Net programs. The simplest
way is to create a reference to the Word doc (in an Excel macro) and
loop through all the different form fields (Provided each form has a
unique bookmark). You can then use the bookmark names to select the
column name in Excel that it would go under.

In this case you wouldn't need to have anything other than the forms
in Word. I also have code that opens a window that allows you to
select which Word doc to open as well as code that loops through every
Word doc in a folder. If the answer isn't posted in a few days I'll
push some of the code up for you to see.

CK

Holy Moley... I think that is EXACTLY what we're looking for!!

I just wish there was a way for me to post up a few sample Word docs
and a sample Excel sheet, just to show what I mean.

But CK, it seems like you are definitely onto something.

I know I am not allowed to mail you a password to access an FTP site,
nor send you an attachment via E-mail.

What if I were to open a gmail account, send myself the stuff via an
attachment, and then post the username and password here.  That should
work.

I'll throw up a Word doc or two, one of our Excel sheets, and a screenshot or two.

This would be great if it works!

Thanks!

Mike

Hey Mike,

Here's the code I just wrote for an Excel macro that should do the
trick for you. I haven't tested it too much but it should work for
your needs. No idea what your skill level is but to make it easy,
record a new macro in Excel and then edit it and overwrite it with
everthing here from Option Explicit down. This macro was created in
Excel 2003 but it should work with 2000 and up. Make sure you include
a reference to the Microsoft Word Object (in Tools - References)
before saving the Macro.

Good luck,

Charles K.

'---------------
Option Explicit

'Make sure you include a reference to the Microsoft Word Object (in
Tools - References)
Public WordObj As Word.Application
Public intRow As Integer

Sub GetWordValues()
' Macro recorded 2/10/2005 by CK
' Keyboard Shortcut: Ctrl+Shift+O

OpenWordDoc

End Sub

Private Function OpenWordDoc() As String
    
    Dim fd As FileDialog
    Dim x As Integer
    
    On Error GoTo TheEnd
    
    Set WordObj = CreateObject("Word.Application")
    
    'Create a FileDialog object as a File Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogOpen)
      
    On Error GoTo InvalidNumber
    
    'Get the starting row number
    intRow = CInt(InputBox("Please enter the last Row used", "Row
Number Input", "1"))
    
    On Error Resume Next
    
    If intRow = 0 Then intRow = 1
    
    With fd
    .AllowMultiSelect = True
    .Filters.Clear
    .Filters.Add "Word Files", "*.doc"
    
    If .Show Then
        If .SelectedItems.Count > 0 Then
            For x = 1 To .SelectedItems.Count
                'Pass the file name and the current file count
                FillExcel .SelectedItems.Item(x), x
            Next
        End If
    End If
    
    End With

    Set fd = Nothing

    WordObj.Quit
    Set WordObj = Nothing

Exit Function

InvalidNumber:
    If MsgBox("You entered an invalid number, OK to start at 2?",
vbOKCancel, "Invalid Row") = vbCancel Then
        WordObj.Quit
        Set WordObj = Nothing
        End
    Else
        intRow = 2
        Resume Next
    End If

TheEnd:
    MsgBox Err.Number & " => " & Err.Description, vbOKOnly, "Error
Opening Word Doc"
    WordObj.Quit
    Set WordObj = Nothing
    End
    
End Function

Private Function FillExcel(strFile As String, intFileNum As Integer)

   Dim WordDoc As Word.Document
   Dim WordRange As Word.Range
   Dim x As Integer
   
   On Error Resume Next
    
   Set WordDoc = WordObj.Documents.Open(strFile)
    
   'Uncomment the ' on the next line to see Word cycle through the values
   'WordObj.Visible = True
      
   'Activate the current WorkSheet (Change Sheet1 to whatever
WorkSheet you're using)
    Worksheets("Sheet1").Activate
    
    'Prompt to use FieldNames as Column Headers
    If intFileNum = 1 Then
        If MsgBox("Use FieldNames as Column Headers?", vbYesNo,
"Column Headers") = vbYes Then
            For x = 1 To WordDoc.FormFields.Count
                'This line takes the name of the bookmark and puts it
on the first row (only for the first doc opened)
                ActiveSheet.Cells(1, x) = WordDoc.FormFields(x).Name
            Next
        End If
    End If
             
         
   'Loop through all the fields
   For x = 1 To WordDoc.FormFields.Count
    
    'intRow is the file order number (each file will import on a new row)
    'the x is the column - Essentially it'll cycle through all the
bookmarks in the Word doc
    
    ActiveSheet.Cells(intRow + intFileNum, x) = WordDoc.FormFields(x).Result
   
   Next
    
    WordDoc.Close
    Set WordDoc = Nothing

End Function

Hmm, noticed that it might not have formatted correctly when I posted
it so when you paste it in Excel, if you see any lines that are red it
probably means that the line shouldn't have been broke. Go to the end
of the line or the one above it and hit Delete then hit the Space bar.
You should be able to figure out which ones are broken by running it
(it'll give you an error).

CK

Hi there!  I tried out the macro, and pasted the following results...
Unfortunately, I added to the Clarify Question portion, and not the
Comments portion.  I guess that explains why I might not have received
a reply..!  Here is where I'm at:
=======================
Hey CK!

My knowledge of this stuff us pretty lousy.

First of all -> Good advice with creating a macro and then cutting and
pasting your code in.  That is what I usually do in these situations.

Second -> Great comment about the line breaks.  I figured that out
before I read your latest post!

Now...

1 - In tools, references, I did include MS WOrd 11.0 Object Library.
2 - I do have Office 2003
3 - I wrote over my temporary macro, so now GetWordValues is in my library.
4 - When I run it, I receive the following:

No Cells were Found.

Where should I place the Word docs in question?

I tried putting them into (1) My Documents (2) the default Excel
folder (3) Copied the files to the clipboard.  (4) left a file open.

Same message.

Help, please! :)

Mike,

I tried wrecking the code in numerous places but I can't seem to get
the same error you received. A few things I noticed was that it
behaved erratically when another Word document was open so make sure
all Word Documents are closed before you run it.

If you "Step Into" the Excel Macro instead of just running it and then
keep hitting F8 you'll be able to "Walk" through the code line by
line. If you tell me the last line that you're on before the error
message shows up I should be able to help you better. In any case, you
should be prompted with three windows:
1. Please enter the last Row used.
2. File Open (where you select the Word Document(s) you want to import from)
3. Use FieldNames as Column Headers?

You can tell by the second window that the Word files can be anywhere
on your machine (though if you select multiple files they need to be
in the same folder). It would be best to test it using only 1 file at
a time in the beginning to figure out how it works.

The error you are getting is in relation to cells therefore the
program is either having trouble writing the information into Excel or
there are some tables in the Word document that are causing issues.

One thing this program needs is an Excel WorkSheet called Sheet1. To
test if it's your Word doc that's having the issues, the easiest way
to make sure is to create a new Word doc with only 5 or 6 formfields
in it (all with valid bookmark names) and no tables. After that make
sure the document is locked (to close the fields) and then enter some
data. Save the doc and close Word before running the Macro I gave you
in Excel. The three message boxes should come up and when it asks for
a file make sure you choose only the new Word doc you just created. If
it works you'll know the Excel Macro is good and that something in
your Word doc is to blame. If this is the case, I'll need to see one
of your Word samples to further root out the cause.

Another possibility is that you're missing other References. In Tools
- References you should see:

1.Visual Basic for Applications
2.Microsoft Excel 11.0 Object Library
3.Microsoft Office 10.0 Object Library
4.OLE Automation
5.Microsoft Word 11.0 Object Library

If one of these is missing, add it.

Other than that my hands are tied. Try the above and let me know has
it goes. If nothing works, I'll need a copy of your files to test it
myself.

Good Luck,

Charles K.

Thanks Charles!

I am going to try this right now, even if it's 2:15 in the morning!

I'll have a follow-up within the hour!!!

Thanks!

Mike

Hello again!
Okay, new problem.

I am stepping through it, and this is how it goes.  Within four lines, it crashes:


Private Function OpenWordDoc() As String

On Error GoTo TheEnd

Set WordObj = CreateObject("Word.Application")

MsgBox Err.Number & " => " & Err.Description, vbOKOnly, "Error Opening Word Doc"


I get the following dialog box:
"Error Opening Word Doc"
-2147221231 => Automation Error
ClassFactory cannot supply requested class

Then when I step back into it,
I get a runtime error 91:
Object Variable or With block variable not set.

And yes, I have these:

1.Visual Basic for Applications
2.Microsoft Excel 11.0 Object Library
3.Microsoft Office 10.0 Object Library
4.OLE Automation
5.Microsoft Word 11.0 Object Library

I have set up an account on my FTP server.

If you want me to post up my Excel file, please let me know!

Thanks!!

Mike

I thought I could solve it, but no luck.

I did a Google search on -2147221231, and it seems like it's an error
that creeps up now and again when one MS Office app tries to access
another.

One way to solve it was to ensure all the VB runtimes are installed.

So I went to the MSFT website, and downloaded VB6.0-KB290887-X86.exe,
which installs Visual Basic 6.0 SP6 run-time files.

I was so sure it would work after rebooting, but..no dice.

By the way, my Windows XP PRO is fully updated with every critical and
optional patch.

I also tried disabling my virus scanner.

Nothin...

I found someone have a similar problem here:
http://www.mrexcel.com/archive/VBA/16052.html

And also, I came across the following on MSDN's channel 9:
(I did what he did.. changed Word.Application to Excel.Application,
and it went a little further.  It paused for about a second, then I
got an "Error Opening Word Doc 13 => Type mismatch error.
So at least we know it has something to do with Word, just like this MSDN guy:

Hello all, I hope someone here can shed a little light on this problem
I've started having recently. I have a little JS script that I use to
check the spelling of posts I make online by calling Word's spell
checker from IE. I recently upgraded to Office 2003 and since doing so
I get the following error when I run the script: ClassFactory cannot
supply requested class (code 80040111). The offending line of the
script is shown below:

app= new ActiveXObject("Word.Application");

If you replace Word With Excel, PowerPoint, Visio, InfoPath, etc the
script runs just fine and the application opens as expected. Only when
I try to create an instance of Word do I get the error.

Windows XP Pro (SP1)
Office 2003 (SP1)
WSH 5.6

I'd appreciate any help. I know there are other spell checkers for IE
but the fact that this used to work and no longer does is just
bothering me. I've googled for it, but the hits I get aren't terribly
relevant. I tried the knowledgebase but got no love there either.

TIA

Hi Mike,

The problem your facing with the Excel Macro is most likely a VBScript
issue not Visual Basic. Try downloading and installing the latest
VBScript from Microsoft.

http://www.microsoft.com/downloads/details.aspx?familyid=c717d943-7e4b-4622-86eb-95a22b832caa&languageid=f49e8428-7071-4979-8a67-3cffcb0c2524&displaylang=en

Since I've tried the macro on several machines, I'm thinking that it's
something on your machine that is wrong. If you still can't get it to
work after this, I'll need a copy of your files to go on any further.

Cheers,

Charles

Hi Charles,

Well... here's something interesting.. I have three computers here,
and I'm getting some results on one of them!

I downloaded that VBScript thing, but no dice on my machine.

If only I can get it to run here, I'd be thrilled!

I did run it on my partner's PC... really neat!  Getting onto the
right track, here!

Asisde from this crashing on my machine, if you could please let me
know if this slight change in functionality it possible, that would be
awesome:

1 - Minor Enhancement
Is there a way to have it ask on what column it should start pasting? 
Right now, it pastes everything from Column A.  If it could ask either
which offset, or which column letter to start pasting, that would
rock.  I do see it in the code, but I have never done any vbasic
programming so I don't know how to set up a dialog box to ask the
usuer for the first column number...

2 - Bookmarks vs. Formfields
It's posting everything in the document.  
Every FormField, when all I need is every Bookmark.
I was able to modify the header row by changing 

    ActiveSheet.Cells(intRow, x) = WordDoc.Formfield(x).Name
to
    ActiveSheet.Cells(intRow, x) = WordDoc.Bookmarks(x).Name

The header row worked like a charm.  Instead of having blank spaces,
representing the formfields that aren't bookmarks, it now just listed
the bookmarks.  (This was the first thing I ever did in Visual Basic!
I haven't touched code since working on BASIC, Pascal and Cobol while
back in University

But when I changed:
 ActiveSheet.Cells(intRow + intFileNum, x) = WordDoc.FormFields(x).Result
to
 ActiveSheet.Cells(intRow + intFileNum, x) = WordDoc.Bookmarks(x).Result

But that didn't work.  There must be something to return the value in
the bookmark...

(Also, just to let you know, as it's going through the FormFields,
it's returning them in the order that they appear in the document. 
When I change it to BookMarks, it spits it out in alphabetical order. 
This is perfect.  Depending on how I name my bookmarks, I am able to
pick in which order they will appear.)


I reallllly hope we can get this working on my machine!  It's
interesting how my google searches turned up several other people with
the same error.

(I just sent it to my colleague who does the data entry of the
reports.  If we get it to pop out bookmarks and not formfields, and it
works on her machine, I'm happy!  Then whatd'ya say we double the
amount to get it work on mine!?)

THanks for everything!!!

Hi Mike,

Just to let you know, I'm not the one getting the money. I'm just a
guy that was looking into something, stumbled upon your problem and
thought I'd lend a free hand. I have no idea who would get the money
since I neither work for Google nor do I know how they operate. This
is simply goodwill and payment in part to all the free help I've
gotten over the years from anonymous users.

Since I would either need access to your machine or spend a bunch of
time on possible scenarios, the issue you're having with your machine
is outside of my control. Sorry I can't spend more time helping you
out. My only suggestion at this time is to either re-install Office
2003, see if there are any Office updates you get or call Microsoft
support.

As to a few of your requirements, here's a few things to get you on
the right track.

To add a column selection, add this to the line directly beneath
"Public intRow As Integer"

Public IntCol as Integer

Add this to the line directly beneath "intRow = CInt(InputBox("Please
enter the last Row used", "Row Number Input", "1"))"

intCol = CInt(InputBox("Please enter the last Column used", "Column
Number Input", "0"))

Add this to the line directly beneath "Else   intRow = 1"

intCol = 0

Change the line from "ActiveSheet.Cells(1, x) = WordDoc.FormFields(x).Name" to

ActiveSheet.Cells(1, x + intCol) = WordDoc.FormFields(x).Name

Change the line from "ActiveSheet.Cells(intRow + intFileNum, x) =
WordDoc.FormFields(x).Result" to

ActiveSheet.Cells(intRow + intFileNum, x + intCol) = WordDoc.FormFields(x).Result

That should take care of your Column selection.

The Bookmarks to FieldForms issue gets a little harder since they're
not exactly the same thing. Essentially, you can have FormFields
without names and Bookmarks without FormFields. I name every FieldForm
and the only Bookmarks I use are connected to FieldForms so I never
have the issue you discussed.

Though you can get the name of the Bookmark the way you decribed,
getting the value is a little different. Not knowing exactly how you
set up your Word Document (and to be honest wondering why you'd have a
FormField without a name and also not wanting to use the values in
it), I have this suggestion. Of course this will create a column for
every Bookmark in your Word Document so if you have any that aren't
associated with a FieldForm, they will show also.

Change both of the lines "For x = 1 To WordDoc.FieldForms.Count" to

For x = 1 To WordDoc.Bookmarks.Count

Change the line from "ActiveSheet.Cells(1, x + intCol) =
WordDoc.FieldForms(x).Name" to

ActiveSheet.Cells(1, x + intCol) = WordDoc.Bookmarks(x).Name

Change the line from "ActiveSheet.Cells(intRow + intFileNum, x +
intCol) = WordDoc.FormFields(x).Result" to

ActiveSheet.Cells(intRow + intFileNum, x + intCol) =
WordDoc.FormFields(WordDoc.Bookmarks(x).Name).Result

That should take care of both your issues however remember that if the
FormField doesn't have a Bookmark name in your Word Document it won't
show up in Excel.

Mike, good luck with everything else and it's been a pleasure helping you out.

Charles K.

Hi Charles!
I just wanted to you thank you very much for all your help.

I had no idea that you weren't a "Google Researcher"!  This help is
VERY much appreciated!

I originally wrote two nights ago, but I had included contact info,
because I figured I'd love to send more little projects like this your
way for a fee.

Unfortunately, I received an E-mail back from Google as I was not
allowed to include personal contact information.  They removed the
comment.

Today I will type in the changes, below, and hopefully I'll be up and running!

Thank you again for all your help!  I really really appreciate it!

Best regards,

Mike

Hi Charles!

Are you (or anyone) by any chance still monitoring this thread??

I have a slight problem here, and I am hoping someone can shed some insight!

Please do reply if this thread is still alive, and I'll post the question!

Thanks all!

Mike