VBA – DavidRingstrom.com

Sep 10

Convert .snag files to .jpg

Filed under Excel, 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

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

Set myImage = Nothing

End Sub

Jan 14

Using Collections in Excel to populate UserForm Controls

Filed under Uncategorized, VBA

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
    Me.lstDropdown.Clear

    '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

    Next

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

        Me.lstDropdown.AddItem myList.Item(i)

    Next

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

End Sub

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

Me.lstDropdown.Clear

'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

Jan 14

Write to CSV file

Filed under Excel, VBA

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

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;

'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

Dec 22

Verifying Dates within Excel UserForms

Filed under Excel, VBA

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

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

Category: VBA

Convert .snag files to .jpg

Using Collections in Excel to populate UserForm Controls

Write to CSV file

Verifying Dates within Excel UserForms

David’s Books

Contact David

Contact Us

Quick Links