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

http://office.microsoft.com/en-us/excel-help/fix-text-formatted-numbers-by-applying-a-number-format-HA102749043.aspx

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.

Determine Monday

In VB, the (Saturday Mod 7) = 0.

' VBA Example: Determine Monday
Public Function Monday(a_dt As Date) As Date
    Dim t As Long
    
    t = Int(a_dt)
    Monday = t - ((t + 6) Mod 7) + 1
End Function

 

' VBA Example: Determine Monday
' version 2
Public Function Monday(a_dt As Date) As Date
    Dim t As Long
    
    t = Int(a_dt) - 1
    Monday = t - (t Mod 7) + 2
End Function

 

' VBScript: determine the first monday of the month of a given date
Public Function First_Monday(a_dt) 
    Dim t

    t = Int(a_dt) - Day(a_dt) + 7
    First_Monday =  t - ((t + 6) Mod 7) + 1
End Function

Refresh Excel query tables

VBScript

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")
    
    oExcel.Workbooks.Open(filename)
    
    For Each oQueryTable In oExcel.ActiveSheet.QueryTables
        oQueryTable.Refresh False
    Next    
    
    oExcel.ActiveWorkbook.Save
    oExcel.Workbooks.Close
    oExcel.Quit
    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.Workbooks.Open(filename)
    
    oExcel.Run macroname
    
    oExcel.ActiveWorkbook.Save
    oExcel.Workbooks.Close
    oExcel.Quit
    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.
https://stackoverflow.com/questions/1431034/can-anyone-tell-me-what-this-ascii-character-is