Moving data to Excel from Word (Excel 2003)

[sdckapr]

A suggestion would be to create the "word document" template as a userform in excel and have the form enter the info directly into excel. It could be done with even using a worksheet in excel for entry and then write a macro to save the data.

As to all the other items, it would be possible to do extract in excel from Word but it is not clear to me where all the numbers come from. The Word doc does not seem tot match the Excel table. Some I can guess at where they go, but oher fields are not clear. It also seems that some of the data is not being copied into the excel doc

Some also seems to be "standard data" based on what the pond name is and this is not on the sheet. This type of data should also be stored somewhere (for either methodology) so it can be obtained when the extraction is being done.

Either way can be done (it might even be easier to do with access) but it should be able to be done directly only using excel and not using word at all. (I think it would be easier to just use excel, but I don't use word alot, esp for creating data tables). If you could elaborate on what you need and where you might want some help, we should be able to help you out.

Steve

[ledunch]

Yes Steve, it would be a lot easier to have the data sheet template done in Excel. It turns out this is a highly constrained exercise. One of those constraints is the computer ability and resources of the volunteers. I moved them from paper and pencil to e-mail and finally to a Word template. Even with that application only about 1/4 of the volunteers can handle the transcription into Word - many simply do not have it on their computers. The bottom line is that I cannot use an Excel template.

Not all of the data from the template goes into this particular excel file -examples being names of the testers, comments, and conditions at the time of the test. The excel file I am concerned with is what I might call the physical data file, that is the numerical results of the measurements. Also included in that file are those parameters that might be used in graphical comparisons of that data like date, time, pond area, instrument used. Another important data set in the spreadsheet are the nutrient data from the lab analysis of the water samples. I have to input that separately since the results show up months later.

As you point out, the standard data can be accessed from various places. So while I am looking for suggestions as to a general approach to automate this data transfer, focusing on some specifics might help to high light the problem. Lets say we need the Secchi data and the dissolved oxygen data taken out of the template and placed into the spreadsheet. Each dissolved oxygen measurement has three pieces of data (depth, temperature and oxygen) so each triad of data from a row on the template table wants to find its way onto a row in the spreadsheet . The related data (pond name, date, time, air temp, station depth, oxygen meter) from the template also wants to go on that row in the spreadsheet. Finally, some standard data for that pond fills out the rest of that row along with a designation as to what type data it is, for example DO for dissolved oxygen. The Secchi data, is quite simple being a single number. So it wants to go on a separate row in the spreadsheet along with its related and standard data and its test designation SD. Well, actually, the Secchi data in the spreadsheet wants to be the average of the two values on the template.

The reason for my question is that no matter what I do I have to put in effort and some learning to get it done. That being the case, I would like to know that the approach I take is a good one- efficient, extendable, as simple as possible, or whatever good means. If the consensus is that this should be done with a VBA approach that is fine. If its something else that also fine. I guess the bottom line is I do not feel qualified to choose the approach, though I expect I will be able to eventually implement it.

Hope this helps clarify a bit.
Paul

[sdckapr]

Could you detail based on the WORD example, what info you want to extract and how/where you want it in an excel file?

I presume you want info placed at the end of the current excel file, but I am not sure where all the info comes from based on the template. The example word file would not seem to generate the excel example file. It would help if you detailed what you want extracted from the word tables and where you want the info in the excel file.

Steve

[sdckapr]

The items in Cols B, C, D: do you have a place where, based on the "pond name" they may be looked up at run time? If so, you could have these filled as well. It would be nice to have it available when the code is running since the "depth" (col J) for "phos" (column N) items needs the max depth (col D) when the word template indicates "Bottom" (the value entered is 1 m less than max).

A "lookup table" could be hardcoded in the macro, but it makes more sense that a table be available somewhere in a workbook to look up the values.

I could imagine: The lookup table and code could be in 1 workbook. You have another workbook open (and the active workbook) with the "pond data" (where you want the word data added) and then you have word running with all the templates open to be added. The code would loop thru the open word templates, grabbing the info from the various word tables, filling in the info at the end of the active excel book.

One other question (at least for now): the online template puts a comment with the oxygen meter number in it. Do you want this included?

If so, Do you want the name included in the comment and whose name: the "volunteer" from the template, the "username" from whomever is running the code or someone else? Excel adds it automatically as the user name for whomever is entering it manually, but the macro could do what you wanted...

Steve

[sdckapr]

I didn't notice until now that you were extracting all the other info as well (I was concentrating on your original samples)

I will leave it to you, as an exercise, to learn how to grab the info from the other columns and place it into the proper place in the workbook. I think the code could be modified without too much trouble to add the extra items.

If you have specific questions about it, let us know.

Steve

[ledunch]

Wow a complete solution!
I am your humble servant Steve.
When I get back this evening I will exercise the code. I am fairly confident I will be able to handle any simple additions to what you have already provided. I am also fairly confident that if I want to make the routine handle additional functions in the future I will be back with additional questions. Having a wonderful start like the one you have provided is such a great help.
Many thanks.

Paul

[sdckapr]

You are welcome. I hope it works out for you.

Steve

[ledunch]

Steve,

Your macro does what I need when run with a single Word data sheet opened. When I have multiple Word data sheets opened, however, a data placement problem occurs. Specifically, the data which should be placed in columns K, L & M are placed in columns J, K & L . This is true for all but the first data sheet, which has its data properly placed . Comments to remedy this problem would be appreciated.

Paul

[sdckapr]

I spent a little time refamiliarizing myself with what was done last year at this time. I could not replicate the problem with the sample files here.

This suggests:
I do not understand the problem
one (or more) of the word files has changed format
the excel file has changed.

Could you attach a sample workbook (with the code you use) along with 2 word files that when both are opened, the problem occurs.

Steve

[sdckapr]

Move the line:
<pre> Set AWF = Application.WorksheetFunction
iColItems = 6
<font color=red> iCol = 10</font color=red>
On Error Resume Next</pre>


to:
<pre> For Each wrdDoc In wrdApp.Documents
<font color=red> iCol = 10</font color=red>
lFirstRow = wks.Range("A65536").End(xlUp).Row + 1
lRow = lFirstRow</pre>


The problem is a fault in my origiinal logic.
IThe col starts at 10 for the first table, but it gets set at the end of printing to 9 so for each subsequent document it starts at 9 instead of 10.

By moving the initial setting to 10 to within the loop, it will start at 10 for each doc.

Steve

[ledunch]

My initial results using your proposed fix suggests that things are now working fine.
I will experiment a bit more before processing all of last year's data.

Many thanks,
Paul

[ledunch]

Steve,

I have used your macro to process all of my 2005 data with great results. Now, after being away for 10 days and starting to work on my 2006 data, I get an error message when the macro is run : "Subscript out of range". Thinking I might have somehow set up my new 2006 workbook incorrectly, I went back to the 20005 workbook and I get the same result. There is no indication as to which subscript is at fault. Do you have any ideas for troubleshooting this problem?

Paul

[sdckapr]

What line of code gives the error?

[Click on "debug" and see what line is highlighted]

Steve

[ledunch]

Steve,
For reasons unknown to me nothing was highlighted in debug. Prompted by your note, I stepped through (F8) the code and found the problem. The code could not find the Pond Info data.

When I finished the 2005 data, and before I left on my trip, I took the Pond Info worksheet out of the workbook (and forgot I took it out) and sent a copy of the workbook off to the appropriate people . When I started work on the 2006 Workbook I opened a separate workbook called Pond Info assuming, without justification or further thought, that this would be ok. It was not.

I've now got a Pond Info worksheet in the 2006 workbook and things are back to working.
I'm afraid my novice status with VBA is showing.

Thanks,
Paul

[sdckapr]

Glad you found your problem.

Steve