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.


Leave Comment

How to make your VBA macros x64 compatible

Blog 6.3.2013 1 Comment

In a hurry? Just watch the video above. It covers the most important points. If your scenario isn’t covered in the video, then I probably cover it in this article.

Overview

SolidWorks 2013 includes VBA 7—the latest version of Microsoft’s Visual Basic for Applications. This is great news for several reasons, the most significant being: 1) VBA user forms no longer pop-up behind the SolidWorks application window, and 2) it proves to us that both Microsoft and SolidWorks Corporation are serious about maintaining the VBA language, which is the language of most SolidWorks API code.

VBA7 is 100% compatible with VBA6 (the version included in SolidWorks 2012 and earlier). For this reason, all SolidWorks API code that you write in pre-2013 should work in 2013, and vice versa. Nothing about the SolidWorks API itself has changed that requires modification.

That being said, you may have discovered that some of your macros no longer work in SolidWorks 2013. When you try to run the macro, you may get the following error.

VBA7 DLL error

Why is this? Were we not told that VBA7 is 100% compatible with VBA6? The issue isn’t with VBA. The reason your macros are failing is because they are not set up to reference 64 bit DLL files, or the DLL files you are trying to reference are not 64 bit compatible. This includes ActiveX controls, which have the extension .ocx but are actually still DLLs underneath. So if you are using any of the Microsoft Common Controls in your user form (e.g., slider, calendar, web browser) then you are using ActiveX controls that will fail in VBA7.

First I’m going to give you the quick-and-dirty steps on how to (hopefully) fix your dilemma. Later, if you’re so inclined, you can keep reading and learn the technical “why” behind the dilemma.

Fixing the Problem

Fixing the problem comprises three steps: 1) obtaining a 64 bit version of the DLL that is causing problems, 2) re-declaring its functions using new VBA7 keywords, 3) including conditional code that allows for backward compatibility on VBA6.

1. Get a 64 bit version of the DLL

If you wrote the DLL yourself, then re-compile the DLL to work on “Any CPU”. This will allow the DLL to run on 32 or 64 bit systems.

If someone else wrote the DLL, you will need to search online or contact them to determine if a 64 bit version available. An example is the SolidWorks Document Manager (SwDocumentMgr.dll). With the advent of 64 bit architecture, SolidWorks Corporation recompiled this DLL to work on 64 bit computers. If, for some reason, you cannot obtain a 64 bit version of this DLL then your only options are 1) find another 64 bit compatible DLL containing a function that does the same thing, 2) write your own DLL with the desired function and compile it for 64 bit, 3) if the DLL works on Microsoft’s COM framework (as opposed to .NET) then you can actually create a custom 32 bit “server” DLL to act as an interface between it and VBA7. For more information on the last solution, consult the excellent SolidWorks World 2013 presentation on VBA7 by Frank Lindeman, available here.

If the DLL is a Microsoft common library (Win32), then you can rest assured that the DLL has been recompiled for use on 64 bit. An easy way to determine whether the DLL is a Win32 DLL is to google it. If there is an article describing it at MSDN then it is probably a Win32 API function. Four of the most common Win32 DLLs are kernel23.dll, gdi32.dll, user32.dll, and shell32.dll.

As mentioned in the Overview, ActiveX controls are technically just wrappers for an underlying DLL. The most popular ActiveX controls are Microsoft Common Controls (mscomctl.ocx). Unfortunately, Microsoft has decided not to update the underlying DLLs to support 64 bit, so you will definitely need a workaround. If you can’t find an alternative DLL and don’t mind writing your own, keep in mind that since these controls are really just wrappers for underlying Win32 API calls, it is certainly possible to write a DLL that accesses those Win32 API calls directly.

2. Modify the function declarations and associated variables

Once you have a 64 bit version of the DLL, all you need to do is modify the function declaration to include the PtrSafe keyword after Declare and change all Long variables used in the function to LongPtr. For example, examine the difference in declaration with this Win32 API function called “SHGetPathFromIDList”:

32 bit
Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long

64 bit
Private Declare PtrSafe Function SHGetPathFromIDList Lib "shell32.dll" Alias "SHGetPathFromIDListA" (ByVal pidl As LongPtr, ByVal pszPath As String) As LongPtr

A few additional comments:

1. In your macro, you may have many more Long variables associated with the function that are declared on separate lines. If you do not convert these to LongPtr, then you may either get a “Type mismatch” compile error, or SolidWorks will crash entirely. Watch the video at the beginning of the article for an example of this. Conversely, you might also notice that it is not necessary to change all Long variables to LongPtr. This is because the only variables that need converting are those that hold pointers. If you don’t know whether a variable corresponds to pointer (and therefore should be converted to LongPtr), simply verify by using this Microsoft-published text file of PtrSafe Win32 API calls. Alternatively, you can indiscriminately convert all Long variables to LongPtr, since it doesn’t hurt anything.

2. The inclusion of the PtrSafe keyword simply means you think the DLL is safe for 64 bit use. This doesn’t mean it actually is safe! If you aren’t certain if a DLL is 64 bit, you can determine this using tools like Process Explorer or Dependency Walker.

3. Even if your DLL is compiled for 64 bit and you are using PtrSafe and LongPtr properly, your macro will still fail if your DLL has any dependent DLLs that are not compiled for 64 bit.

3. Make your code compatible with VBA6 and VBA7

The PtrSafe and LongPtr keywords that we used just a moment ago are unrecognized by VBA6. Does this mean we have to create a version of our macros for use on 64 bit and another version for 32 bit? Thankfully, no. This is where Conditional Compilation Constants come in. Using these constants, we can actually tell the VBA pre-processor to exclude certain lines of code during compilation. Specifically, we’ll use the “VBA7” constant to test for the version of VBA we’re using. If it is VBA7, we’ll use our declaration involving PtrSafe and LongPtr. Otherwise, we’ll use the VBA6 declaration. The syntax looks like this:

#If VBA7 Then
Private Declare PtrSafe Function SHGetPathFromIDList Lib "shell32.dll" Alias "SHGetPathFromIDListA" (ByVal pidl As LongPtr, ByVal pszPath As String) As LongPtr
#Else
Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long
#EndIf

Not too bad, is it? Note that you can use conditional compilation constants anywhere in your code, including LongPtr declarations within other functions and sub-procedures.

Technical Background

I hope by this point you know what you need to do to correct any 64 bit compatibility issues you’re having with your macros. For some of you, however, this isn’t enough. You want to know the “why” behind all of this. This next section should answer of that for you.

To answer the “why” question, we need to begin by understanding why 64 bit computers exist in the first place. As anyone who’s taken a basic course in digital logic knows, the smallest unit of information in a computer is a bit, which represents a 1 or a 0. The smallest amount of information that your computer can write to and read from, however, is not a bit but a byte, which is composed of 8 bits. Next, what you need to understand is that when data is stored in RAM (memory), each byte has an address. Just like a postal address, your computer needs to know where data is being stored in memory in order to read it or write to it. On a 32 bit system, these addresses are composed of 4 bytes. Since each byte is composed of 8 bits, that means we have 32 bits total to work with. In binary, that means our total number of possible bytes we can address with a 32 bit memory address is 2^32, or 4,294,967,296. As you know, that’s equal to 4 GiB.

Now, as we all know, 4GB just isn’t good enough! We want more memory… lot’s more! But with only 32 bits, we can’t address all of that memory. The solution? Start making addresses that are 64 bits in length. This will allow for a whopping 16 Exabytes of possible memory. (Note: No doubt it will only be a couple of decades before I look back and laugh at myself for finding 16 Exabytes so astounding!)

Continuing on, we need to know that addresses are also called “pointers”. Since these pointers are composed 4 bytes, we need store them in a data type of this size. If you open up the Microsoft VBA Help in the VB Editor and locate the “Data Type Summary” article, you’ll see that Long and Object data types listed as containing 4 bytes. Not surprisingly, these are the data types used for pointers. In the case of the SolidWorks API, the Object data type is used to store the pointer for our SolidWorks object interfaces like ISldWorks, IModelDoc2, etc. In the case of DLL functions, the pointers are often times declared as Long.

VBA6 is the version of VBA used in the late 90’s and the following decade. It can only handle 32 bit addresses. Now that computers are using 64 bit addresses, a new version of VBA was needed to handle addresses of this length. Thus VBA7 was created. Since VBA7 uses 64 bit memory addresses, any DLLs that it references must be 64 bit compatible. This is where the problem comes in for so many users: the DLLs referenced in their macros are still compiled for 32 bit computers. For many DLLs, this issue is solved by recompiling the DLL for 64 bit.

When you call a function from a DLL in a VBA macro, you need to use the Declare statement. For example, let’s say you have a DLL called “diskspace.dll” that resides in C:\somepath\. Here is how you would declare it in your module:

Declare Function getdiskinfo Lib "c:\somepath\diskinfo.dll"
(ByVal mydrive As String, ByVal myvolume As String, free As Long) As Long

On 64 bit computers, however, this will not work because our Long variables, which are being used as pointers, cannot handle 64 bit addresses. Basically, we need a new version of Long that can hold a 64 bit memory type. In VBA7, Microsoft created this new variable for us. It is called “LongLong”, and it only works in 64 bit applications. Converting our Long variables to LongLong isn’t the only modification we have to make, however. In order to indicate that the DLL is safe for 64 bit use, we also need to insert the PtrSafe keyword after the Declare keyword. So all together we have this:

Declare PtrSafe Function getdiskinfo Lib "c:\somepath\diskinfo.dll"
(ByVal mydrive As String, ByVal myvolume As String, free As LongLong) As LongLong

Keep in mind that this will ONLY work on 64 bit versions of our application. For applications like Microsoft Office, however, you can actually have a 32 bit version of Office running on a 64 bit computer. In this case, LongLong wouldn’t work, so Microsoft created a data type that transforms into Long or LongLong depending on the application. This data type is called “LongPtr”. SolidWorks x86 (32 bit) cannot be installed on a 64 bit computer, but you might as well use LongPtr anyway:

Declare PtrSafe Function getdiskinfo Lib "c:\somepath\diskinfo.dll"
(ByVal mydrive As String, ByVal myvolume As String, free As LongPtr) As LongPtr

To summarize: once your DLL is compiled for 64 bit, all you need to do is change the pointers to LongPtr and insert the PtrSafe keyword after the Declare keyword. Moreover, you can use what are called “Conditional Compilation Constants” to allow for backward compatibility of your code, which was demonstrated in any earlier section of the post.

Before I finish up, I want to share something interesting regarding the SolidWorks API and 64 bit. Even though 64 bit computers have been supported by SolidWorks since SolidWorks 2006, SolidWorks 2013 is the first version to contain VBA7 (since Microsoft did not make VBA7 available to third-party vendors until recently). That means that for SolidWorks 2006-2012 64 bit, VBA6 was still being used. How did that work? It worked because SolidWorks Corporation created an out-of-process COM server called swVBAserver.exe to handle the interaction between 64 bit SolidWorks and 32 bit VBA. (You may have noticed swVBAserver.exe in your Task Manager.) Normally, if VBA and SolidWorks were both 32 bit or 64 bit, VBA can run inside the SLDWORKS.EXE process. When this isn’t the case, however, VBA must run out of process, which also causes the API to run slightly slower. The out of process scenario is also what caused the infamous issue of user forms appearing behind the SolidWorks application: it is the swVBAserver.exe that owned the form, not SLDWORKS.EXE. In SolidWorks 2013, VBA does run inside the SLDWORKS.EXE process, so the correct owner is identified and no additional code is necessary. (Note: swVBAserver.exe is still used in SolidWorks 2013 and later, which is why you will see it in the Task Manager, but not as a proxy server for 32 bit VBA. It is also used to drive equation updates using IEquationMgr.)

If you want more resources on the technical aspects of the move to 64 bit, I’d encourage you to check out the following:

Thanks for reading!
Keith

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


Leave Comment

Fix “Compile error : Can’t find project or library”

Blog 4.16.2013 1 Comment

You’ve written your first “serious” SolidWorks API macro. You’re ready to share with your co-workers. You fire up your email, attach the macro, and click Send. Expecting an avalanche of compliments, you instead get several responses from your co-workers telling you that the macro doesn’t run. “Doesn’t run?! How can that be? It runs fine on my computer!” You ask for a screenshot of the error and this is what they send you:

Compile error: can't find project or library

Has this ever happened to you? Maybe not in this exact scenario, but the infamous “Compile error: Can’t find project or library” error message eventually befalls every API programmer trying to share their VBA handiwork with others. Fortunately, it isn’t that hard to fix.

The Cause

When you write code with the SolidWorks API, you constantly use what are called “API calls”. These are the functions you use in your code to execute commands in SolidWorks. Visual Basic for Applications, however, can’t make heads or tails of these API calls unless your code references the appropriate libraries. By libraries I mean the .dll and .tlb files created by SolidWorks Corporation that define each SolidWorks API call. Some of the more common ones include:

  • SolidWorks 20XX Type Library
  • SolidWorks 20XX Commands type library
  • SolidWorks 20XX Constant type library
  • SolidWorks 20XX Extensibility type library
  • Etc.

You can see this list if, while in the VB Editor, you go to Tools–>References. If you click on one of these listings, you’ll also see the file path for the type library. When you create a new macro on your computer, the macro looks at these paths for the references. The problem occurs when you transfer that macro to another computer that has these references in a different location. If your macro can’t locate a reference, you may see the word “MISSING” before the missing reference, but not necessarily.

The Solution

At the computer where the error is occurring, open up the macro in the Visual Basic Editor. Go to Tools–References, which displays a list of the libraries your macro is referencing. Do you see “MISSING” before any of the checked listings at the top? (Example) If yes, then try this first set of instructions. Otherwise, skip to the second set of instructions.

  1. Uncheck the missing references.
  2. Click OK in the References dialog box
  3. Re-open the References dialog by going to Tools–>References.
  4. Re-check the references you unchecked earlier. (They should still be listed near the top of the list.)
  5. Test out the macro now. If the problem persists, you can return to the References dialog box and try navigating to the location of the .tlb or .dll, which is usually in a folder like C:\Program Files\SolidWorks Corp\SolidWorks. If this still doesn’t fix the problem, to the next set of instructions.

In this second set of instructions, you will copy the contents of the problematic macro to a brand new macro created on this computer. (This is the method demonstrated in the video at the beginning of the post.)

  1. Leave the VB Editor open and return to SolidWorks. Create a new macro by going to Tools–>Macro–>New.
  2. Back in the VB Editor, both the problematic macro and the new macro should be visible in the Project Explorer in the top left.
  3. Delete out the existing module in the new macro by right clicking on it and choosing Remove . Click No if you are asked whether you want to export the module.
  4. One by one, drag all of the modules from the problematic macro into the new macro’s project name.
  5. Test the new macro on this computer. You should not encounter an error.

Finally, don’t forget that this is just one of the many compile or run-time errors you can run into while programming with the SolidWorks API using VBA. Our FREE SolidWorks API Debugging Tips PDF contains descriptions and solutions for 14 other common errors. Grab it now and keep it handy—you won’t regret it!

Conquering compile errors,
Keith

Did you find this post helpful? Keep up with future CADSharp.com content, webinars, and special offers by signing up for our newsletter.


Leave Comment