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.