宏将MS Word表格导出到Excel工作表
答:http://www.mrexcel.com/forum/showthread.php?t=36875
下面是一些代码,读取Word中的表格到Excel的活动工作表。如果Word包含多个表格,它会提示您输入单词文档以及表格编号。
Sub ImportWordTable()
Dim wdDoc As Object
Dim wdFileName As Variant
Dim TableNo As Integer 'table number in Word
Dim iRow As Long 'row index in Excel
Dim iCol As Integer 'column index in Excel
wdFileName = Application.GetOpenFilename("Word files (*.doc),*.doc", , _
"Browse for file containing table to be imported")
If wdFileName = False Then Exit Sub '(user cancelled import file browser)
Set wdDoc = GetObject(wdFileName) 'open Word file
With wdDoc
TableNo = wdDoc.tables.Count
If TableNo = 0 Then
MsgBox "This document contains no tables", _
vbExclamation, "Import Word Table"
ElseIf TableNo > 1 Then
TableNo = InputBox("This Word document contains " & TableNo & " tables." & vbCrLf & _
"Enter table number of table to import", "Import Word Table", "1")
End If
With .tables(TableNo)
'copy cell contents from Word table cells to Excel cells
For iRow = 1 To .Rows.Count
For iCol = 1 To .Columns.Count
Cells(iRow, iCol) = WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text)
Next iCol
Next iRow
End With
End With
Set wdDoc = Nothing
End Sub
该宏应插入到Excel(未字)并放入标准的宏模块,而不是到所述表或工作簿事件代码模块。为此,请转至VBA(键盘Alt-TMV),插入宏模块(Alt-IM),然后将代码粘贴到代码窗格中。像其他人一样(Alt-TMM),从Excel界面运行宏。
如果您的文档包含很多表格,如果您的100+页面表格实际上是每个页面上的单独表格,则可以轻松修改此代码以读取所有表格。但现在我希望这是一张连续的桌子,不需要任何修改。
精益求精。
达蒙
VBAexpert Excel的咨询 (我的其他生命:http://damonostrander.com)
这部分代码是通过每个表,并将其复制到Excel循环的一个。也许你可以创建一个工作表对象,它使用表号作为计数器动态更新你所指的工作表。
With .tables(TableNo)
'copy cell contents from Word table cells to Excel cells
For iRow = 1 To .Rows.Count
For iCol = 1 To .Columns.Count
Cells(iRow, iCol) = WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text)
Next iCol
Next iRow
End With
End With
我无法得到这个工作。双“End With”不对。 – Wikis 2011-12-07 12:40:55
爱它,这绝对是辉煌的,达蒙(即使花了我一年多的时间找到...)。下面是我通过所有表(从选择的表开始)的除循环最终代码:
Option Explicit
Sub ImportWordTable()
Dim wdDoc As Object
Dim wdFileName As Variant
Dim tableNo As Integer 'table number in Word
Dim iRow As Long 'row index in Excel
Dim iCol As Integer 'column index in Excel
Dim resultRow As Long
Dim tableStart As Integer
Dim tableTot As Integer
On Error Resume Next
ActiveSheet.Range("A:AZ").ClearContents
wdFileName = Application.GetOpenFilename("Word files (*.doc),*.doc", , _
"Browse for file containing table to be imported")
If wdFileName = False Then Exit Sub '(user cancelled import file browser)
Set wdDoc = GetObject(wdFileName) 'open Word file
With wdDoc
tableNo = wdDoc.tables.Count
tableTot = wdDoc.tables.Count
If tableNo = 0 Then
MsgBox "This document contains no tables", _
vbExclamation, "Import Word Table"
ElseIf tableNo > 1 Then
tableNo = InputBox("This Word document contains " & tableNo & " tables." & vbCrLf & _
"Enter the table to start from", "Import Word Table", "1")
End If
resultRow = 4
For tableStart = 1 To tableTot
With .tables(tableStart)
'copy cell contents from Word table cells to Excel cells
For iRow = 1 To .Rows.Count
For iCol = 1 To .Columns.Count
Cells(resultRow, iCol) = WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text)
Next iCol
resultRow = resultRow + 1
Next iRow
End With
resultRow = resultRow + 1
Next tableStart
End With
End Sub
下招:工作如何从Word的表...中提取表,我真的想替至?
TC
非常感谢。我必须改变对于tableStart = 1到tableTot到对于tableStart = tableNo到tableTot所以它开始你告诉它的地方。还做了一个修改,让每个表都存储在分离的excel工作簿中。 – javydreamercsw 2014-03-24 17:41:15
谢谢你这么多达蒙和@Tim
我修改它来打开docx文件,用户逃生检查后移动的工作表线条清晰。
下面是最终代码:
Option Explicit
Sub ImportWordTable()
Dim wdDoc As Object
Dim wdFileName As Variant
Dim tableNo As Integer 'table number in Word
Dim iRow As Long 'row index in Excel
Dim iCol As Integer 'column index in Excel
Dim resultRow As Long
Dim tableStart As Integer
Dim tableTot As Integer
On Error Resume Next
wdFileName = Application.GetOpenFilename("Word files (*.docx),*.docx", , _
"Browse for file containing table to be imported")
If wdFileName = False Then Exit Sub '(user cancelled import file browser)
ActiveSheet.Range("A:AZ").ClearContents
Set wdDoc = GetObject(wdFileName) 'open Word file
With wdDoc
tableNo = wdDoc.tables.Count
tableTot = wdDoc.tables.Count
If tableNo = 0 Then
MsgBox "This document contains no tables", _
vbExclamation, "Import Word Table"
ElseIf tableNo > 1 Then
tableNo = InputBox("This Word document contains " & tableNo & " tables." & vbCrLf & _
"Enter the table to start from", "Import Word Table", "1")
End If
resultRow = 4
For tableStart = tableNo To tableTot
With .tables(tableStart)
'copy cell contents from Word table cells to Excel cells
For iRow = 1 To .Rows.Count
For iCol = 1 To .Columns.Count
Cells(resultRow, iCol) = WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text)
Next iCol
resultRow = resultRow + 1
Next iRow
End With
resultRow = resultRow + 1
Next tableStart
End With
End Sub
感谢您的代码。我想我可以修改你的代码来读取所有的表格,但是我如何为每个表格创建一个不同的Excel表格? – QLands 2010-12-16 21:02:27
这些源不保留原始Word表格的文本格式。它是否存在任何解决方案? – 2011-02-08 11:07:09
如果代码在解析表时抛出错误,请尝试在“With wdDoc”行后面添加以下代码:“On Error Resume Next”。这基本上说,如果一个单元格抛出一个可恢复的错误,代码执行不会停止,但会恢复执行到下一个单元格。 – Santhos 2013-01-15 11:36:15