You are here:

Excel/repeat the same procedure

Advertisement


Question
Hello Damon,

I hope You can help me to modify the code below. I found it to copy word data to excel but it is only for one file.
I would like to repeat the same procedure for all the files in the folder.
Would You be so kind to give some advice on this matter.
I would appreciate any assistance!
Kind regards,

attis

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

Answer
Hi Attis,

This modified code should do what you want:

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 to any file in folder containing tables to be imported")
  
  If wdFileName = False Then Exit Sub '(user cancelled import file browser)
  
  'current (working) folder is now folder containing Word documents to be looped through
  
  wdFileName = Dir("*.doc")
  
  Do
  
     'debug.Print wdfilename  'uncomment this line if you want files processed listed in Immediate window.
  
     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
     
     wdFileName = Dir()
     
  Loop Until wdFileName = ""

End Sub

This will loop through all the Word files in the folder.  Of course, you may need to modify this code to direct data from each Word table to an appropriate place in the Excel file such that it does not overwrite data from previous Word files/tables.

I hope you find this helpful.

Damon

Damon
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Damon Ostrander

Expertise

I have extensive experience with VBA programming in Excel 5 through Excel 2013. As a former aerospace engineer with a large aerospace corporation and consultant in a small defense technology services company, I have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.

Experience

I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.

Education/Credentials
B.S. in Electrical Engineering and Computer Science, University of California, Berkeley.

©2016 About.com. All rights reserved.