Category: VBA

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
        DoEvents
 
        'Activates SnagIt Editor
        AppActivate "SnagIt Editor"
        DoEvents
        
        '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
           .Capture
           
       End With

    Next

    Set myImage = Nothing

End Sub

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

        '...as 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;

        Next

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

    Next

    'Close text file
    Close #1

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

End Sub

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())
        Else
            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

        Else

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

        End If

    End With

End Function