topic Re: Excel VBA Macro and Changing AutoCAD Versions in VBA Forum

Excel VBA Macro and Changing AutoCAD Versions

Anonymous — Tue, 25 Feb 2020 20:20:42 GMT

Hi,

I've got a question about reference libraries that I was hoping someone could help me out with. We have an excel template that we use for product design that I've written a VBA macro for that completes an AutoCAD drawing file with the design parameters from Excel. Everything works fine as written, used with the AutoCAD version (2016) it was written with, but we've updated our AutoCAD software version and the macro won't work properly without reloading the correct reference libraries. The macro uses the AXDBLib (axdb20enu.tlb) and AutoCAD 2016 Type Library (acax20enu.tlb) to generate the drawing file.

However, this is a file used by multiple people in the company, which means that changing the references for a newer version complicates things for someone who hasn't been updated from the old version yet. Also, with the update schedule they've released, this issue will continue to come up as newer versions are introduced, older versions of the Excel file would have the same issue if used in the future, etc.

Is there a way to structure the macro to load (and potentially unload) the AutoCAD reference libraries mentioned above based on the software version of the person trying to run the macro through Excel? That way, people using multiple different versions of AutoCAD can each successfully use the same template file at the same time, with the same macro still working if needed to generate the file at a later date and time. Thanks!

Re: Excel VBA Macro and Changing AutoCAD Versions

Ed__Jobe — Tue, 25 Feb 2020 20:40:46 GMT

There are two ways to handle this. The first you mentioned, is to programatically handle project references. You do this by referencing Microsoft Visual Basic for Applications Extensibility 5.3 tlb. Then you can reference/unreference tlb's. I created this sub for use with AutoCAD. You would have to change it for Excel.

 Public Function SetReferenceByGUID(strGUID As String, maj As Long, min As Long) As VBIDE.Reference

     'Returns a Reference object if successful in setting a reference in the current dvb,
     'given the GUID of a tlb, olb, dll. Using a GUID avoids having to test
     'for a valid filepath.
     On Error GoTo ErrorHandler
     Dim objVB As VBE
     Dim objRef As VBIDE.Reference
     Dim objRefs As VBIDE.References
     Dim strPath As String

     Set objVB = Application.VBE
     Set objRef = objVB.ActiveVBProject.References.AddFromGuid(strGUID, maj, min)
     Set SetReferenceByGUID = objRef
     GoTo Finish:
ErrorHandler:
     If Err.Number = 32813 Then
         'reference was already set, just return the reference
         Set objRefs = objVB.ActiveVBProject.References
         For Each objRef In objRefs
             If objRef.GUID = strGUID Then
                 Set SetReferenceByGUID = objRef
             End If
         Next objRef
     Else
         Set SetReferenceByGUID = Nothing
     End If
Finish:
     Set objVB = Nothing
     Set objRef = Nothing
 End Function

The second way is to use Late Binding. You do this by dimensioning variables as Object rather than class specific. This makes development harder though, since you don't get Intellisense help when specifying methods/properties. To get around this, first get the project working and tested using regular binding, dimensioning as specific classes. Then after testing, set everything to Object. When I say everything, I mean only the AutoCAD variables.

Re: Excel VBA Macro and Changing AutoCAD Versions

Anonymous — Tue, 03 Mar 2020 19:27:43 GMT

Hi Ed,

The code you provided for setting the reference by GUID works as long as the GUID is known. Is there a way to determine the GUID from the Autocad version installed for the axdbXXenu.tlb and acaxXXenu.tlb libraries? That way, if the user needs to load the acax20enu.tlb for 2016, the acax22enu.tlb for 2018, etc., the code is able to verify the correct GUID instead of requiring that as an input.

Re: Excel VBA Macro and Changing AutoCAD Versions

Ed__Jobe — Wed, 04 Mar 2020 16:16:44 GMT

Perhaps this function is better suited to your needs.

Public Function SetReferenceByFile(FilePath As String, Optional DisplayErrors As Boolean) As VBIDE.Reference 'returns True if successful in setting a reference in the current dvb, 'given the FilePath of a tlb, olb, dll. The DisplayErrors option determines 'whether or not an error message will be displayed upon erroring. On Error GoTo ErrorHandler Dim objVB As Object 'VBE Dim objRef As VBIDE.Reference Dim objRefs As VBIDE.References Dim strPath As String Set objVB = AcadApplication.VBE Set objRef = objVB.ActiveVBProject.References.AddFromFile(FilePath) SetReferenceByFile = True GoTo Finish: ErrorHandler: If Err.Number = 32813 Then 'reference was already set, just return the reference Set objRefs = objVB.ActiveVBProject.References For Each objRef In objRefs If objRef.FullPath = FilePath Then Set SetReferenceByFile = objRef End If Next objRef Else If DisplayErrors = True Then MsgBox Err.Number & ", " & Err.Description, vbExclamation, "SetReferenceByFile." Else End If Set SetReferenceByFile = Nothing End If Finish: Set objVB = Nothing Set objRef = Nothing End Function

The next problem is deciding how to detect which version is installed. Is it possible that a user can have more than one version of acad installed?