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 VB.net. 

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, _

            outputFolderPath)

        'get the next file

        file = Dir

    Loop

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

    Next

    '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)

        Next

    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, _

        outputFolderPath)

    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 & "\"

    Else

        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

    Else

        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

Usage

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

Descriptions

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.