Putting Tables To Work In Excel 2010

I was very lucky to be able to develop a major Excel 2007    “Application” shortly after it shipped.  In that file I used the new Structured References that are designed to work with the enhanced Excel “Tables” from that version.  I was very pleased with the functionality it provided for a “professional” approach to data management in Excel.

I am currently once again doing an Excel application in which I am taking advantage of this feature and I thought I might throw you some VBA code from that Workbook that indicates how I am using it in the application.

Please be advised that there may well be many other ways of doing what I am doing (including using Dynamic Named Ranges and External Data Connections, etc. and even “prettier” code),  but I have found that this capability is doing the job for me in a way that is reliable, efficient and cool (sorry – had to say that).  Of course a procedure like this needs Error trapping in a big way but that’s not what I’m trying to show here.

The features of this procedure are very straightforward:

1. I turn off a few Application Properties like Recalc, ScreenUpdating and DisplayAlerts.

2. I open the file with the data being imported from a named range on the Workbook called “RequirementsWeeklyFile”.  I append the Default Folder to the name of the source file from a cell named “EDIDir”.  This way these sources can be changed from within the Workbook if necessary rather than by revising Code.   The reason I don’t use a Linked Table in this case is because the name is dynamic (it includes the date in the name and is driven by selections in the Workbook), and also this Workbook will be emailed to other ] users without access to the source data Folders.

3. I prep the ‘Target” Worksheet for recieving a new set of data by deleting all previous data in the Table.  Because the Table “EDIReleasesWeekly” already exists, and probably has data in it, (or not), I delete the rows inside the table before I import the new data.  Note that I put a data Table of this type on its own Worksheet and it starts in cell A1.  This Worksheet is hidden. In this example the data in the Table is used in the model but is never displayed to the user… it is simply a data source for reports in Visible Worksheets.

4. I paste the new data into the “Target” Worksheet starting in range(“A1”), pasting Values and then Formats in case there are any formulas in the source that I don’t want or need to bring over.

5. I resize the already existing to reflect the new data.  This is important because by resizing the Table it doesn’t do away with the name and then redefine it.  By “Resizing” the Table, any formulas that refer to that name will be preserved. This means that any references to the Table will reflect not only the new data but also the new size (either smaller or larger).

 Sub GetWeeklyData()

   Dim Swbk As Workbook
  
   With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .DisplayAlerts = False
        .Calculation = xlCalculationManual
    End With
   
    Set Swbk = Application.Workbooks.Open(Range(“EDIDir”).Value & Range(“RequirementsWeeklyFile”).Value, , True)
   
    ThisWorkbook.Activate
  
    With Worksheets(“EDIReleasesWeekly”)
        .ListObjects(“EDIReleasesWeekly”).ListRows(1).Delete
        Swbk.Worksheets(1).UsedRange.Cells.Copy
        .Cells(1, 1).PasteSpecial Paste:=xlValues
        .Cells(1, 1).PasteSpecial Paste:=xlFormats
        .ListObjects(“EDIReleasesWeekly”).Resize Range(.Cells1,1).CurrentRegion.Address)
    End With
   
    Application.CutCopyMode = False
   
    Swbk.Close False
   
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
        .DisplayAlerts = True
        .Calculation = xlCalculationAutomatic
    End With
   
End Sub

By using this process I am able to preserve formulas like this:

=SUMIFS(EDIReleasesWeekly[QTYRQ],EDIReleasesWeekly[PARTNO],[@[VBS Nbr]],EDIReleasesWeekly[DUNSNO],[@Plant],EDIReleasesWeekly[Week],$C4)

throughout the Workbook.  I no longer have to worry about the number of rows in the data source as the size is adjusted in the Importing code while preserving the names.  Of course it goes without saying that there will no changes in the Field names or number of fields in the source file,  but that is managed through a SQL Server automated weekly process that creates the file for me.

I will be writing more about the strength of these functions and how they can be managed in VBA in future posts.

I am a big believer in all the ways Excel can consume data. I am very convinced that by using Tables and Structured references based on Tables, Excel becomes a more professional, cost effective and more reliable member of Corporate reporting systems. 

Dick