For TargetColumn = LBound(ColumnAssignments) To _
UBound(ColumnAssignments)
SourceColumn = TargetColumn - LBound(ColumnAssignments) + 1
If ColumnAssignments(TargetColumn) >= 1 And _
ColumnAssignments(TargetColumn) <= _
Table.ListColumns.Count Then
InsertRange.Columns(ColumnAssignments(TargetColumn)) _
.Value = Application.Index(Values, , SourceColumn)
End If
Next TargetColumn
End If
Set BulkInsertIntoTable = InsertRange
' Restore the total row setting
Table.ShowTotals = ShowTotals
Application.Calculation = Calculation
Application.ScreenUpdating = ScreenUpdating
End Function
Repairing Formatting and Formulas
Normally, a Table maintains the same formatting and formulas across all rows, excluding the header and total rows. When the formatting or formula is changed in a cell in a Table's column, Excel applies that new formatting or formula to the entire column. The formatting and formulas are automatically applied to new rows as they are added.
A Table's formatting or formulas can become inconsistent when you're manually editing formatting or applying different formulas in the same Table column. You can fix the Table column by reapplying the formatting to the entire column plus one additional row at the bottom. (The total row must be disabled to make this adjustment.) You can fix a formula (convert the column back to a calculated column) by applying the formula to the entire column.
The routine below accomplishes this by using the Table's Resize method. First, the Table is resized to be just one row. Next, the formatting and formulas are cleared from all Table rows from row 2 down to the last row plus one row. Finally, the Table's range is set back to what it was. This final step instructs Excel to apply the formatting and formulas in the first row to all rows below the first row. The result is a consistently formatted Table, using the first data row as the template for all other rows. The code assumes that there is at least one row of data in the Table:
Public Sub RepairTable( _
ByVal Table As ListObject _
)
' Repair the Table's formatting and formulas by making them consistent down the
' entire length of each column.
'
' Syntax
'
' RepairTable(Table)
'
' Table - A Table object (ListObject object).
Dim RowCount As Long
Dim ListColumn As ListColumn
Dim ShowTotals As Boolean
RowCount = Table.ListRows.Count
If RowCount < 2 Then Exit Sub
With Table
ShowTotals = .ShowTotals
.ShowTotals = False
.Resize .HeaderRowRange.Resize(2)
For Each ListColumn In .ListColumns
With ListColumn.DataBodyRange.Resize( _
Application.Max(RowCount, 1)).Offset(1)
If Left(.Rows(1).Formula, 1) = "=" Then
.Cells.Clear
Else
.Cells.ClearFormats
End If
End With
Next ListColumn
.Resize .HeaderRowRange.Resize(1 + RowCount)
.ShowTotals = ShowTotals
End With
End Sub
Copying a Table Style to a New Workbook
There is no easy way to copy a Table style from one workbook to another. The following sample code copies the Table style assigned to the Table named "tblRegister" to the workbook "Destination Workbook.xlsx":
Sub ExportTableStyle()
Dim Source As Workbook
Dim Target As Workbook
Dim Table As ListObject
Set Source = ThisWorkbook
Set Target = Workbooks("Destination Workbook.xlsx")
Set Table = Source.Worksheets("Register").ListObjects("tblRegister")
Target.Worksheets.Add Before:=Target.Worksheets(1)
Table.Range.Copy Target.Worksheets(1).Range("A1")
Target.Worksheets(1).Delete
End Sub
Retrieving an Access Crosstab Query
In Excel there is no native way to return a crosstab query from Access. In essence, a crosstab query is a type of SELECT query that pivots specified fields across columns and aggregates in the intersection of row and column fields. You can think of it as a PivotTable in an Access query.
In the following example, GetCrosstabQueryFromAccess uses an ADO (ActiveX Data Objects) method to create a crosstab query from a table that resides in a known database table. These three routines utilize this same example code to return the query into a Table:
"TestCrosstabQuery1"—Returns a crosstab query into a new worksheet
"TestCrosstabQuery2"—Returns a crosstab query into an existing Table and overwrites header values
"TestCrosstabQuery3"—Returns a crosstab query into an existing Table but does not overwrite header values
All the routines in this example utilize a constant that expresses the SQL statement needed for the crosstab query.
NOTE
This example uses a reference to “Microsoft ActiveX Data Objects 6.1 library”. To set this, you select Tools | References in the Visual Basic Editor, enable this reference, and click OK.
Const TestSQL As String = “TRANSFORM Sum(tblSampleData.Total) AS SumTotal “ & _
“SELECT tblSampleData.Region “ & _
“FROM tblSampleData “ & _
“GROUP BY tblSampleData.Region “ & _
“PIVOT Format([Date],””mmm””) In “ & _
(""Jan"",""Feb"",""Mar"", ""Apr"",""May"",""Jun"", ""Jul"",""Aug"",""Sep"", ""Oct"",""Nov"",""Dec"");"
Sub TestCrosstabQuery1()
'Bring crosstab query into a new table created on a new worksheet
Call GetCrosstabQueryFromAccess(TestSQL)
End Sub
Sub TestCrosstabQuery2()
'Bring crosstab query into an existing table
'Overwrite headers with field names
Call GetCrosstabQueryFromAccess(TestSQL, True, ThisWorkbook.Worksheets("Query").ListObjects("qryCrosstab"))
End Sub
Sub TestCrosstabQuery3()
'Bring crosstab query into an existing table
'Do not overwrite headers
Call GetCrosstabQueryFromAccess(TestSQL, False, ThisWorkbook.Worksheets("Query").ListObjects("qryCrosstab"))
End Sub
Sub GetCrosstabQueryFromAccess( _
ByVal SQL As String, _
Optional ByVal OverwriteHeaders As Boolean, _
Optional ByVal Table As ListObject _
)
Dim DataConnection As ADODB.Connection
Dim DataRecordset As ADODB.Recordset
Dim DestinationSheet As Worksheet
Dim DestinationTable As ListObject
Dim DestinationRange As Range
Dim ShowTotalRow As Boolean
Dim ShowHeaderRow As Boolean
Dim ColumnHeader As Long
'Make sure database is found
If Dir(ThisWorkbook.Path & "SampleDatabase.accdb", vbNormal) = vbNullString Then
MsgBox "Database not found", vbExclamation, "Whoops!"
Exit Sub
End If
'Create and open connection
Set DataConnection = New Connection
Set DataRecordset = New Recordset
DataConnection.CursorLocation = adUseClient
DataConnection.ConnectionString =
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
ThisWorkbook.Path & "SampleDatabase.accdb"
DataConnection.Open
'Get the recordset via query
Set DataRecordset = DataConnection.Execute(SQL)
'Set destination table
If Table Is Nothing Then
'Use a new table created on a new worksheet in the active workbook
Set DestinationSheet = ActiveWorkbook.Worksheets.Add(After:=ActiveSheet)
Set DestinationRange = DestinationSheet.Range("A1").Resize(DataRecordset.RecordCount + 1, _
DataRecordset.Fields.Count)
DestinationSheet.Activate
DestinationRange.Select
Set DestinationTable = DestinationSheet.ListObjects.Add( _
SourceType:=xlSrcRange, _
XlListObjectHasHeaders:=xlYes, _
Destination:=DestinationRange _
)
Else
'Use existing table passed to routine, which assumes the Table columns
'already match the query field results
If Not DestinationTable.DataBodyRange Is Nothing Then DestinationTable.DataBodyRange.Delete
DestinationTable.InsertRowRange.Insert
End If
'Save header/total row status
ShowHeaderRow = DestinationTable.ShowHeaders
ShowTotalRow = DestinationTable.ShowTotals
DestinationTable.ShowTotals = False
DestinationTable.ShowHeaders = True
'Copy recordset to table
DestinationTable.DataBodyRange(1, 1).CopyFromRecordset DataRecordset
'Get column headers
If OverwriteHeaders Or Table Is Nothing Then
For ColumnHeader = 0 To DataRecordset.Fields.Count - 1
DestinationTable.HeaderRowRange(1, ColumnHeader + 1) = DataRecordset.Fields(ColumnHeader).Name
Next ColumnHeader
End If
'Close recordset from database
DataRecordset.Close
'Set table header/total rows how they were originally
DestinationTable.ShowTotals = ShowTotalRow
DestinationTable.ShowHeaders = ShowHeaderRow
End Sub
Automating Tables with .NET
This section is for experienced .NET developers. It assumes that you are able to create and compile a .NET Visual Basic or C# project.
Accessing the ListObject with a .NET solution is very similar to doing so in VBA. This section provides examples in both Visual Basic and C# using the .NET framework to check whether a Table exists. Each solution is built as an Excel COM add-in, although the logic could be applied to many other situations and projects. These projects are available for download at www.exceltables.com.
Each example below has two code parts: the TableExists function and a custom ribbon button click event handler. Both routines reside in a Ribbon (Visual Designer) class called MyRibbon. The button name on the ribbon is "button1". The code presents the user with an input box to enter a Table name and then presents a message box with the result True if the Table exists in the active workbook or False if it does not exist. The code does very little error handling.
NOTE
The code examples below were created in Visual Studio 2012 with Visual Studio Tools for Office (VSTO).
The following reference must be included to reference the Excel Object Model in the project:
Microsoft.Office.Interop.Excel
This reference is created when the project is specified as an Excel add-in. The following steps detail how to set up a new project for the code examples below:
1. Create a new project in Visual Studio. Select Templates | Language | Office/SharePoint | Office Add-ins | Excel 2013 Add-in.
2. Add a new ribbon item to the project. Select PROJECT | Add New Item, select Ribbon (Visual Designer) and change the name to "MyRibbon", and then click Add.
3. Set the ribbon to "Custom". In the Properties pane, expand the ControlId group and change the ControlIdType from "Office" to "Custom".
4. Add a ribbon button. From the Toolbox, drag a ribbon button onto the ribbon group.
5. Open the "MyRibbon" class. Right-click MyRibbon in the Solution Explorer and select View Code.
6. Copy the appropriate sample code from below. Copy the code below for the desired language into the solution's MyRibbon class. (In C# projects, the default name is "MyRibbon.cs", and in Visual Basic projects, the default name is "MyRibbon.vb".)
TIP
You expose the Excel application with the property Globals.ThisAddin.Application.
Finding Whether the Table Exists by Using C#
The following code is contained in the ribbon class MyRibbon, in a project named "TableExample_C":
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Tools.Ribbon;
using Excel = Microsoft.Office.Interop.Excel;
namespace TableExample_C
{
public partial class MyRibbon
{
private void button1_Click_1(object sender, RibbonControlEventArgs e)
{
// Set variables
var xlApp = Globals.ThisAddIn.Application;
var xlBook = xlApp.ActiveWorkbook;
const string Prompt = "Enter a Table name to check if it exists in the active workbook:";
// Get table name
var CheckName = xlApp.InputBox(Prompt, "Table Exists");
// Run TableExists function, notify user
bool bExists;
bExists = TableExists(CheckName, xlBook);
MessageBox.Show(bExists.ToString(), "Result", MessageBoxButtons.OK);
}
public static bool TableExists(string TableName, Microsoft.Office.Interop.Excel.Workbook WKB)
{
try
{
foreach (Excel.Worksheet WKS in WKB.Worksheets)
{
// Loop through all Tables in the iterated worksheet
foreach (Excel.ListObject LO in WKS.ListObjects)
{
if (LO.DisplayName == TableName) return true;
}
}
// In case no Table names match
return false;
}
catch
{
// If an error occurred, default to false
return false;
}
}
}
}
Finding Whether the Table Exists by Using VB
The following code is contained in the ribbon class MyRibbon, in a project named "TableExample_VB":
Imports System.Windows.Forms
Imports Microsoft.Office.Tools.Ribbon
Imports Excel = Microsoft.Office.Interop.Excel
Public Class MyRibbon
Private Sub Button1_Click(sender As Object, e As RibbonControlEventArgs) Handles Button1.Click
Try
'Set variables
Dim xlApp As Excel._Application = Globals.ThisAddIn.Application
Dim xlBook As Excel.Workbook = xlApp.ActiveWorkbook
Const Prompt As String = "Enter a Table name to check if it exists in the active workbook:"
'Get table name
Dim CheckNa
me As String = xlApp.InputBox(Prompt, "Table Exists")
'Run TableExists function, notify user
Dim bExists As Boolean = TableExists(CheckName, xlBook)
MessageBox.Show(bExists.ToString(), "Result", MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch ex As Exception
MessageBox.Show("An error occurred.", "Whoops!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
End Try
End Sub
Public Function TableExists(ByVal TableName As String, WKB As Excel.Workbook) As Boolean
Try
'Loop through all worksheets in the specified workbook
For Each xlSheet As Excel.Worksheet In WKB.Worksheets
'Loop through all Tables in the iterated worksheet
For Each xlTable As Excel.ListObject In xlSheet.ListObjects
If xlTable.DisplayName = TableName Then Return True
Next
Next
'In case no Table names match
Return False
Catch ex As Exception
'If an error occurred, default to false
Return False
End Try
End Function
End Class
10 Tables in Excel Online
The first version of Office Online, released in summer 2007, was called Office Web Apps. Office Online is a browser-based limited version of the Office suite of applications. Office Online provides the basic functionality of Office, and you can access it on any device, with any operating system, as long as you have an Internet connection and use a compliant browser.
The Office Online application is hosted on Microsoft servers. The advantage of using it is that you need nothing but a browser; you don’t have to install an application, and users do not have to worry about updating the application or applying hotfixes. Similar to Google Spreadsheets and Google Docs, Office Online is ideal for students and those not wanting to pay a fee for the desktop version of Office.
Office Online is compatible with most major browsers:
Internet Explorer (7 or later)
Firefox
Chrome
Safari TIP
We recommend using the latest available version of your preferred browser. Office Online makes use of many of the latest features implemented in browsers, so it’s important that you use the latest browser version available and keep it up-to-date to make the most of your Office Online experience.