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

sample code to find unsupported character conversions

/*
    http://en.wikipedia.org/wiki/Code_page_1252
    http://en.wikipedia.org/wiki/EBCDIC_037
  
    http://stackoverflow.com/questions/13389182/in-net-how-do-i-determine-what-characters-existing-in-the-windows-1252-codepag
 
 
    Notes:
    (1) There is a problem in the code.  The .Net conversion from Windows 1252 to IBM 37 has problem.  It converted the of Ÿ (159 in windows 12562) to "Y" (232 IBM 37) instead of "Ÿ" (255 IBM 37).
    (2) The Console cannot display all the characters on the terminal.
 */

using System;
using System.Collections.Generic;
using System.IO;
using System.Text;

namespace test_code_page
{
    class find_unsupported_conversions
    {
        static void Main(string[] args)
        {
            FileStream stream = null;
            StreamWriter streamwriter = null;
            TextWriter console_streamwriter = Console.Out;
            string path;

            if (args.Length == 1)
                path = args[0];
            else
            {
                Console.Write("Enter path: ");
                path = Console.ReadLine();
            }

            try
            {
                stream = new FileStream(path, FileMode.CreateNew, FileAccess.Write);
                streamwriter = new StreamWriter(stream);
                Console.SetOut(streamwriter);
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot open '" + path + "' for writing");
                Console.WriteLine(e.Message);
            }

            test_code_pages();

            Console.SetOut(console_streamwriter);
            if (streamwriter != null)
                streamwriter.Close();
            if (stream != null)
                stream.Close();
        }

        private static void test_code_pages()
        {
            Encoding source_encoding = Encoding.GetEncoding("Windows-1252");
            Encoding target_encoding = Encoding.GetEncoding("IBM037");
            
            byte source_question_mark = source_encoding.GetBytes("?")[0];
            byte target_question_mark = target_encoding.GetBytes("?")[0];

            int count = 0;
            byte[] b = new byte[1];
            for (int i = 0; i < 256; i++)
            {
                b[0] = (byte)i;
                if (b[0] != source_question_mark && Encoding.Convert(source_encoding, target_encoding, b)[0] == target_question_mark)
                {
                    count++;
                    Console.WriteLine(source_encoding.GetString(b) + " " + i.ToString());
                }
            }

            Console.WriteLine(count.ToString() + " unsupported.");
        }
    }
}