Run an AutoCad VBA Form from Excel

Anonymous · ‎01-26-2011

I have a form in AutoCAD VBA that is used to draw a machine part. The user enters some dimensions in the textboxes and then clicks a "Draw" button, executing AutoCAD VBA commands to draw the part. This has been working successfully for a few years now.

The dimensions that the user inputs into the form originate from an Excel spreadsheet used for designing the part. I have received a request to produce a button in Excel that automatically raises that form and pre-populates the textboxes. Is this possible, and if so, does anyone have some sample code that demonstrates how to perform this task from Excel VBA?

Thanks in advance for any information you can provide.

-John

Anonymous · ‎01-27-2011

Hi,

I’m not sure about running the VBA from Excel, but I do use Excel data CAD, to create hatches on a drawing. The below code should help you in what you need to do.

Function ExcelImport()

Dim excelApp As Object

Set excelApp = CreateObject("Excel.Application")

If Err <> 0 Then

Err.Clear

Set excelApp = CreateObject("Excel.Application")

If Err <> 0 Then

MsgBox "Could not start Excel", vbExclamation, "error"

End If

excelApp.Visible = False

strFile = excelApp.GetOpenFilename("Excel Files (*.xls), *.xls")

Set wbkObj = excelApp.Workbooks.Open(strFile)

Set shtObj = excelApp.Worksheets(1)

i = 1

' count rows

Do Until shtObj.Cells(i, 1) = ""

i = i + 1

Loop

ReDim varExcelData(0 To i - 3, 0 To 3)

If shtObj.Cells(2, 1) = "" Then GoTo NoData

' ***************************************

' You will need to do what you need here

' ***************************************

NoData:

' Close Excel

Set excelApp = GetObject(, "Excel.Application")

excelApp.Quit

End Function

Anonymous · ‎01-28-2011

Thanks for your reply! In considering your suggestion, I was led to discover the following solution. It involves writing values to a temporary data file (since you can't send calling arguments), and then calling an AutoCAD macro that reads the values in and shows the form.

Here's the code behind my Excel commandbutton:

Private Sub cmdDraw_Click()
    Dim iRow As Integer
    Dim AcadApp As Object
    
    Application.StatusBar = "Writing output file..."
    If Dir("C:\TEMP", vbDirectory) = "" Then
        MkDir "C:\TEMP"
    End If
    Open "C:\TEMP\SpringPlate.txt" For Output As #1
        For iRow = 2 To 14
            Write #1, Worksheets("Spring Plate").Cells(iRow, 9).Text
        Next iRow
    Close #1
    
    Application.StatusBar = "Opening AutoCAD..."
    On Error Resume Next
    Set AcadApp = GetObject(, "AutoCAD.Application") 'Try to get a running AutoCAD
    If Err.Number <> 0 Then     'Was AutoCAD not running?
        On Error GoTo xErr
        Set AcadApp = CreateObject("AutoCAD.Application") 'create a running AutoCAD
    End If
    On Error GoTo xErr
    AcadApp.Visible = True
    If AcadApp.Documents.Count = 0 Then
        AcadApp.Documents.Open ("F:\Engineer\LIBRARY\Templates\spring plate layout template.dwg")
    End If
    AcadApp.LoadDVB ("F:\Engineer\LIBRARY\VBA\SpringPlate.dvb")
    AcadApp.RunMacro "ShowSpringPlateForm_WithPresetValues"
    
    MsgBox "Done!"
    
xErr:
    If Err.Number <> 0 Then
        MsgBox "Error #" & Err.Number & ": " & Err.Description
    End If
    Set AcadApp = Nothing
    Application.StatusBar = False
End Sub

And then here's the AutoCAD macro that is called at the end of the Excel code:

Public Sub ShowSpringPlateForm_WithPresetValues()
    'Note: This subroutine is intended to be run automatically, through VBA in Excel, using the Spring Plate design spreadsheet.
    Dim sTemp As String
    Dim sFile As String

    On Error GoTo xErr

    'Check to see if data file produced from Excel currently exists.
    sFile = "C:\TEMP\SpringPlate.txt"
    If Dir(sFile, vbDirectory) = "" Then
        MsgBox "The data file " & sFile & " was not found. Please note that this macro is intended to be run remotely from the Spring Plate design spreadsheet funcion in MS Excel. Execution will be termintated.", vbExclamation, "File Not Found"
        GoTo xErr
    End If

    'Get data from temporary data file created in Excel, and put the data in the textboxes
    Open sFile For Input As #1
    Input #1, sTemp
    InputF.ShellIDText.Text = sTemp
    Input #1, sTemp
    InputF.ShellThckText.Text = sTemp
    Input #1, sTemp
    InputF.PCDText.Text = sTemp
    Input #1, sTemp
    InputF.BossWidthText.Text = sTemp
    Input #1, sTemp
    InputF.BossDiaText.Text = sTemp
    Input #1, sTemp
    InputF.PinDiaText.Text = sTemp
    Input #1, sTemp
    InputF.SPThckText.Text = sTemp
    Input #1, sTemp
    InputF.NumbText.Text = sTemp
    Input #1, sTemp
    InputF.SPWrapText.Text = sTemp
    Input #1, sTemp
    InputF.ClevisWeldLgthText.Text = sTemp
    Input #1, sTemp
    InputF.ClevisThkText.Text = sTemp
    Input #1, sTemp
    InputF.ClevisWeldSizeText.Text = sTemp
    Input #1, sTemp
    InputF.GearFaceWidthText.Text = sTemp
    Close #1
    Kill sFile  'Delete the temporary data file
    
    InputF.Show 'Show the spring plate form

xErr:
    If Err.Number <> 0 Then
        MsgBox "Error #" & Err.Number & ": " & Err.Description
    End If
End Sub

Anonymous · ‎01-28-2011

The only problem I have now is that the ACadApp.RunMacro command is sychronous, and therefore Excel waits (in locked state) for the user execute the form in AutoCAD. Is there a way to execute the AcadApp.RunMacro command asynchronously - basically sending the command from Excel, and then allowing Excel to finish up and unbind from AutoCAD? I've experimented with the sendcommand function, but I can't get it to do what I need. Any thoughts? Thanks!

Anonymous · ‎01-28-2011

Got it!

AcadApp.ActiveDocument.SendCommand "-VBARUN" & vbCr & "ShowSpringPlateForm_WithPresetValues" & vbCr

Anonymous · ‎02-05-2011

How do u draw in Autocad using excel vba, I am new to using the interface and I would like assistance on how to initiate a macro from excel to create a drawing in autocad

Please help

Community

Run an AutoCad VBA Form from Excel

Run an AutoCad VBA Form from Excel

Run an AutoCad VBA Form from Excel