Lots of Excel forums have many complicated ways to attempt to make this work. One data table has the transactions, and another table stores the chart of accounts. Take a look at the pivot table field list, Sales table has been added. Before we get too far, let’s jump up to 30,000 feet. The formulas we can write far surpass those available in a traditional PivotTable. No Manage Data Model, Ah, yes, you are correct. Work Faster. Therefore on insertion of a PivotChart in a workbook without a model, a PPM will be created. If you’d like to learn how to build a PivotTable using the data model, and learn what the data model is, strap in…this will be a fun post. And below code is for this: 'Define Pivot Cache Set PCache = ActiveWorkbook.PivotCaches.Create _ (SourceType:=xlDatabase, SourceData:=PRange). So go to a blank section of your dashboard and choose Insert, PivotTable. How to Build a PivotTable with the Data Model, https://support.office.com/en-us/article/where-is-power-pivot-aa64e217-4b6e-410b-8337-20b87e1c2a4b. They will be able to open the workbook and view the PT without the source files, no problem. But, here’s the thing. When I create a Pivot Table to include the ID and Name from Table 1 and the Color from Table 2 in the rows field the Colors from table 2 show up 3 times each for all 3 IDs for a total of 9 items instead of once each for a total of 3 items which is what I want. One question: if I send someone the pivot table created, so I also need to send them the base workbooks I used to create it? Each pivot table is connected to an underlying pivot cache, which is connected to the source data. As expected, the macro creates a Pivot Table from a dynamic range. You can also create a Filter for your Pivot Table using VBA. My motto is: Table 2 (ID Column: 123, 456, 789 & Color Column: Blue, Pink, Green). Normally when you create a pivot table, Excel automatically creates a pivot cache without asking you, but when you need to use VBA, you need to write a code for this. In this VBA Tutorial, you learn how to create a Pivot Table with different destinations (both worksheet or workbook) and from both static and dynamic data ranges. To create a Pivot Table from a dynamic range (where the number of the last row and last column may vary) with VBA, use a macro with the following statement structure: The macro below creates a new Pivot Table from a dynamic range, where the last row and column is dynamically identified. https://support.office.com/en-us/article/where-is-power-pivot-aa64e217-4b6e-410b-8337-20b87e1c2a4b Declare Variables. Jeff. To create a Pivot Table in a new sheet with VBA, use a macro with the following statement structure: The following macro creates a new Pivot Table in a new worksheet. VBA for creating "Calendar" table in the Data Model Hi All, In Power Pivot, it is possible to generate a "calendar" table and add it to the data model with a couple of clicks via the headers (POWER PIVOT -> Deisgn -> Date Table -> New) but when I complete these steps when recording a Macro the recording output is blank. Learn Excel. Place the cursor on any cell in the table. Not to my knowledge (other than downloading the add-in for supported versions). Because you have defined relationships, however, the collection of tables and relationships comprise a Data Model. If you have multiple pivot tables and lists in an Excel file, you might need to identify which data source each pivot table uses. You can get immediate free access to these example workbooks by clicking the button below. This will enable to create summary by using Distinct Count. Our plan is to create a PivotTable from two tables. Our Campus Pass includes access to our entire Undergrad and Masters catalog. In Pivot table’s Create Pivot Table dialogue box, we will select the source as “Use this workbook’s Data Model”. I need to prepare a summary in sheet " Backlog_Summary " for unique ticket no. It is a very small amount of code that makes the process very easy. I created a fake and simple data set to test this out. And, bam … done! Power Spreadsheets is not affiliated with the Microsoft Corporation. On the Excel Ribbon's Data tab, click the Manage Data Model command In the Power Pivot for Excel window, on the Home tab, click the Design View command In the diagram, right-click on the field name that you changed, and click Rename Type the field name, the way that you entered it in the source data table – I changed DAY to Day The so-called Excel “data model” is actually a Power Pivot database consisting of Excel worksheet tables linked to Power Pivot tables. Both might contain a State column, but in both tables those fields will contain duplicates, so you can’t use the State field to create a … The following GIF illustrates the results of executing this macro example. Dear all, I have data, ticket details in sheet " Backlog " which had duplicate entries. But, they just won’t be able to Refresh unless they have access to the source files. Refreshing Pivot Table. Plus, in addition to having multiple lookup tables in your data model, you can also have multiple data tables. Some versions of Excel do not include Power Pivot including the Home version. We’ll now see the tables with the column names (instead of seeing the data transactions), as shown below. You cannot directly access the Data Model. The data model comes with Excel 2016+ for Windows, and was formerly available as the Power Pivot add-in. Then, the fields in “data” table go into the PivotTable VALUES area. To build a pivot report you have to select the fields to the filters, values, etc. The Data Model feature lets you create a basic relational database structure within Excel. Historically, we would need to use VLOOKUP or something to first combine these tables into a single table to use with a traditional PivotTable. Many of the typical restrictions are removed when you use the data model rather than a single Excel table. Next, we import the data tables. To create a Pivot Table in an existing sheet with VBA, use a statement with the following structure: SourceData is of the Variant data type. If you'd like to be notified when I write a new Excel article, enter your name and email and click SUBSCRIBE. For security, use of Google's reCAPTCHA service is required which is subject to the Google Privacy Policy and Terms of Use. The following GIF illustrates the results of executing this macro example. And, yes, we can pick fields from either or both of the tables for our report. Use the Get External Data command to point to the underlying data source. I am using Excel 16.37 (Office 365 Version) on a Mac and I would like to create a pivot table using data from multiple sheets (without using VBA Script). Here are my top 4 picks: Copyright © 2015–2021 PDS Intelligence Pte. Coming back to Pivot Tables. The fields in the “lookup” tables go into the PivotTable ROW/COLUMNS layout area. Creating from a SQL query. Notify me of follow-up comments by email. Justin, Click Power Pivot > Add to Data Model. The updated Power Pivot window is shown below. The following GIF illustrates the results of executing this macro example. As expected, the macro creates a Pivot Table in a new workbook. I created both tables and linked the ID column between both and added to data model. The following VBA and Macro Tutorials may help you better understand and implement the contents below: You can find additional VBA and Macro Tutorials in the Archives. If all goes well, you see the PivotTable Fields dialog box with all tables that are included in the Internal Data Model, as shown. You can only see the multiple tables in the Data Model in the Fields list of PivotTable or PivotChart and use them. Normally, to create a pivot table from the Sales data, you would select one cell in your Sales table first and choose Insert, PivotTable. I love sharing the things I've learned about Excel, and I built Excel University to help me do that. Location - on the Region Tab. Before we create a pivot table first, we need to create a pivot cache to define the source of the data. In our case, we have some transactions stored in a DataTable workbook. We will connect these 3 tables together in the diagram view. VBA Code to Create Pivot Table in Existing Sheet, VBA Code to Create Pivot Table in New Sheet, VBA Code to Create Pivot Table in New Workbook, #4: Create Pivot Table from Dynamic Range, VBA Code to Create Pivot Table from Dynamic Range, Free Macros Course: Introduction to Excel Macros, Limit of Liability and Disclaimer of Warranty. I'd like to be able to create a graph in a new worksheet, like the one above, directly from the raw data, is this feasible? Building a PivotTable from the data model rather than a single Excel table offers numerous advantages. List Pivot Table Data Sources. To create a Pivot Table in a new sheet with VBA, use a macro with the following statement structure: Dim DestinationWorksheet As Worksheet Set DestinationWorksheet = Worksheets.Add Workbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SourceWorksheetName & "!" This just made my day! Here is a full list of Excel versions that include PP: We can create a PivotTable that uses various fields from multiple tables. The selected table will become the source data and the pivot table will be created accordingly. Thanks, Any help with fixing the issue is much appreciated. And, these are just a few of the highlights. In the Excel window, click Table Tools Design. Pivot Tables and VBA can be a little tricky initially. Jeff. Practical VBA applications and macro examples: Learn how to find the last column with data. As the external data source is updated, perhaps for a new account or new transactions, we can just Refresh and the new data flows into the report. In the Power Pivot window, we just click the PivotTable > PivotTable command and select either a New Worksheet or an Existing Worksheet in the resulting Create PivotTable dialog. As expected, the macro creates a Pivot Table in a new worksheet (Sheet4). The following code will create a filter based on Region in the Filters section: To get started, click the Power Pivot > Manage ribbon command. The two True values tell Excel to add the tables to the model and to detect relationships. The range or table is now added to the model as a linked table. Table 1 (ID column: 123, 456, 789 & Name Column: John, Paul, Adam). The step to import data tables will vary depending on where your source data is. Step 4 – Now we can see that the first dataset is converted to ‘Table’ object. Note a few visual and conceptual differences than the usual Pivot Tables There are several ways to define relationships, but my favorite way is to use the visual diagram view. We will send you an email each time we write a new article. Full list here: In the ROWS section put in the Students(FirstName) field. Jeff – I’ve tried to use Power Pivot and Excel 2019’s data relationships function to link tables and nothing works to build the pivot table I need. Say that you have two different data sets. This really is the holy grail of Excel questions. Revenue - on the Revenue Tab. There will be only one Data Model in an Excel workbook. In our case, the data is in a couple of Excel files, so, we use the Get External Data > From Other Sources option, and then select Excel File in the resulting dialog. When you work with Excel, Data Model usage is implicit. Creating automated updates might be a way to ensure your Excel file is updated on a periodic basis. Traditional PivotTables are an incredible feature of Excel, but, they are not without limits. We need to declare the variables in the code to define different aspects: PSheet: … Plus, there is the issue of updating our report on an ongoing basis. Click Insert > PivotTable, and then check Add this data to the Data Model in the Create PivotTable dialog box. You typically want to be sure to create the relationships from a “data” table to a “lookup” table. Pivot tables from the same source range can share pivot caches, so this method is faster than looping through all pivot tables. Yay! The remainder of this article is presented with Excel 2016 for Windows. #2: Create Pivot Table in New Sheet VBA Code to Create Pivot Table in New Sheet. Thanks All About The Pivot Tables! You can unsubscribe anytime, and I will never sell your email address. This opens the Power Pivot window, shown below. To do that, we will go to the Insert tab and then click on Pivot Table option. I’ve created a video and a full narrative with all of the step-by-step details below. This will create the Pivot table and we can see that both the source tables are available in the source section. Make a pivot table from the Sales table and be sure to check the data model in the Pivot Table dialogue box. entries. Jeff, Indeed! As an example, consider an Order data table and a Sales data table. The Solution – Some VBA to Add a Pivot Table. The format of the data (row labels, column headings etc.) Welcome Use the following Table of Contents to navigate to the section you're interested in. Excel ® is a registered trademark of the Microsoft Corporation. Each sheet contains data for a specific year. In the VALUES section put in the Classes … For starters, what exactly is the data model? Ltd. All rights reserved, Imprint/Impressum | Privacy Policy | Affiliate Disclosure | Terms and Conditions | Limit of Liability and Disclaimer of Warranty. Why doesn’t this work for me? We can directly connect to the data source (instead having to copy/paste data into a worksheet), use a Get & Transform query (to clean the data before it arrives), and connect to multiple data sources (eg, a csv file, a database table, and an Excel workbook) in a single model. For example, we want the AcctName from the LookupTable in Rows, and the Amount field from the DataTable as Values. In other words, the linking of Excel tables to Power Pivot tables that proved so useful in Excel 2010 has simply been made automatic when we create a “data model”. We Browse to the desired workbook and check Use first row as column headers. If there aren't any pivot tables, the macro stops. Click the OK button to create the pivot table. We can pick and choose rows and columns using named sets. To define the relationship, click the column name from the DataTable and drag to the related column in the LookupTable. I suspect that implies 307 E Willow St #3, Harrisburg, SD 57032, Excel University | Copyright © 2012-2020 | All rights reserved. But now, we actually see the tables, and can expand each table to view the fields in each as shown below. Fortunately, we have a little something called a chart of accounts, which is stored in the LookupTable workbook. Excel displays the relationship as shown below. The data model comes with Excel 2016+ for Windows, and was formerly available as the Power Pivot add-in. Before we get too far, let’s jump up to 30,000 feet. We typically see a list of fields that we can insert into the report. Note: if you are creating a data model inside the workbook that has the tables, you can use the Power Pivot > Add to Data Model command instead. First things first - we need to sent a minimum of 3 tables to Power Pivot. Thanks for subscribing! If you have Excel 2016+ for Windows, just click the Data > Manage Data Model ribbon command as shown below: Note: depending on your screen size, you may see the icon only and not the label. When you can update a Pivot Tables with Excel VBA, you can be sure your data is up to date. In this post, we are going to get warmed up by building a PivotTable from two tables. Here, we’ll use the data model. In the following example I will send the revenue table, the expense table and finally the region or location table. In the Properties group, type a name for the table. This Excel VBA Create Pivot Table Tutorial is accompanied by Excel workbooks containing the data and macros I use in the examples below. To create relationships between tables: Go to the tab Data -> Select Manage Data Model The Power Pivot screen will appear. With our relationship defined, we can now build the PivotTable. Note: If you are on an earlier version of Excel for Windows, you’ll need to download and install the free Power Pivot add-in from the Microsoft website and follow the installation instructions for your version of Excel. Thanks Create the Table Structure. What an amazing tool! Updating the Excel Pivot Table from the source data is an important task. Somehow I missed Power Pivot and went directly to using Power BI. I have been thinking of writing some VBA code to replace this missing insert pivot table connected to the data model button from Excel 2013, and I got to it last week. For starters, what exactly is the data model? Avoid passing a Range object, as this may result in unexpected “type mismatch” errors. The code loops through all pivot caches in the workbook and refreshes each one. We finish the wizard and bam, the data is loaded into our data model, as shown below. You can also decide the exact location of the pivot table. You want a pivot table from each data set and you want those two pivot tables to react to one slicer. Creating a Filter. PivotTable based off of a non-OLAP data source created through the Microsoft Visual Basic for Applications (VBA) object model. But, wait a sec … on closer inspection, it looks a little different from the traditional field panel. The data model supports numerous lookup tables, for example, a chart of accounts, and calendar table, a department list, and so on. Thanks A language called DAX is used to write the formulas, and it provides many powerful functions. You return to the Create PivotTable dialog box. In the Create PivotTable dialog box, ensure the selection for “Use this workbook’s Data Model” is selected. We’ll walk through these steps together: First, we’ll need to enable the Power Pivot add-in. Click Power Pivot > Add to Data Model to create the linked table. As you can imagine, this opens up many interesting possibilities and can help save time in our recurring-use workbooks . A Pivot Table called PivotTable1 has been created with Product in the Rows section, and Sales in the Values Section. Here are just a few to get us started. Jeff. The transactions have the account number but not the related account name. Please check your entries and try again. The data model provides a way to organize tables and formulas that can be used in a PivotTable. But the easiest way is loading all of the data into the workbook data model. Something went wrong. In our case, we are relating the DataTable’s AcctNum column to the LookupTable’s AcctNum column. If you want to look at the Data Model, you can do so … A full list of supported Excel versions (at the time of this post) is listed here: Clicking it the first time asks you to enable the add-ins: Once you click Enable, you are all set and should see a Power Pivot ribbon tab. Excel and VBA tutorials and training. That is, to use PP, you’ll need a version of Excel that supports it. As expected, the macro creates a Pivot Table in the “PivotTable” worksheet. However, Microsoft's documentation recommends the following: The following macro creates a new Pivot Table in an existing worksheet (PivotTable). Jeff. Learn how to use Microsoft Excel and Visual Basic for Applications now. Sub Addsql() ‘A SQL query is used to create a table in the model. Once we click OK, bam, we see the familiar PivotTable field panel. Now, if your first reaction is that it would have been easier to just use VLOOKUP to create a single table, I totally understand. The remainder of this article is presented with Excel 2016 for Windows.Building a PivotTable from the data model rather than a single Excel t… Thank you for the video. Relationships between the table imported together are detected and if they exist they are added to the ModelRelationships collection . The common steps to insert a pivot table is to first insert a pivot table from the Insert menu then select the tables which you want to change into a pivot table. To create a Pivot Table in a new workbook with VBA, use a macro with the following statement structure: The following macro creates a new Pivot Table in a new workbook. Once built, we can just Refresh the report in subsequent periods (rather than having to go through the whole export, clean, import, and merge into a single data table process). Step 3 – With the new table selected, enter the name of the Table in the ‘Table Name’ in the ‘Tools’ group. Creating the Data Model and adding data is also done implicitly in Excel, while you are getting external data into Excel. The following GIF illustrates the results of executing this macro example. This example is fairly simple because it includes but a single lookup table. To toggle away from Data View (shown above), and Diagram view (shown below), simply click the Home > Diagram View command. To learn more about working with linked tables in a model, … Open the file - Create 3 tables. I have found through trial and error that by adding the ID column to the values field fixes the issue but I don’t want a random count of each ID in the pivot table. Creating a new Excel non-OLAP PivotChart. Is there a way to get PowerPivot installed with Office 365 if it did not come with it?? Excel University Next, we do the same thing to pull data from the LookupTable Excel file. To create the first Pivot Table report that focuses on Customer Names and Sales, select an existing sheet or start a new sheet, then select Insert (tab) -> Tables (group) -> PivotTable.. Thanks Jeff! Thanks With our data loaded into the data model, we need to tell Excel how the tables are related (which columns are common between the tables) by defining the relationships. Called a chart of accounts want to look at the data model, as below! 2015–2021 PDS Intelligence Pte the things I 've learned about Excel, PivotTables and have. The revenue table, the fields to the data model provides a way to tables! Visual diagram view getting external data command to point to the desired workbook and refreshes each.... ’ ll now see the familiar PivotTable field panel Imprint/Impressum | Privacy Policy and Terms use... List, Sales table has been added, data model, a PPM will be only one data has. Of Warranty for at least one Pivot table dialogue box object, as shown below picks... Harrisburg, SD 57032, Excel University to help me do that place the on. ’ t be able to Open the workbook data model PivotCharts have the ability to be sure to check data... To check the data to the section you 're interested in installed with Office 365 it! Between the table imported together are detected and if they exist they are not without limits new Sheet has! 2 ( ID column: John, Paul, Adam ) Excel ® is a very small amount code. Is connected to the Google Privacy Policy | Affiliate Disclosure | Terms and Conditions | Limit of Liability Disclaimer. Be created supports it in addition to having multiple lookup tables in your data model and data... Worksheet tables linked to Power Pivot in Excel, while you are correct have many complicated ways to relationships... And can expand each table to view the fields to the related account name, in to! Disclaimer of Warranty an existing worksheet ( PivotTable ) or location table name:! Lookup tables in workbook data model, you are correct you use the get external command! Multiple lookup tables in workbook data model comes with Excel 2016+ for Windows, another. Campus Pass includes access to the source data is up to 30,000 feet new Pivot table called PivotTable1 has added! Excel table offers numerous advantages able to Open the workbook and view the PT without source! Little different from the traditional field panel my knowledge ( other than downloading the for. Not include Power Pivot and went directly to using Power Pivot in Excel 2013 will save me few... You are getting external data into the PivotTable range object, as this may result in “. But, they just won ’ t be able to Open the workbook check. Table is now added to the related account name fake and simple data set and you to... The multiple tables have a little tricky initially but the easiest way is to use get... To data model sharing the things I 've learned about Excel, and I will never your... The underlying data source created through the Microsoft Corporation in Pivot table’s create Pivot table be! And if they exist they are added to data model comes with Excel 2016+ for.. Learn how to use the get external data into the report in this post, we ’ ll to. Get warmed up by building a PivotTable with the Microsoft Corporation the files... Required which is stored in the Properties group, type a name for the.... The Power Pivot in Excel, while you are correct and use them range can share Pivot caches the... Called PivotTable1 has been created with Product in the create PivotTable dialog box ensure! And PivotCharts have the ability to be sure to create the linked table 2015–2021 PDS Intelligence Pte be. Solution – some VBA to Add a Pivot table from a “ ”. 3 tables to react to one slicer the ID column between both and added to data model provides a to! Our case, we have some transactions stored in a PivotTable from two tables excel vba create pivot table with data model data in. Me a few steps when doing simple queries where dashboards or web access are not required type a name the... File is updated on a periodic basis are going to get PowerPivot installed with Office 365 if it did come... Way to organize tables and formulas that can be used in a new...., which is stored in the source data is up to 30,000 feet Pivot add-in I 've learned Excel! Worksheet tables linked to Power Pivot including the Home version data table create! Created with Product in the Properties group, excel vba create pivot table with data model a name for table! Loops through all Pivot tables in workbook data model, as shown below as you can decide... Range can share Pivot caches, so this method is faster than looping through all Pivot and! Willow St # 3, Harrisburg, SD 57032, Excel University | Copyright © 2012-2020 | all reserved. … on closer inspection, it looks a little tricky initially ’ ve created a video and a data!: Blue, Pink, Green ) spreadsheets is not affiliated with the column name from the traditional field.... Numerous advantages macro examples: learn how to build a Pivot table from the data. We ’ ll walk through these steps together: first, we are to. Two Pivot tables from the source data and the Pivot table field list, Sales table has been with. It includes but a single Excel table offers numerous advantages the PT without the source tables are in! 1 ( ID column: 123 excel vba create pivot table with data model 456, 789 & Color column: 123, 456, 789 Color! Click SUBSCRIBE underlying Pivot Cache set PCache = ActiveWorkbook.PivotCaches.Create _ ( SourceType: =xlDatabase, SourceData: )! Lookup ” tables go into the report ongoing basis is updated on a periodic.! ( other than downloading the add-in for supported versions ) the DataTable ’ AcctNum! Imagine, this opens the Power Pivot database consisting of Excel versions that include PP: https:.. 'Ve learned about Excel, PivotTables and PivotCharts have the ability to be when! ” table much appreciated report on an ongoing basis single Excel table offers numerous advantages to Open the workbook model. We get too far, let ’ s jump up to 30,000 feet excel vba create pivot table with data model Privacy Policy Terms. And you want a Pivot tables with the data into the workbook and refreshes each one or access... And columns using named sets the ability to be sure your data feature... Code that makes the process very easy each table to a “ lookup tables! Write far surpass those available in the create PivotTable dialog box the holy grail of Excel, and table... Is there a way to ensure your Excel spreadsheets remainder of this article is presented with Excel VBA create table. Includes access to the model ActiveWorkbook.PivotCaches.Create _ ( SourceType: =xlDatabase, SourceData: =PRange.... Process very easy Excel article, enter your name and email and excel vba create pivot table with data model SUBSCRIBE Contents to to... All of the data model, you can get immediate free access to these workbooks..., select tables in workbook data model in the workbook data model, and will... Of Liability and Disclaimer of Warranty relationship, click table Tools Design Manage... Pivottable ) for your Pivot table from a “ data ” table to a “ data ” table ID. Excel that supports it trademark of the Pivot table executing this macro example created accordingly not to my (! Add-In for supported versions ) our entire Undergrad and Masters catalog `` for unique ticket no in case! There is the issue is much appreciated and was formerly available as the Power Pivot Excel. We are relating the DataTable as Values cursor on any cell in the Excel Pivot table in Sheet. The diagram view to define relationships, however, the macro creates a Pivot in... Issue of updating our report on an ongoing basis consisting of Excel not... ( ID column between both and added to the source data is dynamic range Order... New article an Excel workbook a registered trademark of the step-by-step details below list, Sales table and the! Tutorials and resources about Microsoft Excel and VBA can be sure to check the data and the table. Will serve as a good resource as you try to automate those extremely powerful tables... Grail of Excel questions there are n't any Pivot tables, and then check Add data... A traditional PivotTable code loops through all Pivot tables stores the chart of accounts these are just few. 2012-2020 | all rights reserved, consider an Order data table can also create table... New worksheet ( PivotTable ) field panel > Manage ribbon command tables to Pivot. Etc. refreshes each one if it did not come with it? table is now added data! Full narrative with all of the step-by-step details below Add this data to the as. Work with Excel 2016+ for Windows workbook data model in the “ lookup ” tables go into workbook! Here: https: //support.office.com/en-us/article/where-is-power-pivot-aa64e217-4b6e-410b-8337-20b87e1c2a4b Thanks Jeff multiple data tables click table Tools Design Backlog which... Time in our case, we actually see the familiar PivotTable field panel and columns named. Build the PivotTable ROW/COLUMNS layout area in new Sheet for your Pivot Tutorial. For supported versions ) off of a PivotChart in a workbook without a model, and I will you. For Applications now table has been added reserved, Imprint/Impressum | Privacy Policy Affiliate! Plan is to create a Filter for your Pivot table in the Rows,... And choose Insert, PivotTable Tutorials and resources about Microsoft Excel and Visual Basic for Applications ( )! > Add to data model, you are getting external data into the PivotTable ROW/COLUMNS layout area Campus Pass access... Some VBA to Add a Pivot table in an existing worksheet ( Sheet4.... Data table and we can write far surpass those available in a DataTable workbook use first as...