Category: VBA

Convert .snag files to .jpg

The following code converts .snag files into .jpg formats. This isn't directly supported by the SnagIt COM API, but I was able to hack it by way of SendKeys to get an image onto the clipboard. In a broader sense, I'm using this to convert .snag files to .jpg on the file, which I can then insert into blank PowerPoint slides for dynamically building presentations. SnagIt code appears to be scarce on the Internet, so I'm offering this as thanks to others that posted various aspects that I pieced together to accomplish this.

Sub SnagItAutomation()

    'Creates a SnagIt object (SnagIt 8 and later)
    Dim myImage As SNAGITLib.IImageCapture2
    Set myImage = CreateObject("SnagIt.ImageCapture")
    'List of .snag file names
    Dim myFiles(4) As String
    myFiles(0) = "image1"
    myFiles(1) = "image2"
    myFiles(2) = "image3"
    myFiles(3) = "image4"
    myFiles(4) = "image5"
    'Path to SnagIt exe file
    strProgramPath = "C:\Program Files (x86)\techsmith\Snagit 10\SnagitEditor.exe"
    'Path to image (.snag) files
    strInputPath = "I:\"
    'Path to where resulting .jpg files should be saved
    strOutputPath = "T:\pending\"
    For i = LBound(myFiles) To UBound(myFiles)
        'Opens image file in SnagIt editor
        Shell strProgramPath & " """ & strInputPath & myFiles(i) & ".snag"""
        'Forces Excel to finish the process before moving on
        'Activates SnagIt Editor
        AppActivate "SnagIt Editor"
        'Sends Ctrl-C to copy image to the clipboard
        SendKeys "^c"
        'Generates capture
        With myImage
            'Tells SnagIt to grab capture from the clipboard
           .Input = siiClipboard
            'Tells SnagIt that we want our output to be a file
           .Output = sioFile
           'Sets output path for file
           .OutputImageFile.Directory = strOutputPath
           'Sets specifies name for output file
           .OutputImageFile.Filename = myFiles(i)
           'Sets file type for output file
           .OutputImageFile.FileType = siftJPEG
           'Tells SnagIt to use our fixed file name as specified above
           .OutputImageFile.FileNamingMethod = sofnmFixed
            'Intiates the capture
       End With


    Set myImage = Nothing

End Sub

Using Collections in Excel to populate UserForm Controls

The following is programming code we frequently use to populate a drop-down list or listbox on an Excel UserForm with a unique list of items from a spreadsheet.

Private Sub UserForm1_Intialize()

    'Creates a new collection
    Dim myList As New Collection

    'Determines number of rows to loop through
    numRows = Range("A1").CurrentRegion.Rows.Count

    'Optional - erases existing dropdown list from control

    'Loops through each row and adds to collection
    For i = 2 To numRows

        'An item can only be added to a collection once, hence the on error
        On Error Resume Next
        myList.Add Cells(i, "A"), Cells(i, "A")
        On Error GoTo 0


    'Populates control with items from the collecton
    For i = 1 To myList.Count

        Me.lstDropdown.AddItem myList.Item(i)


    'Optional - erases an existing value
    Me.lstDropdown.Value = ""

End Sub


Write to CSV file

The following is programming code we frequently use when creating CSV files from Excel.

Sub WriteToCSVFile()

    'Captures date/time stamp for appending to file name
    strTime = Replace(Format(Now(), "mm-dd-yy hh:mm"), ":", "-")

    'Prompts user to specify a file name
    strFile = Application.GetSaveAsFilename("File Name " & strTime, "*.csv,*.csv", , "Specify File Name")

    'Terminates routine if user clicks Cancel
    If strFile = False Then Exit Sub

    'Ensures text file is closed
    Close #1

    'Opens text file for output
    Open strFile For Output As #1

    'Determines number of rows to write
    numRows = Range("A1").CurrentRegion.Rows.Count

    'Determines number of colums to write
    numCols = Range("A1").CurrentRegion.Columns.Count

    'Loops through all rows...
    For i = 1 To numRows

        ' well as all except for last column
        For j = 1 To numCols - 1

            'Include semi-colon to prevent Excel from adding a line-break
            Write #1, Cells(i, j).Value;


        'Write last columnn for given row, omit semi-colon so Write adds a line-break.
        Write #1, Cells(i, numCols).Value


    'Close text file
    Close #1

    'Notify user
    MsgBox "The upload file " & strFile & " has been created.", vbInformation, "Upload file created"

End Sub


Verifying Dates within Excel UserForms

The following code is a routine we frequently use in our Excel projects to verify that the user has entered a date properly within a text box in a userform. In addition, dates such as 0704 get transformed to 07/04/yy where yy is the current year. Valid date inputs include 0704, 070412, 07/04/12, or 07/04/2012. All four will be validated and transformed to 07/04/12.

Function CheckDate(ctrl As Control)

    With ctrl

        strMonth = Left(.Value, 2)
        strDay = Mid(.Value, 3, 2)

        If Len(.Value) = 4 Then
            strYear = Year(Now())
            strYear = Right(.Value, 2)
        End If

        On Error Resume Next
        strDate = DateValue(strMonth & "/" & strDay & "/" & strYear)
        On Error GoTo 0

        If IsDate(strDate) Then

            .Value = Format(strDate, "mm/dd/yy")
            CheckDate = False

        ElseIf IsDate(.Value) Then

            .Value = Format(.Value, "mm/dd/yy")
            CheckDate = False

        ElseIf .Value = "" Then
            'Do nothing
            CheckDate = True


            MsgBox "You entered an invalid date!", vbExclamation, "Caution!"
            CheckDate = True

        End If

    End With

End Function