英文:
VBA: Extract specific word table to excel
问题
编辑：所以我成功识别出所有的Word文档。然而，我在提取表格第2列第2行数据时遇到了问题。
错误：设置Table = doc.Table
对象变量未设定
Word表格如附图所示。我只想提取从第2行第2列开始的"x"。
希望有人能帮助我解决这个问题。先谢谢！
子 Open_Multiple_Word_Files(sPattern As String)
子 Search_it()
英文:
Edited: SO I managed to identify all th word docs. However, I'm having a problem achieving to extract the 2nd column row 2 data of the table.
Error: Set Table = doc.Table
Object variable block
The word table looks like the attached img. I only wanted to extract the "x" starting from row 2 column 2..
Hoping someone can actually help me on this.. Thank you in Advance!
Sub Open_Multiple_Word_Files(sPattern As String)
Dim shApp As Object
Dim shFolder As Object
Dim File, Files
Dim currentPath As String
Dim doc As Word.Document
Dim wd As New Word.Application
Dim sh As Worksheet
'get folder
Set shApp = CreateObject("shell.application")
folder2search = "C:\Users\ChrisLacs\Desktop\extrac\"
Set shFolder = shApp.Namespace(folder2search)
'get files
Set Files = shFolder.Items()
'check if file names match the pattern
For Each File In Files
If File.Name Like sPattern Then
Set Table = doc.Table
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row + 1
For i = 1 To 6
sh.Cells(lr, i).Value = Application.WorksheetFunction.Clean(Table(1).Rows(i).Cells(2).Range.Text)
Debug.Print File.Name & " - " & (File.Name Like sPattern)
Next
End If
Next File
End Sub
Sub Search_it()
Open_Multiple_Word_Files "Form*"
End Sub
答案1
得分: 1
你的代码只会打开一个单词文件，因为这行代码：
Set doc = wd.Documents.Open(ActiveWorkbook.Path & "\Form.docx")
只会打开你指定的那个文件。
要打开特定文件夹中匹配模式 Form* 的所有Word文件，可以使用以下方法：
Sub Open_Multiple_Word_Files(sPattern As String)
Dim shApp As Object
Dim shFolder As Object
Dim File, Files
Dim currentPath As String
' 获取文件夹
Set shApp = CreateObject("shell.application")
folder2search = "C:\Users\...\Documents"
Set shFolder = shApp.Namespace(folder2search)
' 获取文件
Set Files = shFolder.Items()
' 检查文件名是否与模式匹配
For Each File In Files
If File.Name Like sPattern Then
' 在此处添加匹配时要执行的操作。
' Debug.Print File.Name & " - " & (File.Name Like sPattern)
End If
Next File
End Sub
Sub Search_it()
Open_Multiple_Word_Files "Form*"
End Sub
这段代码会打开匹配模式的所有Word文件。
英文:
Your code only opens a single word file, because this line:
Set doc = wd.Documents.Open(ActiveWorkbook.Path & "\Form.docx")
only opens the specifit file that you say so.
One way to open all word files in a specific folder that match the pattern Form* is the following:
Sub Open_Multiple_Word_Files(sPattern As String)
Dim shApp As Object
Dim shFolder As Object
Dim File, Files
Dim currentPath As String
'get folder
Set shApp = CreateObject("shell.application")
folder2search = "C:\Users\...\Documents"
Set shFolder = shApp.Namespace(folder2search)
'get files
Set Files = shFolder.Items()
'check if file names match the pattern
For Each File In Files
If File.Name Like sPattern Then
'write here things to do when pattern s matched.
'Debug.Print File.Name & " - " & (File.Name Like sPattern)
End If
Next File
End Sub
Sub Search_it()
Open_Multiple_Word_Files "Form*"
End Sub
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论