将数据从Word文档提取到Excel SpreadSheet [英] Extract Data from Word Document to an Excel SpreadSheet

问题描述

我需要每天从word文档中提取一个值，并将其写入excel工作簿。我目前手动执行此操作，它是关于我最有效的方法的边界线。

I have a requirement to extract a value from a word document on a daily basis and write it to an excel workbook. I currently do this manually and it is border line regarding the most efficient method for me

1. 使用Excel文件创建一个vba脚本并添加任何单词文件参考。

2使用单词导航到表格9。 STOCKS ...（提取示例如下 - 附录A），并阅读以红色突出显示的柴油（ltrs）日常使用量。

2 Using the word navigate to the table "9. STOCKS..." (extracted example below – Appendix A) and read the Diesel (ltrs) daily usage highlighted in red.

3.将此值写入电子表格单元格。

3.Write this value to a spreadsheet cell.

1. 这个值的日期也是关键，但它保存在word文档的另一部分（附录B）中。日期也是文件名，但我们信任的内部值超过单词文档名称。使用第3和第4点的知识将关联的日期提取到相邻的电子表格单元格。

下表显示了表格，因为格式我无法向您发送确切的表格，但我可以发送它的值。

The table is displayed below, because of the formatting I'm not able to send you the exact table but I will be able to send the values of it.

9.STOCKS（截至当天00:01）的报告）。
股票持有
每日使用
最低库存

9.STOCKS (As of 00:01 hrs on Day of report issue). Stock Held Daily Usage Minimum Stock

柴油（ltrs）

390436
<强> 15012
25000

Diesel (ltrs)
390436 15012 25000

氮（mm）

35
1
19
冠军1033（手提包）

15
1
4
Nexguard（锅炉）
4

0.25
4 x 200 ltrs

Nitrogen (mm)
35 1 19 Champion 1033 (totes)
15 1 4 Nexguard (Boilers) 4
0.25 4 x 200 ltrs

附录B：
报告的比较期：
00:01 - 24:00 2010年8月10日

Appendix B: Beatrice Period of Report: 00:01 – 24:00 10th August 2010

如果您对我的问题有任何疑问，请回复我，感谢您的努力，并希望提前感谢

If you have any doubts regarding my question please get back to me, I appreciate your efforts and wanted to thanks in advance

推荐答案

这里是一些使用后期绑定的代码（声明对象而不是word.application等）。从 Excel 2003 ，它

here's some code making use of late binding (declare objects rather than word.application etc). From Excel 2003, it

1. 打开WORD文档


2. 对于字符串最小库存


3. 将光标移动一些行/字进一步


4. 展开/选择WORD光标


5. 将此WORD选择粘贴到EXCEL

1. opens a WORD document
2. searches for string "minimum stock"
3. moves the cursor some lines/words further
4. expands/selects the WORD cursor
5. pastes this WORD selection into EXCEL

对于报告期间重复步骤2-5：（注意 ：是一个字边界，所以我们需要跳8个字到右边到达日期）

steps 2-5 are repeated for "Period of report:" (note that the ":" is a word boundary, so we need to jump 8 words to the right to arrive at the date)

对于WORD，我从Q复制了文本（没有表，只是纯文本）。如果您使用表格，可能需要使用各种 Move 语句的单元（例如，对于单元格 unit：= 12 ）;策略保持不变：找到一个常量文本，将光标移动到最终目的地，展开选择，创建一个单词范围并传输。

For WORD I copied the text from your Q just as is (no table, just plain text). If you use tables instead, you may need to play with the units of the various Move statements (e.g. for cells unit:=12); the strategy remains the same: find a constant text, move cursor to final destination, expand selection, create a word range and transfer.

两个项目都放在当前单元格中在Excel及其右邻居。

Both items are placed into the current cell in Excel and its right neighbor.

Sub GrabUsage()
Dim FName As String, FD As FileDialog
Dim WApp As Object, WDoc As Object, WDR As Object
Dim ExR As Range

    Set ExR = Selection ' current location in Excel Sheet

    'let's select the WORD doc
    Set FD = Application.FileDialog(msoFileDialogOpen)
    FD.Show
    If FD.SelectedItems.Count <> 0 Then
        FName = FD.SelectedItems(1)
    Else
        Exit Sub
    End If

    ' open Word application and load doc
    Set WApp = CreateObject("Word.Application")
    ' WApp.Visible = True
    Set WDoc = WApp.Documents.Open(FName)

    ' go home and search
    WApp.Selection.HomeKey Unit:=6
    WApp.Selection.Find.ClearFormatting
    WApp.Selection.Find.Execute "Minimum Stock"

    ' move cursor from find to final data item
    WApp.Selection.MoveDown Unit:=5, Count:=1
    WApp.Selection.MoveRight Unit:=2, Count:=2

    ' the miracle happens here
    WApp.Selection.MoveRight Unit:=2, Count:=1, Extend:=1

    ' grab and put into excel        
    Set WDR = WApp.Selection
    ExR(1, 1) = WDR ' place at Excel cursor

    'repeat
    WApp.Selection.HomeKey Unit:=6
    WApp.Selection.Find.ClearFormatting
    WApp.Selection.Find.Execute "Period of Report:"
    WApp.Selection.MoveRight Unit:=2, Count:=8
    WApp.Selection.MoveRight Unit:=2, Count:=3, Extend:=1

    Set WDR = WApp.Selection
    ExR(1, 2) = WDR ' place in cell right of Excel cursor

    WDoc.Close
    WApp.Quit

End Sub

您可以创建一个按钮并调用那个sub从那里，或者将GrabUsage（）链接到一个功能键。

You can create a button and call that sub from there, or link GrabUsage() to a function key.

我评论了 WApp.Visible = True 因为在生产中你不希望WORD甚至显示出来，但是你需要它来调试和玩游标。

I commented out the WApp.Visible = True because in production you don't want WORD even to show up, but you will need it for debugging and playing with the cursor movements.

后期绑定的缺点（而不使用对Word库的引用）是单元的硬编码（6 =故事，5 =行，2 =单词），而不是使用Word枚举，但我有时会早日绑定操作系统崩溃....不是很性感但似乎有效。

The disadvantage of late binding (and not using references to the Word library) is the hardcoding of units (6=story, 5=line, 2=word) instead of using Word enumerations, but I sometimes get OS crashes with early binding .... not very sexy but it seems to work.

FileDialog对象需要引用MS O ffice办公室图书馆。 AFAIK这是Excel 2003中的标准，但是要比崩溃更好。

The FileDialog object needs a reference to the MS Office Office Library. AFAIK this is standard in Excel 2003, but better to check than to crash.

我没有包含代码来检查项目是否真的找到;我把它留给你的创造力。

And I didn't include code to check if the items are really found; I leave this to your creativity.

希望有所帮助。

这篇关于将数据从Word文档提取到Excel SpreadSheet的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！