Developer

Excel 2007 UDF/Formula for highlighted cell

Jack Huang Developer, I.T. 0 Comment

Excel does not provide facilities for sorting or filtering a range of cells by the colour of the cells, or for counting the instances of a particular colour. Here is a User Defined Functions(UDF) that will do the trick.

This UDF will return an array of colorindex values that can be used in standard worksheet functions. For exapmle: ColorIndex(A1)

To create a new UDF in Excel 2007, open Developer -> Visual Basic Editor -> Insert -> Module

'---------------------------------------------------------------------
' ColorIndex Function
'---------------------------------------------------------------------
' Function:    Returns the colorindex of the supplied range
' Synopsis:    Initially, gets a colorindex value for black and white
'              from the activeworkbook colour palette
'              Then works through each cell in  the supplied range and
'              determines the colorindex, and adds to array
'              Finishes by returning acumulated array
' Variations:  Determines cell colour (interior) or text colour (font)
'              Default is cell colour
' Constraints: Does not count colours set by conditional formatting
'---------------------------------------------------------------------
' Author:      Bob Phillips
'              Additions for ranges suggested by Harlan Grove
'---------------------------------------------------------------------


'---------------------------------------------------------------------
Function ColorIndex(rng As Range, _
                    Optional text As Boolean = False) As Variant
'---------------------------------------------------------------------
Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim iWhite As Long, iBlack As Long
Dim aryColours As Variant

    If rng.Areas.Count > 1 Then
        ColorIndex = CVErr(xlErrValue)
        Exit Function
    End If

    iWhite = WhiteColorindex(rng.Worksheet.Parent)
    iBlack = BlackColorindex(rng.Worksheet.Parent)

    If rng.Cells.Count = 1 Then
        If text Then
            aryColours = DecodeColorIndex(rng, True, iBlack)
        Else
            aryColours = DecodeColorIndex(rng, False, iWhite)
        End If

    Else
        aryColours = rng.Value
        i = 0

        For Each row In rng.Rows
            i = i + 1
            j = 0

            For Each cell In row.Cells
                j = j + 1

                If text Then
                    aryColours(i, j) = _
                      DecodeColorIndex(cell,True,iBlack)
                Else
                    aryColours(i, j) = _
                      DecodeColorIndex(cell,False,iWhite)
                End If

            Next cell

        Next row

    End If

    ColorIndex = aryColours

End Function

'---------------------------------------------------------------------
Private Function WhiteColorindex(oWB As Workbook)
'---------------------------------------------------------------------
Dim iPalette As Long
    WhiteColorindex = 0
    For iPalette = 1 To 56
        If oWB.Colors(iPalette) = &HFFFFFF Then
            WhiteColorindex = iPalette
            Exit Function
        End If
    Next iPalette
End Function

'---------------------------------------------------------------------
Private Function BlackColorindex(oWB As Workbook)
'---------------------------------------------------------------------
Dim iPalette As Long
    BlackColorindex = 0
    For iPalette = 1 To 56
        If oWB.Colors(iPalette) = &H0 Then
            BlackColorindex = iPalette
            Exit Function
        End If
    Next iPalette
End Function

'---------------------------------------------------------------------
Private Function DecodeColorIndex(rng As Range, _
                                  text As Boolean, _
                                  idx As Long)
'---------------------------------------------------------------------
Dim iColor As Long
    If text Then
        iColor = rng.font.ColorIndex
    Else
        iColor = rng.Interior.ColorIndex
    End If
    If iColor < 0 Then
        iColor = idx
    End If
    DecodeColorIndex = iColor
End Function

'---------------------------------------------------------------------
' End of ColorIndex Function
'---------------------------------------------------------------------

How To Get a List of Files from a FTP Server in SSIS

Jack Huang DBA, Developer 3 Comments

In SSIS, the FTP task provides the following operations:
Send files
Receive files
Create local directory
Create remote directory
Remove local directory
Remove remote directory
Delete local files
Delete remote files

As you can see, there is no operation to retrieve a list of files. 

The Foreach Loop Container doesn’t work for FTP, it only works on local folder.

You can use the Script task to do this.  Here is a sample SSIS package to retrieve Demo*.xls from the FTP server. Then download all files to a local drive and remove only the remote files that we’ve transferred.

The SetWorkingDirectory can be use to specify the folder on the FTP server to query for the list of files.

    Public Sub Main()
        Dim result As Integer
        Dim conMan As ConnectionManager
        Dim ftp_client As FtpClientConnection
        Dim sFolderNames() As String
        Dim sFileNames() As String
        Dim sFileName(0) As String

        conMan = Dts.Connections("MyFTP")
        ftp_client = New FtpClientConnection(conMan.AcquireConnection(Nothing))

        ftp_client.Connect()
        ftp_client.SetWorkingDirectory("incoming")
        ftp_client.GetListing(sFolderNames, sFileNames)

        For Each fileName As String In sFileNames
            sFileName(0) = fileName
            If fileName.EndsWith("xls", StringComparison.OrdinalIgnoreCase) And fileName.StartsWith("Demo", StringComparison.OrdinalIgnoreCase) Then
                ftp_client.ReceiveFiles(sFileName, "D:\Test\ftp_data\", True, False)
                ftp_client.DeleteFiles(sFileName)
            End If
        Next fileName

        ftp_client.Close()

        Dts.TaskResult = Dts.Results.Success
    End Sub

Once you have the file on local folder, you can use Foreach Loop Container to process these files.

Automatically publish products on Twitter

Jack Huang Developer 0 Comment

With the following PHP script, when we add new product in Zen Cart, it will automatically publish the product on Twitter.

(It can be added to /admin/includes/modules/update_product.php )

// BOF twitter feed
    
    if ($action == 'insert_product')
    {
      $purl = zen_catalog_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $products_id);
      $tinyPurl = file_get_contents('http://tinyurl.com/api-create.php?url='.$purl);
      function updateStatus($user, $password, $message)
      {
        $url = "http://twitter.com/statuses/update.xml";
        $curl = curl_init($url);
        $data = array('status'=>$message);
        curl_setopt($curl, CURLOPT_USERPWD, $user.':'.$password);
        curl_setopt($curl, CURLOPT_POST, 1);
        curl_setopt($curl, CURLOPT_POSTFIELDS, $data);
        curl_setopt($curl, CURLOPT_RETURNTRANSFER,1);
        curl_setopt($curl, CURLOPT_HTTPHEADER, array('Expect:'));
        $result = @curl_exec($curl);
        curl_close($curl);
        return $result;
      }
      $message = STORE_NAME . ' has a new product. ' . $tinyPurl;
      $user = 'YYYYYYYYY';
      $password = 'XXXXXXXXX';
      $result = updateStatus($user, $password, $message);
      $messageStack->add_session($result, 'error');
    }
    // EOF twitter feed