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
Advertisements

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.");
        }
    }
}

DTS: VBScript tests file creation month at the ftp site

Option Explicit

'  This VBScript tests the file creation month at the ftp site.
'
' reference: http://www.microsoft.com/technet/scriptcenter/resources/tales/sg1002.mspx
' reference: http://www.sqlservercentral.com/articles/Administering/usingvbscripttoautomatetasks/1171/
'
Function Main()
    Dim fso
    Dim temp_folder
    Dim ftp_control_file
    Dim ftp_control_filename
    Dim shell
    Dim return
    
    
    Set fso         = CreateObject("Scripting.FileSystemObject")
    Set temp_folder = fso.GetSpecialFolder(2) ' Temporary Folder = 2
        
    ' ftp control file name
    ftp_control_filename = temp_folder.Path + "\" + fso.GetTempName()
    
    ' create ftp control file
    Set ftp_control_file = fso.CreateTextFile(ftp_control_filename, True)
    ftp_control_file.WriteLine ("open ftp.xyz.com")
    ftp_control_file.WriteLine ("my_user_name")
    ftp_control_file.WriteLine ("my_password")
    ftp_control_file.WriteLine ("ascii")
    ftp_control_file.WriteLine ("dir my_file_name.txt")
    ftp_control_file.WriteLine ("bye")
    ftp_control_file.Close
    Set ftp_control_file = Nothing
    
    ' reference: http://www.printdistributor.com/forum/post/124
    ' The WScript object isn't part of DTS VBScript.
    Set shell = CreateObject( "WScript.Shell" )
    ' reference: http://www.chebucto.ns.ca/~ak621/DOS/ExitCode.html
    ' the return variable will be the return value from the last FIND command
    return = shell.Run("%comspec% /c ftp -s:"""  & ftp_control_filename & """ | find ""my_file_name.txt"" | find "" " & MonthName(Month(Now), True) & " """, 0, True)
    Set shell = Nothing
    
    ' clean up control file
    fso.DeleteFile ftp_control_filename
    
    Set temp_folder = Nothing
    Set fso         = Nothing
    
    ' return the flag to the DTS package
    If return = 0 Then
        Main = DTSTaskExecResult_Success
    Else
        Main = DTSTaskExecResult_Failure
    End If
End Function

Using ADO to upload data

Dim cnn
Dim rst

Set cnn = CreateObject("ADODB.Connection")
cnn.ConnectionString = "Driver={SQL Native Client};Server=myserver;Database=test;Trusted_Connection=yes;"
cnn.Open
cnn.Execute "CREATE TABLE ##tmp (id INTEGER NOT NULL PRIMARY KEY)"

 

Set rst = CreateObject("ADODB.Recordset")
Set rst.ActiveConnection = cnn
rst.CursorLocation = 3
rst.CursorType = 3
rst.LockType = 3
rst.Open "##tmp", , , ,2
rst.AddNew
rst("id") = 1
rst.Update
rst.AddNew
rst("id") = 2
rst.Update

Using global temporary table is required because ADO updating process will open the second connection to the server. The CursorLocation, CursorType, and LockType must be set. ADO will generate RPC with the INSERT statement for each record.