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:
- Open up a new VBA macro in SolidWorks
- In the VB Editor, go to Tools–>References
- 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
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
Const WM_QUIT = &H12
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
While Cells(row, 1).Value <> ""
swApp.SendMsgToUser2 Cells(row, 1).Text, swMbInformation, swMbOk
row = row + 1
xlApp.Visible = True
PostMessage xlApp.hwnd, WM_QUIT, 0, 0
Set xlWB = Nothing
Set xlApp = Nothing
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:
- Open Excel
- Go to the View tab in the ribbon bar
- Click the Macros button on the far right
- 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
Set swApp = CreateObject("SldWorks.Application")
swApp.Visible = True
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:
- Create the design table in Excel
- Very important: save the design table as a .xls, NOT a .xlsm
- In your SolidWorks part or assembly, delete out the existing design table if it already has one
- Insert the design table you created in step 1, choosing the “From file” option
- 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!
Want to keep up with future CADSharp.com content, webinars, and special offers? Sign up for our newsletter.
You can also run an Excel macro from SolidWorks by using:
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”
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.
In Windows Task Manager there was a process open. Once I had ended the process and ran the macro again everything worked fine.
Sorry to have troubled you. Great blog. Thanks
Glad you got it working. Thanks for sharing this tip.
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
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.
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.
Poorvesh, try using this code to kill the residual Excel process:
Shell "TASKKILL /F /IM Excel.exe", vbHide
Recently learned about this:
“Do not post the WM_QUIT message using PostMessage; use the PostQuitMessage function.”
Thus far, this has not been an issue, however.
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)
Private Declare Sub Sleep Lib “kernel32” (ByVal dwMilliseconds As Long)
Sleep 1000 ‘Implements a 1 second delay
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
app.DisplayAlerts = False
For Each wb As Workbook In app.Workbooks
app.DisplayAlerts = True
app.Visible = True
app = Nothing
' Terminate residual EXCEL.EXE processes
Catch ex As Exception
errMsg = ex.Message
errStackTrace = ex.StackTrace
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