Re: $$Excel-Macros$$ Macro to Import Word Tables to Excel from a folder
Hi,
Thanks so much for the code but for me even though I select the folder
(which currently contains two filled in Word forms), it only transfers the
headings and "Please enter text" for each field even though text has been
filled in. Could this be because I used plain text content controls?
Thanks
On Wednesday, October 2, 2013 at 6:09:44 AM UTC+1, ashish wrote:
>
> try this
>
> Sub import_word_table_to_excel()
> Application.DisplayAlerts = False
> Application.ScreenUpdating = False
> Dim fldpath
> Dim fld, fil As Object
> Dim appWord As Word.Application
> Dim docWord As Word.Document
> Dim tableWord As Word.Table
> Dim sdoc As String
>
>
> ' use to choose the folder having word documents
>
> Application.FileDialog(msoFileDialogFolderPicker).Title = "Choose Folder"
> Application.FileDialog(msoFileDialogFolderPicker).Show
> fldpath =
> Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
> Set fso = CreateObject("scripting.filesystemobject")
> Set fld = fso.getfolder(fldpath)
>
> Set appWord = New Word.Application
> appWord.Visible = True
> For Each fil In fld.Files
>
> ' browse word documents in a folder
>
>
> If UCase(Right(fil.Path, 4)) = UCase(".doc") Or UCase(Right(fil.Path, 5))
> = UCase(".docx") Then
> Set docWord = appWord.Documents.Open(fil.Path)
> For Each tableWord In docWord.Tables
> ' copy word tables
> tableWord.Range.Copy
> ' paste it on sheet 1 of excel file
> Sheets(1).Paste Destination:=Sheets(1).Range("A65356").End(xlUp).Offset(1,
> 0)
> Next
> docWord.Close
> End If
> Next fil
>
>
> appWord.Quit
> Sheets(1).Select
> Set tableWord = Nothing
> Set docWord = Nothing
> Set appWord = Nothing
>
> Application.DisplayAlerts = True
> Application.ScreenUpdating = True
>
> End Sub
>
>
> On Mon, Sep 30, 2013 at 12:47 PM, Nasir Khan > wrote:
>
>>
>> Hello All,
>> I have got lot of MS Word files in a folder eg. C:\Test\> here>
>> I wish to copy and paste the Tables from all the word documents in the
>> above mentioned folder to excel.
>>
>> I found Macro1 (see below) which copy and paste the Table in Excel.
>> My requirement is to get the tables from all the documents from each
>> folder in the path C:\Test\
>> Can Macro 1 be amended *to copy and paste all tables from each folder in
>> C:\Test\*
>>
>> *MACRO 1 TO IMPORT WORD TABLE*
>> Sub ImportWordTable()
>> On Error GoTo errHandler
>> Dim wordDoc As Object
>> Dim wdFileName As Variant
>> Dim noTble As Integer
>> Dim rowNb As Long
>> Dim colNb As Integer
>> Dim x As Long, y As Long
>> x = 1: y = 1
>> wdFileName = Application.GetOpenFilename("Word files
>> (*.docx),*.docx", , _
>> "Browse for file containing table to be imported") 'adjust this to
>> the document type you are after
>> If wdFileName = False Then Exit Sub
>> Set wordDoc = GetObject(wdFileName)
>> With wordDoc
>> noTble = wordDoc.tables.Count
>> If noTble = 0 Then
>> MsgBox "No Tables in this document", vbExclamation, "No
>> Tables to Import"
>> Exit Sub
>> End If
>>
>>
>> For k = 1 To noTble
>> With .tables(k)
>> For rowNb = 1 To .Rows.Count
>> For colNb = 1 To .Columns.Count
>> Cells(x, y) =
>> WorksheetFunction.Clean(.cell(rowNb, colNb).Range.Text)
>> y = y + 1
>> Next colNb
>> y = 1
>> x = x + 1
>> Next rowNb
>> End With
>> x = x + 2
>> Next
>> End With
>> Set wordDoc = Nothing
>> Exit Sub
>> errHandler:
>> MsgBox "Error in generating tables - " & Err.Number & " - " &
>> Err.Description
>> End Sub
>>
>> *MACRO 2 TO RUN ON ALL THE FOLDERS - this gives me runtime error as shown
>> below*
>>
>> Sub RunOnAllFolders()
>> Dim file
>> Dim path As String
>> Dim MyArray()
>> Dim N As Long
>> path = "C:\Test\"
>> file = Dir(path & "*.docx")
>> ReDim MyArray(0)
>> Do While file <> ""
>> If MyArray(0) = "" Then
>> MyArray(0) = file
>> Else
>> ReDim Preserve MyArray(UBound(MyArray) + 1)
>> MyArray(UBound(MyArray)) = file
>> End If
>> file = Dir()
>> Loop
>> For N = 0 To UBound(MyArray)
>>Documents.Open filename:=path & MyArray(N) *'<> ActiveX Component can't create Object >>*
>>
>> *Call ImportWordTable*
>>
>> ActiveDocument.Save
>> ActiveDocument.Close
>> file = Dir()
>> Next N
>> End Sub
>> Can the runtime error 429 be rectified from Macro 2
>>
>> Any help would be appreciated.
>> Regards
>> Nasir Khan
>>
>> --
>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>> https://www.facebook.com/discussexcel
>>
>> FORUM RULES
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Cod
Re: $$Excel-Macros$$ Macro to Import Word Tables to Excel from a folder
Hi, I know I'm a bit late to this so might not get answer but your code is
exactly what I need to do but I get a runtime error on the line Dim appWord
As Word.Application with the error "User-defined type not defined", do you
have any idea why? Thanks so much for the code.
On Wednesday, October 2, 2013 at 6:09:44 AM UTC+1, ashish wrote:
>
> try this
>
> Sub import_word_table_to_excel()
> Application.DisplayAlerts = False
> Application.ScreenUpdating = False
> Dim fldpath
> Dim fld, fil As Object
> Dim appWord As Word.Application
> Dim docWord As Word.Document
> Dim tableWord As Word.Table
> Dim sdoc As String
>
>
> ' use to choose the folder having word documents
>
> Application.FileDialog(msoFileDialogFolderPicker).Title = "Choose Folder"
> Application.FileDialog(msoFileDialogFolderPicker).Show
> fldpath =
> Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
> Set fso = CreateObject("scripting.filesystemobject")
> Set fld = fso.getfolder(fldpath)
>
> Set appWord = New Word.Application
> appWord.Visible = True
> For Each fil In fld.Files
>
> ' browse word documents in a folder
>
>
> If UCase(Right(fil.Path, 4)) = UCase(".doc") Or UCase(Right(fil.Path, 5))
> = UCase(".docx") Then
> Set docWord = appWord.Documents.Open(fil.Path)
> For Each tableWord In docWord.Tables
> ' copy word tables
> tableWord.Range.Copy
> ' paste it on sheet 1 of excel file
> Sheets(1).Paste Destination:=Sheets(1).Range("A65356").End(xlUp).Offset(1,
> 0)
> Next
> docWord.Close
> End If
> Next fil
>
>
> appWord.Quit
> Sheets(1).Select
> Set tableWord = Nothing
> Set docWord = Nothing
> Set appWord = Nothing
>
> Application.DisplayAlerts = True
> Application.ScreenUpdating = True
>
> End Sub
>
>
> On Mon, Sep 30, 2013 at 12:47 PM, Nasir Khan > wrote:
>
>>
>> Hello All,
>> I have got lot of MS Word files in a folder eg. C:\Test\> here>
>> I wish to copy and paste the Tables from all the word documents in the
>> above mentioned folder to excel.
>>
>> I found Macro1 (see below) which copy and paste the Table in Excel.
>> My requirement is to get the tables from all the documents from each
>> folder in the path C:\Test\
>> Can Macro 1 be amended *to copy and paste all tables from each folder in
>> C:\Test\*
>>
>> *MACRO 1 TO IMPORT WORD TABLE*
>> Sub ImportWordTable()
>> On Error GoTo errHandler
>> Dim wordDoc As Object
>> Dim wdFileName As Variant
>> Dim noTble As Integer
>> Dim rowNb As Long
>> Dim colNb As Integer
>> Dim x As Long, y As Long
>> x = 1: y = 1
>> wdFileName = Application.GetOpenFilename("Word files
>> (*.docx),*.docx", , _
>> "Browse for file containing table to be imported") 'adjust this to
>> the document type you are after
>> If wdFileName = False Then Exit Sub
>> Set wordDoc = GetObject(wdFileName)
>> With wordDoc
>> noTble = wordDoc.tables.Count
>> If noTble = 0 Then
>> MsgBox "No Tables in this document", vbExclamation, "No
>> Tables to Import"
>> Exit Sub
>> End If
>>
>>
>> For k = 1 To noTble
>> With .tables(k)
>> For rowNb = 1 To .Rows.Count
>> For colNb = 1 To .Columns.Count
>> Cells(x, y) =
>> WorksheetFunction.Clean(.cell(rowNb, colNb).Range.Text)
>> y = y + 1
>> Next colNb
>> y = 1
>> x = x + 1
>> Next rowNb
>> End With
>> x = x + 2
>> Next
>> End With
>> Set wordDoc = Nothing
>> Exit Sub
>> errHandler:
>> MsgBox "Error in generating tables - " & Err.Number & " - " &
>> Err.Description
>> End Sub
>>
>> *MACRO 2 TO RUN ON ALL THE FOLDERS - this gives me runtime error as shown
>> below*
>>
>> Sub RunOnAllFolders()
>> Dim file
>> Dim path As String
>> Dim MyArray()
>> Dim N As Long
>> path = "C:\Test\"
>> file = Dir(path & "*.docx")
>> ReDim MyArray(0)
>> Do While file <> ""
>> If MyArray(0) = "" Then
>> MyArray(0) = file
>> Else
>> ReDim Preserve MyArray(UBound(MyArray) + 1)
>> MyArray(UBound(MyArray)) = file
>> End If
>> file = Dir()
>> Loop
>> For N = 0 To UBound(MyArray)
>>Documents.Open filename:=path & MyArray(N) *'<> ActiveX Component can't create Object >>*
>>
>> *Call ImportWordTable*
>>
>> ActiveDocument.Save
>> ActiveDocument.Close
>> file = Dir()
>> Next N
>> End Sub
>> Can the runtime error 429 be rectified from Macro 2
>>
>> Any help would be appreciated.
>> Regards
>> Nasir Khan
>>
>> --
>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>> https://www.facebook.com/discussexcel
>>
>> FORUM RULES
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
