Programmatically add a query table

Sub Add_Dummy_Query_Table()
Dim cnn_str As String

cnn_str = “ODBC;DSN=Excel Files;DBQ=” & ThisWorkbook.FullName & “;DriverId=790;MaxBufferSize=2048;PageTimeout=5;”

With ActiveSheet.QueryTables.Add(Connection:=cnn_str, Destination:=Range(“A1″), Sql:=”SELECT ‘ABC’ AS [dummy]”)
.Name = “dummy”
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub


Fix text-formatted numbers

Beside the suggestion from the following Microsoft page, re-assigning the value with Macro will also resolve.

Selection.Value = Selection.Value

Importing data from Excel is not easy. If the formatting of a column changed (the first 100 cells is number and the second 100 cells is text), the SSIS may convert the second 100 cells to NULL values. Cleaning the formats must be done before the import.

Refresh Excel query tables


Sub RefreshSpreadsheet(filename)
'  This procedure will refresh all the query
'  tables from the active Excel sheet.
    Dim oExcel
    Dim oQueryTable
    Set oExcel = CreateObject("excel.application")
    For Each oQueryTable In oExcel.ActiveSheet.QueryTables
        oQueryTable.Refresh False
    Set oExcel = Nothing
End Sub

Execute Excel macro

VB Script

Sub RunMacro(filename, macroname)
'  This procedure will execute a Excel macro.
    Dim oExcel
    Set oExcel = CreateObject("excel.application")
    oExcel.Run macroname
    Set oExcel = Nothing
End Sub

Sticky Spaces

The Sticky Space is the non-breakable space CHAR(160).

In word processing, like MS Word, by applying the sticky spaces between words can avoid wrapping a phrase or a name into two lines. The sticky space looks like a regular space. The MS Word will handle the phrase connected by them as a single word. To put in the sticky space, just press the Ctrl+Shift+Space.

But, it may be a problem during ETL.

HTML uses non-breaking spaces CHAR(160) for multiple spaces. Copying string contained CHAR(160) from web page, or from HTML email, to Excel spreadsheet may cause problem. For example, the VBA Trim function will not remove the non-breaking spaces.

Beware of the 194, 160 pattern.

Excel VBA: Value, Value2, and NumberFormat

The default property of an Excel Cell is the Value property.  It returns the formatted value of the Excel Cell.  The underlying value is stored in the Value2property.  The format is stored in NumberFormat property.  If the underlying value and the format do not get along, the Value property will return an overflow error.


For example, the Excel Cell A1 is formatted as date m/d/yyyy, and the users enter a string “20070101”.  Since the date values are stored as Double in Excel, the system will display the string “##########” at Cell A1 to mean invalid date.  If the VBA code accesses the Value property, the code will return the overflow error.  But, the value “20070101” can still be retrieved from the Value2 property.


The Excel formatting algorithm will try to distinguish the date value from other values.  If the users enter “6 months”, the system will understand it is a string. Both Value property and Value2 property will return the string “6 months”.

Excel VBA: Default Property Of A Class

Bullen’s book has better example.

Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA®

By: Stephen Bullen; Rob Bovey; John Green

Publisher: Addison-Wesley Professional


Add the following line of code on a blank line immediately following the Property Get statement.

Attribute Value.VB_UserMemId = 0

Now, your Property Get  procedure will look something like the following.  The line in bold is what you added.

Property Get Value() As Long
Attribute Value.VB_UserMemId = 0
    Value = pValue
End Property


  MultiUse = -1  'True
Attribute VB_Name = "MyCollection"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Option Explicit

Private m_collection As Collection

Property Get Item(ByVal id As Variant) As Variant
Attribute Item.VB_UserMemId = 0
    Set Item = m_collection(id)
End Property

Public Function NewEnum() As IUnknown
Attribute NewEnum.VB_UserMemId = -4
    Set NewEnum = m_collection.[_NewEnum]
End Function

Public Sub Add(Item As Variant, Optional key As Variant, Optional Before As Variant, Optional After As Variant)
    Call m_collection.Add(Item, key, Before, After)
End Sub

Public Property Get Count() As Long
    Count = m_collection.Count
End Property

Public Sub Remove(Index As Variant)
    Call m_collection.Remove(Index)
End Sub

Private Sub Class_Initialize()
    Set m_collection = New Collection
End Sub

Private Sub Class_Terminate()
    Set m_collection = Nothing
End Sub