Using Microsoft Excel with the SolidWorks API

Blog 7.3.2013 11 Comments

Given the ubiquity of Microsoft Excel in engineering departments worldwide, it is no surprise that our “Using Microsoft Excel with the SolidWorks API” tutorial, available to Premium members, is one of our most popular. While I am not going to cover all of the material presented in that lesson, I do want to provide you with a basic run-down of using the Excel API in a SolidWorks macro, and vice versa. As a bonus, I’ll also cover how to include an Excel macro in SolidWorks design table.

It’s All About the References

Why is it so easy to integrate the Excel and SolidWorks APIs using Visual Basic for Applications? Microsoft created VBA as a way for users to program with the APIs of their Office products. They also made VBA available to third-party software developers (like SolidWorks Corporation) to integrate in their software. So that’s what SolidWorks Corporation chose to do—use VBA as one way to create macros in SolidWorks. Consequently, SolidWorks users get to use the same language and development environment to program in both SolidWorks and Microsoft Office. Very convenient!

But here’s an important point that many novice API programmers miss: VBA doesn’t automatically understand SolidWorks API code. Rather, SolidWorks Corporation chose to expose SolidWorks API interfaces and calls and store them in “type libraries”. By default these libraries are located in the root directory of your SolidWorks installation. If you want your VBA macro to understand SolidWorks API code, your macro must REFERENCE these type libraries. Guess what? By default, your SolidWorks VBA macro does references these libraries. You can see them if you go to Tools–>References while in the VB Editor.

What I just wrote about SolidWorks is true for Excel or any other application. VBA can’t “see” the API for an application unless its libraries (.tlb, .dll, or .exe) are referenced. Now here’s the big takeaway: if you want to integrate Excel API code into your SolidWorks macro, it is as simple as referencing the Excel libraries alongside your SolidWorks libraries. Indeed, you can reference as many libraries as you want. You could control Excel, Word, and Access in your SolidWorks macro if you wanted.

Enough technical background. Let’s actually make this happen.

Controlling Excel In A SolidWorks Macro

Setting up the reference to the Excel library is as simple as this:

  1. Open up a new VBA macro in SolidWorks
  2. In the VB Editor, go to Tools–>References
  3. Locate “Microsoft Excel 14.0 Type Library”, click the check box next to it, then click OK on the References dialog box

Now, to connect to Excel we’re going to use this code:

Dim xlApp As Excel.Application
Set xlApp = New Excel.Application

Notice its similarity to the code we use to get ISldWorks. Beyond that, learning how to work with the Excel API is really just as simple as using the Object Browser and Google a lot. Indeed, unlike SolidWorks, there is no “Excel API Help” contained with Excel. As an example, here’s some code that will display a message box for each row in the first column of a spreadsheet. (Place this sample spreadsheet in C:\ to use the following example.) Make sure that Excel is not already open while you run this code.

#If VBA7 Then
Declare PtrSafe Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As Long
#Else
Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
#End If
Const WM_QUIT = &H12
    
Sub main()
    Dim swApp As SldWorks.SldWorks
    Set swApp = Application.SldWorks
    
    Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    
    Set xlApp = New Excel.Application
    xlApp.Visible = False
    Set xlWB = xlApp.Workbooks.Open("C:\test.xls")
    
    'find number of rows with data
    Dim row As Integer
    row = 1
    With xlWB.Worksheets(1)
        While Cells(row, 1).Value <> ""
            swApp.SendMsgToUser2 Cells(row, 1).Text, swMbInformation, swMbOk
            row = row + 1
        Wend
    End With
    
    'clean up
    xlApp.Visible = True
    PostMessage xlApp.hwnd, WM_QUIT, 0, 0
    
    Set xlWB = Nothing
    Set xlApp = Nothing
End Sub

Two notes: 1) The Win32 API call is used to properly shut down Excel, otherwise a residual EXCEL.EXE process can remain that interferes with future attempts to use Excel, 2) If you get a compile error that says “User-defined type not defined”, it probably means that you did not set the correct references.

Interesting in learning how to create a 3D point cloud in SolidWorks using point data stored in an Excel spreadsheet? Check out Lesson 7.1 on our Videos page.

Controlling SolidWorks In An Excel Macro

Now that we know how to run Excel from SolidWorks, let’s do just the opposite. First, a note about the differences between SolidWorks and Excel macros. In SolidWorks, the VBA macro is its own file (.swp). You cannot store a SolidWorks macro in a SolidWorks document and then run it from there. In Excel, it’s the exact opposite. Macros cannot be saved as external files. They must be stored in the Excel document, and they must be run from there. To created, edit, run, or delete an Excel macro, follow these steps:

  1. Open Excel
  2. Go to the View tab in the ribbon bar
  3. Click the Macros button on the far right
  4. To create a new macro, start typing a name and then click Create, otherwise select a macro from the list and choose Run, Edit, or Delete

Once you’ve created a new macro, you’ll need to go to Tools–>References and add in “SldWorks 20XX Type Library”. Next, try out this code:

Dim swApp As SldWorks.SldWorks
Sub main()
    Set swApp = CreateObject("SldWorks.Application")
    swApp.Visible = True
    swApp.SendMsgToUser "Hello!"
End Sub

You should have a good idea of where to go from there. But what if you have more that one version of SolidWorks on your computer? How do you specify which one to open? Simply specify the version number like this:

Set swApp = CreateObject("SldWorks.Application.21")

Determine the version number for a major release like this: take the last two digits of the year (e.g., “13” for SolidWorks 2013) and add 8. Therefore, SolidWorks 2009’s version number is 17, 2013’s is 21, and so on.

What if, instead of opening a new instance of SolidWorks, you want to get an existing instance? Instead of calling CreateObject, use GetObject like this:

Set swApp = GetObject(, "SldWorks.Application")

Of course, you can also specify a version if you want:

Set swApp = GetObject(, "SldWorks.Application.21")

Storing An Excel Macro In A Design Table

Finally, for those of you who use design tables, I want to show you a cool trick. If you have an Excel macro that you would prefer to use to generate configurations in your design table, you can keep it embedded in the design table if you take the following steps:

  1. Create the design table in Excel
  2. Very important: save the design table as a .xls, NOT a .xlsm
  3. In your SolidWorks part or assembly, delete out the existing design table if it already has one
  4. Insert the design table you created in step 1, choosing the “From file” option
  5. Edit your design table in a new window and you should have access to your Excel macro in View–>Macros


Want to share any other tips or tricks for using Excel with SolidWorks? Please leave a comment!

Keith

Want to keep up with future CADSharp.com content, webinars, and special offers? Sign up for our newsletter.


11 Comments

  1. You can also run an Excel macro from SolidWorks by using:
    xlApp.Run "MyMacro"

    MyMacro (or whatever you choose to call it) will, of course, have to exist as a macro in the workbook you open. This example shows you how to programmatically create a macro from scratch:

    How To Create and Call an Excel Macro Programmatically from VB

    Note that in order to get this to work, you may have to change some security settings in Excel. The following are for Excel 2010:

    1. File–>Options, which will open a dialog
    2. On the left pane at the bottom, click Trust Center
    3. Trust Center settings, a command button in the bottom right
    4. On the left pane in the center, click Macro Settings
    5. Check “Trust access to the VBA project object model”

  2. dec

    Hi Keith

    I am trying to connect to SolidWorks from Excel. I am using SW 2011 x64 and Excel 2010.

    I have tried your method of connecting which is:

    Set swApp = GetObject(, “SldWorks.Application”).

    At first it worked, then all of a sudden it stopped working and even when SW is running and a document open, I just get error 429 Activex component can’t create object.

    I have declared swap as a Object type and a SolidWorks type for testing with references and without references, but still having issues.

    Any ideas?

    Thanks
    Declan

  3. poorveshmistry

    Hey Keith,

    I have SolidWorks 2013 & MS Excel 2013
    I had created any SolidWorks parts with configurations that I regularly use in my assembly design and so I have created an Excel sheet which contains list of SolidWorks files & their locations. But every time I have to open Excel, refer it for part location & load those parts in SolidWorks.

    Is there a way (macro) to load those parts in SolidWorks through the Excel file ?

    Thanks & Regards
    Poorvesh

    • Hi,

      If I am understanding your issue correctly, you want a way to quickly select a SolidWorks model in your spreadsheet and then open it? Yes, this wouldn’t be very hard to do. What you would do is have the user select the row with the desired model, run the macro, and it will copy the path and use that as the first argument in ISldWorks::OpenDoc6. You could actually have this function connected to a command button in Excel.

      I am going to be presenting on the topic of SolidWorks and Excel at this upcoming SolidWorks World, so I may use an example like this. I’ll send you the code if I do.

      Thanks,
      Keith

      • poorveshmistry

        Finally the following code worked as expected, I thought I should share, my only problem with the code is just as you had mentioned, Excel does not shut down properly, so cannot run the macro again, until I kill the application “EXCEL.EXE”in task manager, please provide me some solution.

  4. Recently learned about this:

    “Do not post the WM_QUIT message using PostMessage; use the PostQuitMessage function.”

    http://msdn.microsoft.com/en-us/library/windows/desktop/ms644944%28v=vs.85%29.aspx
    http://msdn.microsoft.com/en-us/library/windows/desktop/ms644945%28v=vs.85%29.aspx

    Thus far, this has not been an issue, however.

  5. I have also found that when creating and destroying pointers to Excel applications many times in a row, it is necessary to cause the thread to wait a half second or so between these operations, otherwise errors can inadvertently crop up. To cause the thread to sleep in VBA:

    #If VBA7 Then
    Private Declare PtrSafe Sub Sleep Lib “kernel32” (ByVal dwMilliseconds As Long)
    #Else
    Private Declare Sub Sleep Lib “kernel32” (ByVal dwMilliseconds As Long)
    #End If

    Sub Sleep()
    Sleep 1000 ‘Implements a 1 second delay
    End Sub

  6. Update: Since writing this blog post, I’ve found that bluntly killing the EXCEL process is more reliable than using PostMessage, like I demonstrate above. Below is the VB.NET code I currently use. Since it will close all Excel processes, I strongly recommend ensuring that the user does not have any Excel processes open before running this code, otherwise they could lose unsaved changes.

    Public Shared Function QuitExcel(app As Application, ByRef errMsg As String, ByRef errStackTrace As String) As Boolean
        errMsg = String.Empty
        errStackTrace = String.Empty
        
        Try
            app.DisplayAlerts = False
        
            For Each wb As Workbook In app.Workbooks
                wb.Close(False)
            Next
        
            app.DisplayAlerts = True
            app.Visible = True
            app.Quit()
            app = Nothing
        
            ' Terminate residual EXCEL.EXE processes
            IsRunning(True)
        
            Return True
        Catch ex As Exception
            errMsg = ex.Message
            errStackTrace = ex.StackTrace
            Return False
        End Try
    End Function
        
    Public Shared Function IsRunning(killAll As Boolean) As Boolean
        Dim swProcess() As Process
        swProcess = Process.GetProcessesByName(ProcessName_Excel)
        
        If swProcess.Length = 0 Then Return False
        
        If killAll Then
            For Each myProcess As Process In swProcess
                myProcess.Kill()
            Next
        End If
        
        Return True
    End Function

Questions and Comments

You must be logged in to post a comment.