The code I'm using is within Excel -- it extracts tables out of multiple Word docs within a folder at once.
It's awesome and even inserts a color bar (colorized/filled row to separate the long batch of tables within 1 sheet).
The results are all stacked within a single sheet of the workbook starting with Column A on Row 4 (Row 3 is the first gray color bar indicating the top of the first table).
*There's a colorized/filled row that separates each table.*
Need the ability to use a MSGBOX to call out a specific table name and
have every occurrence of that table name existing -- extracted and pasted into the next available empty sheet.
(it's taking a bunch of separated tables, finding all that are equal to X and copying them all to another sheet to create one big table)
Example:
6 Word docs, let's say each file was named a diff car:
.......MercedesGLE350SUV.docx, MercedesCL.docx, MercedesS.docx, etc.
Each docx holds a variety of tables such as below:
The same table names are used throughout each docx file in many/most but not all cases (but that's ok).
....WHEEL parts table
....ENGINE parts table
....WIPER BLADE parts table
....EXHAUST system parts table
....CONVERTABLE parts table
....FLUIDS system parts table
All of these tables get pasted consecutively into the Excel sheet that I'm needing the MsgBox to search through --
The analyst needs the ability to plug into the MsgBox the name of a table: "ENGINE"
(and the code run through column A of the active spreadsheet to locate that name)
Once located, it needs to extract all the content that's sitting between 2 colorized rows that are above and below the table... paste it into the next avail spreadsheet... then go back to the orig listing and keep looking for another 'hit' that is equal to that same table name "ENGINE" --
-- if found, copy THAT table (content residing between the bars) and go paste it into that other sheet to continue building one large ENGINE table full of all the ENGINE parts from all the tables named "ENGINE".
Hope that makes sense!?
I'm working with code tables not car parts - but figured the concept of using cars would make it easiest to grasp the need.
Here's a clip of the code that's putting the color bars in place so you can see exactly what color - assuming the code with the MsgBox has to be told to only take what's between those 2 colorized rows until it finds another occurrence and record that chunk as well.
It's awesome and even inserts a color bar (colorized/filled row to separate the long batch of tables within 1 sheet).
The results are all stacked within a single sheet of the workbook starting with Column A on Row 4 (Row 3 is the first gray color bar indicating the top of the first table).
*There's a colorized/filled row that separates each table.*
Need the ability to use a MSGBOX to call out a specific table name and
have every occurrence of that table name existing -- extracted and pasted into the next available empty sheet.
(it's taking a bunch of separated tables, finding all that are equal to X and copying them all to another sheet to create one big table)
Example:
6 Word docs, let's say each file was named a diff car:
.......MercedesGLE350SUV.docx, MercedesCL.docx, MercedesS.docx, etc.
Each docx holds a variety of tables such as below:
The same table names are used throughout each docx file in many/most but not all cases (but that's ok).
....WHEEL parts table
....ENGINE parts table
....WIPER BLADE parts table
....EXHAUST system parts table
....CONVERTABLE parts table
....FLUIDS system parts table
All of these tables get pasted consecutively into the Excel sheet that I'm needing the MsgBox to search through --
The analyst needs the ability to plug into the MsgBox the name of a table: "ENGINE"
(and the code run through column A of the active spreadsheet to locate that name)
Once located, it needs to extract all the content that's sitting between 2 colorized rows that are above and below the table... paste it into the next avail spreadsheet... then go back to the orig listing and keep looking for another 'hit' that is equal to that same table name "ENGINE" --
-- if found, copy THAT table (content residing between the bars) and go paste it into that other sheet to continue building one large ENGINE table full of all the ENGINE parts from all the tables named "ENGINE".
Hope that makes sense!?
I'm working with code tables not car parts - but figured the concept of using cars would make it easiest to grasp the need.
Here's a clip of the code that's putting the color bars in place so you can see exactly what color - assuming the code with the MsgBox has to be told to only take what's between those 2 colorized rows until it finds another occurrence and record that chunk as well.
Code:
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
wkSht.Cells(resultRow, iCol) = WorksheetFunction.Clean(.Cell(iRow, iCol).Range.Text)
Next iCol
resultRow = resultRow + 1
Next iRow
End With
resultRow = resultRow + 1
With wkSht
.Range(.Cells(resultRow, 1), .Cells(resultRow, iCol)).Interior.ColorIndex = 15
End With
resultRow = resultRow + 1
Next tableStart
End With
End Sub