Dec 28, 2015

Common VBA Procedures for Batch Export

Try as Microsoft may, it doesn’t look like VBA is going away anytime soon!  And really, it’s a great place to start developing macros for SOLIDWORKS.  The macros are single-file, self-contained, and easy to develop.  Not to mention that there are thousands of existing VBA macros out there and lots of sample code online. 

The downside?  VBA doesn’t have as many pre-built functions for common file operations available to C# and 

In an effort to make your macro development easier, I’ve put together a handful of common routines and functions you can add to your SOLIDWORKS VBA macros.  If there are others you’d like to see, let me know!

Batch processing of files is one of those places where you need to manipulate file information.  Rather than packing all of the processing into one routine, I have broken it out into common functions so you can save them into a code module to use in all of your macros.

Macro Code

Start by adding the following procedures and functions to your VBA macro.

Option Explicit
Dim swApp As SldWorks.SldWorks

'batch process using Dir - one folder level only
Sub BatchConvert(folderPath As String, _
    fileNameFilter As String, _
    convertExtension As String, _
    Optional outputFolderPath As String)
    Dim file As String
    'use Dir function to get all files matching the filter
    'in the specific folder
    file = Dir(folderPath & fileNameFilter)
    Do While Not file = ""
        Dim filePath As String
        filePath = folderPath & file
        Dim newFilePath As String
        newFilePath = ConvertSWFile(filePath, convertExtension, _
        'get the next file
        file = Dir
End Sub

'batch process using FileSystemObject - all sub folders included
'must add a reference to Microsoft Scripting Runtime type library
Sub BatchConvert2(folderPath As String, _
    fileNameFilter As String, _
    convertExtension As String, _
    Optional doSubFolders As Boolean = False, _
    Optional outputFolderPath As String)
    Dim file As String
    Dim fso As New FileSystemObject
    Dim fld As Scripting.Folder
    Set fld = fso.GetFolder(folderPath)
    Dim f As Scripting.file
    For Each f In fld.files()
        'wildcards not allowed in fileNameFilter when using
        'FileSystemObject methods
        If InStr(1, f.Name, fileNameFilter, _
        vbTextCompare) <> 0 Then
            Dim newFilePath As String
            newFilePath = ConvertSWFile(f.Path, _
                convertExtension, outputFolderPath)
        End If

    'recursive routine if doSubFolders is True
    If doSubFolders Then
        Dim subFolder As Scripting.Folder
        For Each subFolder In fld.subFolders
            Call BatchConvert2(subFolder.Path, fileNameFilter, _
            convertExtension, doSubFolders, outputFolderPath)
    End If
End Sub

'open the file in SOLIDWORKS and convert by changing 
'the file extension. new file will be created in the 
'same folder as the parent file
'or, pass an outputFolderPath string to send files to 
'a specific folder
Function ConvertSWFile(filePath As String, _
    convertExtension As String, _
    Optional outputFolderPath As String) As String
    Dim model As ModelDoc2
    Dim fileType As Long
    fileType = GetSWFileType(filePath)
    Dim errors As Long, warnings As Long
    Set model = swApp.OpenDoc6(filePath, _
        fileType, swOpenDocOptions_Silent, _
        "", errors, warnings)
    If model Is Nothing Then
        ConvertSWFile = ""
        Exit Function
    End If
    Dim newFilePath As String

    newFilePath = ChangeExtension(filePath, convertExtension)
    If outputFolderPath <> "" Then
        newFilePath = ChangeFolderPath(newFilePath, _
    End If
    Call model.Extension.SaveAs(newFilePath, _
        swSaveAsCurrentVersion, swSaveAsOptions_Silent, _
        Nothing, errors, warnings)
    Call swApp.CloseDoc(filePath)
    ConvertSWFile = newFilePath
End Function

'return the file type from a full file path
'helpful for opening various SOLIDWORKS files
Function GetSWFileType(filePath As String) As Long
    Dim ext As String
    Dim fileType As Long
    ext = Right(filePath, Len(filePath) - InStrRev(filePath, _
        ".") + 1)
    Select Case UCase(ext)
        Case ".SLDPRT"
            fileType = swDocPART
        Case ".SLDASM"
            fileType = swDocASSEMBLY
        Case ".SLDDRW"
            fileType = swDocDRAWING
        Case Else
            fileType = swDocNONE
    End Select
    GetSWFileType = fileType
End Function

'============ File path manipulation ==================

'change the extension of a file from a given file path
Function ChangeExtension(filePath As String, _
newExtension As String) As String
    Dim filePathNoExt As String
    filePathNoExt = Left(filePath, InStrRev(filePath, ".") - 1)
    ChangeExtension = filePathNoExt & newExtension
End Function

'change the folder path of the file
Function ChangeFolderPath(filePath As String, _
newFolderPath As String) As String
    Dim fileName As String
    fileName = Right(filePath, _
        Len(filePath) - InStrRev(filePath, "\"))
    ChangeFolderPath = newFolderPath & fileName
End Function

'verify the path ends with \
Function VerifyFolderPath(folderPath As String) As String
    If InStrRev(folderPath, "\") <> Len(folderPath) Then
        VerifyFolderPath = folderPath & "\"
        VerifyFolderPath = folderPath
    End If
End Function

'verify folder exists
Function VerifyFileFolderExists(fullPath As String) As Boolean
    Dim res As String
    res = Dir(fullPath, vbDirectory)
    'result will be some value if the folder exists
    If res = vbNullString Or fullPath = "" Then
        VerifyFileFolderExists = False
        VerifyFileFolderExists = True
    End If
End Function

'============ Other utilities ===================

'open any file in its associated application
Sub OpenFile(ByVal filePath As String)
   Dim WshShell As Object
   Set WshShell = CreateObject("WScript.Shell")
   'append quotes in case of spaces in file path
   WshShell.Run Chr(34) & filePath & Chr(34)
End Sub

'show a folder browser dialog and return the selected folder path
'or empty string if cancelled
Function BrowseFolder(message As String) As String
    Dim folderBrowser As Object
    Dim options As Integer
    options = 40 'UI with New Folder button and right-click menus
    'options = 0 'UI without New Folder button 
    'and no right-click menus
    Set folderBrowser = CreateObject("Shell.Application"). _
        BrowseForFolder(0, message, options)
    On Error Resume Next
    BrowseFolder = folderBrowser.Self.Path
    On Error GoTo 0
End Function


The following main procedure makes use of most of these procedures and functions to publish SOLIDWORKS files to PDF in a selected directory.

Sub main()

    Set swApp = Application.SldWorks
    Dim folderPath As String
    'browse for folder
    folderPath = BrowseFolder("Select folder to process...")
    folderPath = VerifyFolderPath(folderPath)
    If Not VerifyFileFolderExists(folderPath) Then Exit Sub

    Dim convertExtension As String
    convertExtension = ".PDF"
    'if using Dir function to get files using wildcards
    Dim fileNameFilter As String
    fileNameFilter = _
    InputBox("Enter the file name filter (*.sldprt, *.sld*):")
    Dim outputFolderPath As String
    ''optionally, set an output folder path
    'outputFolderPath = _
    'BrowseFolder("Select destination folder...")
    'outputFolderPath = VerifyFolderPath(outputFolderPath)
    Call BatchConvert(folderPath, fileNameFilter, _
        convertExtension, outputFolderPath)
    ''or if using FileSystemObject to process all sub folders...
    'fileNameFilter = _
    'InputBox("Enter the file name filter " _
    '& "(sldprt or 1234.slddrw):")
    'Call BatchConvert2(folderPath, fileNameFilter, _
    '    convertExtension, True)
    MsgBox "Finished!"
End Sub


BatchConvert uses the Dir function to get all files in a given folder and allows wildcards in the file name filter.  This method doesn’t support processing of sub-folders.  

BatchConvert2 allows processing of sub-folders, but doesn’t allow wildcards because of its use of the FileSystemObject.  Either routine must be passed the desired conversion extension, including the period (.PDF in this example).  Optionally, you can pass an output folder path as a final argument.  If an output folder is specified, all published files go to that folder.  If no output folder path is passed, the files are created in the same location as their source files.

ConvertSWFile is called for each file path.  This does all the heavy lifting of opening the file in SOLIDWORKS, changing the file extension and optional output folder path, saving the new output file and closing the source file.  It returns the newly created file name if you need to log the results.

File Path Manipulation

There are a few functions to help with common operations like changing file extensions, changing folder paths as well as verifying a folder path string is complete and verifying if a file or folder exists.

Other Utilities

The last section includes OpenFile, a routine to open any file in its associated application.  This is triggering the Windows shell, so it is just like double-clicking on a file in Windows Explorer.  It does not return an interface to the opened file and does not wait until the operation is complete. 

BrowseFolder is a function that launches a folder browser and returns the user-selected folder path or an empty string if cancelled. 

I hope you find these helpful in your next macro project!  Let me know if there are others you’d like to see or share.