Combine data from multiple workbooks into one worksheet based on condition

[CYC]

I have a folder with many workbooks in it. Each workbook has multiple worksheets with different names, but they all have a single worksheet called trend. Looks like below. (I cut out the first few columns to save space).

<TABLE style="WIDTH: 338pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=450 border=0 x:str><COLGROUP><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" width=57><TBODY><TR style="HEIGHT: 39pt" height=52><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 79pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 39pt; BACKGROUND-COLOR: transparent" width=105 height=52> material 1</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 55pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=73>material 2</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 53pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=71>material 3</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 57pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=76>material 4</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 51pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=68>isntrument</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 43pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=57>Result</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num></TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num></TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num></TD></TR></TBODY></TABLE>

I need to be able to copy rows of data from the trend worksheet from all the workbooks into a single worksheet only if the result column in each row is not a zero.

Thanks in advance!

-Chris

 

[Fazza]

Hi Chris.

There are various approaches. Simplest to describe is a manual (non-code) approach. With code it can be done the same way - query table - or other, such as ADO+recordsets.

From a new workbook, go via menu data, import external data, new database query, Excel files. Browse for one of the data files and then OK. If you have a message about no visible tables, hit OK and then 'options' and then select 'system tables', and OK to that too. The worksheet names should now appear on the LHS. From the 'trend' worksheet, select from the little dropped down list the fields you want - they will list on the RHS. Continue. Take the option to apply a filter to the 'Result' field so that you have results where 'Result' <>0.

When you get the option, go to MS Query to edit. This is just to add the other files.

When MS Query opens you will see the returned dataset. Hit the 'SQL' icon and edit the text string so that the other files are now used too. So change the initial SQL which will be something like

SELECT `material 1`, `material 2`, `material 3`, `material 4`, isntrument, Result
FROM file_reference_1
WHERE Result<>0

so that for each additional file you append this text

UNION ALL
SELECT `material 1`, `material 2`, `material 3`, `material 4`, isntrument, Result
FROM file_reference_n
WHERE Result<>0

When the full SQL is created, exit the SQL editing box and then hit the `open door` icon and return the results to Excel.

This is now a refreshable query table: refresh it like a pivot table.

Via code obviously saves some mucking around with the text entry.

HTH. Regards, Fazza

 

[CYC]

Thanks for the reply, and having tried this method I think it would be great for some of the things we do. However, I still think I need a VBA solution because by the end of the year, we may have as many as 500 workbooks to pull data from.