|
本帖最后由 xxaadd66 于 2016-5-24 19:42 编辑
别的部门发来的各种指令单,都是doc格式的,我想用宏或者代码直接提取数据批量导入一个excel表格中,请各位高手帮帮忙~~
比如:名称:xxxx 时间:xxxxxxxx 地点:xxxx
净值:xxxx 原值:xxxxxxxxx
这样的word文档数据,提取到excel表格里按列排列到一起,如下:
名称 时间 地点 净值 原值
xxx xxx xxx xxx xxx
代码应该怎么写呢?大神,高手帮帮忙呀~~谢谢了
- Sub Macro1()
- Dim arr, brr, i&, j%, k%
- mypath = ThisWorkbook.Path & "\指令单合集"
- arr = [a1:j1]
- ReDim brr(1 To 2000, 1 To UBound(arr, 2))
- Dim wd As New Word.Application
- wj = Dir(mypath & "*.doc")
- Do While wj <> ""
- With wd.Documents.Open(mypath & wj)
- x = Split(.Range.Text, vbCr)
- n = n + 1
- For i = 0 To UBound(x)
- s = 0
- For j = 1 To UBound(arr, 2) - 3 Step 3
- s = s + 1
- If x(i) Like "*" & arr(1, j) & "*" Then
- For k = j To j + 3
- x(i) = Replace(x(i), arr(1, k) & ":", ",")
- Next
- y = Split(Replace(x(i), " ", ""), ",")
- For k = 1 To UBound(y)
- brr(n, j + k - 1) = y(k)
- Next
- Exit For
- End If
-
- Next
- Next
- .Close False
- End With
- wj = Dir
- Loop
- wd.Quit
- Range("a2").Resize(n, 10) = brr
- End Sub
复制代码
|
|