TheSwamp

Code Red => VB(A) => Topic started by: MSTG007 on May 21, 2021, 09:21:13 AM

Title: Sending Cell values to AutoCAD
Post by: MSTG007 on May 21, 2021, 09:21:13 AM
I am trying to find a way to send a cell value to a activated command at the command line.

Basically, I have a excel file with structure names in Column A1- A20. I want to select the cell of A2 and send that cell value to a lisp routine which is ready to go at the command line with the A2 value.

I am having a dog of a time trying to piece all of this together to work. So the code i have with the Excel Macro is the following:

Excel file is saved as a xlsm. with the References - VBAProject as (AutoCAD 2019 Type Library)
Code: [Select]
Sub Zoom2Structure()
 On Error Resume Next
 Set AcadApp = GetObject(, "AutoCAD.Application")
 If Err Then
 Err.Clear
 Set AcadApp = CreateObject("AutoCAD.Application")
 End If
 AppActivate AcadApp.Caption
 AcadApp.Visible = True
 AcadApp.Application.WindowState = acNorm
 AcadApp.ActiveSpace = acModelSpace
 If AcadApp.Documents.Count = 0 Then
 AcadApp.Documents.Add
 End If
 AcadApp.ActiveDocument.SendCommand "zm2st" & vbCr
End Sub

The following lisp routine which works.
Code: [Select]
(defun c:zm2st (/         C3D       C3DDOC    LOCATION  NTWRK
                NTWRKS    PROD      PRODSTR   PT        STRC
                STRCNAME  STRUCTURES
               )
  (vl-load-com)
  (if (setq C3D    (strcat "HKEY_LOCAL_MACHINE\\"
                           (if vlax-user-product-key
                             (vlax-user-product-key)
                             (vlax-product-key)
                           )
                   )
            C3D    (vl-registry-read C3D "Release")
            C3D    (substr
                     C3D
                     1
                     (vl-string-search "." C3D (+ (vl-string-search "." C3D) 1))
                   )
            C3D    (vla-getinterfaceobject
                     (vlax-get-acad-object)
                     (strcat "AeccXUiPipe.AeccPipeApplication." C3D)
                   )
            C3Ddoc (vla-get-activedocument C3D)
      )
    (progn
      (setq ntwrks (vlax-get c3ddoc 'pipenetworks))
      (setq strcname (getstring "\nStructure name to zoom to: " t))
      (vlax-for ntwrk ntwrks
        (if (not strc)
          (progn
            (vl-catch-all-apply
              '(lambda ()
                 (setq structures (vlax-get ntwrk 'structures))
                 (setq strc (vlax-invoke structures 'item strcname))
               )
              '()
            )
          )
        )
      )
      (if strc
        (progn
          (setq location (vlax-get strc 'position))
          (setq pt (list (vlax-get location 'x) (vlax-get location 'y)))
          (command "zoom" "c" pt "40")
        )
        (progn
          (princ (strcat "\nStructure \"" strcname "\" not found."))
        )
      )
    )
  )
  (princ)
)
 

Its great when you can reference old posts lol. Here are the two that the examples came from.

http://www.theswamp.org/index.php?topic=55042.0 (http://www.theswamp.org/index.php?topic=55042.0)

http://www.theswamp.org/index.php?topic=50025.0 (http://www.theswamp.org/index.php?topic=50025.0)

Thank you for any guidance! Again, i have no clue how to make these functions to work together.


Title: Re: Sending Cell values to AutoCAD
Post by: n.yuan on May 22, 2021, 09:23:16 AM
Since your question is about "SEND" value from Excel VBA to a executing LIST of running AutoCAD, started by Acad VBA's "SendCommand" statement, I assume you already know how to get the cell value in the Excel sheet.

This easiest way is to save the value in one of AutoCAD's user system variable (USERI1-5, USERR1-5 or USERS1-5) with the VBA code, and then retrieve the value in the LISP code (which replace the LISP code of asking user for inputting "Structure Name"). In your code, since the cell value is a text value (Structure Name), you would use USERS1, or USERS2...5.

The VBA code change would like:

Code - vb.net: [Select]
  1. Sub Zoom2Structure(strucName As String)
  2.  On Error Resume Next
  3.  Set AcadApp = GetObject(, "AutoCAD.Application")
  4.  If Err Then
  5.  Err.Clear
  6.  Set AcadApp = CreateObject("AutoCAD.Application")
  7.  End If
  8.  AppActivate AcadApp.Caption
  9.  AcadApp.Visible = True
  10.  AcadApp.Application.WindowState = acNorm
  11.  AcadApp.ActiveSpace = acModelSpace
  12.  If AcadApp.Documents.Count = 0 Then
  13.  AcadApp.Documents.Add
  14.  End If
  15.   '' Add this line
  16.  ThisDrawing.SetVariable "USERS1", strucName
  17.  AcadApp.ActiveDocument.SendCommand "zm2st" & vbCr
  18. End Sub

Then, in your LISP code:

Code - Auto/Visual Lisp: [Select]
  1. (defun c:zm2st (/         C3D       C3DDOC    LOCATION  NTWRK
  2.                 NTWRKS    PROD      PRODSTR   PT        STRC
  3.                 STRCNAME  STRUCTURES
  4.                )
  5.   (if (setq C3D    (strcat "HKEY_LOCAL_MACHINE\\"
  6.                            (if vlax-user-product-key
  7.                              (vlax-user-product-key)
  8.                              (vlax-product-key)
  9.                            )
  10.                    )
  11.             C3D    (vl-registry-read C3D "Release")
  12.             C3D    (substr
  13.                      C3D
  14.                      1
  15.                      (vl-string-search "." C3D (+ (vl-string-search "." C3D) 1))
  16.                    )
  17.             C3D    (vla-getinterfaceobject
  18.                      (vlax-get-acad-object)
  19.                      (strcat "AeccXUiPipe.AeccPipeApplication." C3D)
  20.                    )
  21.             C3Ddoc (vla-get-activedocument C3D)
  22.       )
  23.     (progn
  24.       (setq ntwrks (vlax-get c3ddoc 'pipenetworks))
  25.  
  26.       ;; Comment this line out
  27.       ;;(setq strcname (getstring "\nStructure name to zoom to: " t))
  28.  
  29.       ;; add this line
  30.       (setq strucname (getvar "USER1"))
  31.       ... ...
  32.       ... ...  
  33. )



EDIT (John): Added code tags.
Title: Re: Sending Cell values to AutoCAD
Post by: MSTG007 on May 24, 2021, 01:33:37 PM
Thank you for your help on this! I just have a few questions if you do not mind. Within the Excel VBA side. I am unable to run the Zoom2Structure macro  unless I removed the
Code: [Select]
Sub Zoom2Structure(strucName As String) to be
Code: [Select]
Sub Zoom2Structure(). I was then able to select the macro from the menu. It does activate the lisp CAD but it would not push the selected cell value through.

Within the Lisp, does the
Code: [Select]
  '' Add this line
 ThisDrawing.SetVariable "USERS1", strucName
 AcadApp.ActiveDocument.SendCommand "zm2st" & vbCr
USERS1 code,

Need to match the
Code: [Select]
(setq strucname (getvar "USER1")) "USERS1"

Below is the what I currently have setup.

VBA
Code: [Select]
Sub Zoom2Structure(structName As String)
 On Error Resume Next
 Set AcadApp = GetObject(, "AutoCAD.Application")
 If Err Then
 Err.Clear
 Set AcadApp = CreateObject("AutoCAD.Application")
 End If
 AppActivate AcadApp.Caption
 AcadApp.Visible = True
 AcadApp.Application.WindowState = acNorm
 AcadApp.ActiveSpace = acModelSpace
 If AcadApp.Documents.Count = 0 Then
 AcadApp.Documents.Add
 End If
  '' Add this line
 ThisDrawing.SetVariable "USERS1", strucName
 AcadApp.ActiveDocument.SendCommand "zm2st" & vbCr
End Sub

LISP
Code: [Select]
(defun c:zm2st (/         C3D       C3DDOC    LOCATION  NTWRK
                NTWRKS    PROD      PRODSTR   PT        STRC
                STRCNAME  STRUCTURES USERS1
               )
  (vl-load-com)
  (if (setq C3D    (strcat "HKEY_LOCAL_MACHINE\\"
                           (if vlax-user-product-key
                             (vlax-user-product-key)
                             (vlax-product-key)
                           )
                   )
            C3D    (vl-registry-read C3D "Release")
            C3D    (substr
                     C3D
                     1
                     (vl-string-search "." C3D (+ (vl-string-search "." C3D) 1))
                   )
            C3D    (vla-getinterfaceobject
                     (vlax-get-acad-object)
                     (strcat "AeccXUiPipe.AeccPipeApplication." C3D)
                   )
            C3Ddoc (vla-get-activedocument C3D)
      )
    (progn
      (setq ntwrks (vlax-get c3ddoc 'pipenetworks))
      ;;(setq strcname (getstring "\nStructure name to zoom to: " t))

      (setq strucname (getvar "USERS1"))     

      (vlax-for ntwrk ntwrks
        (if (not strc)
          (progn
            (vl-catch-all-apply
              '(lambda ()
                 (setq structures (vlax-get ntwrk 'structures))
                 (setq strc (vlax-invoke structures 'item strcname))
               )
              '()
            )
          )
        )
      )
      (if strc
        (progn
          (setq location (vlax-get strc 'position))
          (setq pt (list (vlax-get location 'x) (vlax-get location 'y)))
          (command "zoom" "c" pt "40")
        )
        (progn
          (princ (strcat "\nStructure \"" strcname "\" not found."))
        )
      )
    )
  )
  (princ)
)
(C:ZM2ST)

Thanks again for your help! This is definitely cool stuff when it works!
Title: Re: Sending Cell values to AutoCAD
Post by: 57gmc on May 24, 2021, 03:27:48 PM
As you found out, a Sub can't accept an argument. Change the sub to a Function. And yes, both pieces of code need to refer to the same variable, "USERS1".
Title: Re: Sending Cell values to AutoCAD
Post by: MSTG007 on May 24, 2021, 04:21:29 PM
Could elaborate on that? its not as easy as just changing the sub to function. lol i tried. How would it be activated to run?
Title: Re: Sending Cell values to AutoCAD
Post by: 57gmc on May 24, 2021, 05:03:22 PM
yes, just change the keyword Sub to Function. don't forget the end statement. A function cannot be called directly as a command. It must be called from a Public Sub. The intent is that you would have logic in the Sub to determine what the contents of the argument of the Function should be. The structure could be a simple as:
Code - vb.net: [Select]
  1.  Public Sub Test ()    
  2.    Call MyFunction("test string")
  3. End Sub
  4.  
  5. Public Function MyFunction(str As String)
  6.     Debug.Print str
  7. End Function
Title: Re: Sending Cell values to AutoCAD
Post by: MSTG007 on May 25, 2021, 07:45:00 AM
Excellent! I think I have that part of the code working from what I can see.


This is the VBA,  I select the cell value of A20 "10", then select the button in the ribbon. It now activates CAD.
Code: [Select]
Public Sub Test()
   Call MyFunction("test string")
End Sub
 
Public Function MyFunction(str As String)
    Debug.Print str
End Function


Public Sub Z2S(call as iRibbon)
    Call Z2STR("structName String")
End Sub

Public Function Z2STR(structName As String)
 On Error Resume Next
 Set AcadApp = GetObject(, "AutoCAD.Application")
 If Err Then
 Err.Clear
 Set AcadApp = CreateObject("AutoCAD.Application")
 End If
 AppActivate AcadApp.Caption
 AcadApp.Visible = True
 AcadApp.Application.WindowState = acNorm
 AcadApp.ActiveSpace = acModelSpace
 If AcadApp.Documents.Count = 0 Then
 AcadApp.Documents.Add
 End If
  '' Add this line
 ThisDrawing.SetVariable "USERS1", strucName
 AcadApp.ActiveDocument.SendCommand "zm2st" & vbCr
End Function

However with the revised code with the added "USERS1" variable seems to error out the lisp side.

Code: [Select]
(defun c:zm2st (/         C3D       C3DDOC    LOCATION  NTWRK
                NTWRKS    PROD      PRODSTR   PT        STRC
                STRCNAME  STRUCTURES USERS1
               )
....
      (setq ntwrks (vlax-get c3ddoc 'pipenetworks))

      ;;(setq strcname (getstring "\nStructure name to zoom to: " t))

      (setq strucname (getvar "USERS1"))     

      (vlax-for ntwrk ntwrks
....

Code: [Select]
command:.... ; error: bad argument type: stringp nil
getting closer lol.


Title: Re: Sending Cell values to AutoCAD
Post by: 57gmc on May 25, 2021, 10:30:15 AM
In your vba function argument, you designate the argument as "structName", but when you go to use it in the SetVariable statement, you call it "strucName". The latter is missing a "t".
Title: Re: Sending Cell values to AutoCAD
Post by: MSTG007 on May 25, 2021, 12:02:08 PM
 :no: lol. great catch. I changed  "strcname" so they are the same in both lsp and vba.

I was able to execute the command and now i get the following:

Code: [Select]
Structure "" not found.
from this area lsp
....
(princ (strcat "\nStructure \"" strcname "\" not found.")

Within the VBA, what piece of the code calls for the cell which is selected to be copied into memory?
Could that be the last step I am missing?

Code: [Select]
....
ThisDrawing.SetVariable "USERS1", strcname
....
Title: Re: Sending Cell values to AutoCAD
Post by: 57gmc on May 25, 2021, 04:26:44 PM
I have no idea what you’re doing on the Excel side. You have to make sure you’re passing a valid string. Your Z2S sub is invalid. A sub can’t have an argument and you don’t use the argument in the sub anyway.
Title: Re: Sending Cell values to AutoCAD
Post by: MSTG007 on May 25, 2021, 09:35:08 PM
Honestly, if I have a text value of 10 in A1. I want to pass that cell value over to the routine. I would assume if A1 had a formula in it, it would take that value and pass it through. I hope I’m answering this right. Lol
Title: Re: Sending Cell values to AutoCAD
Post by: 57gmc on May 26, 2021, 10:32:53 AM
Honestly, if I have a text value of 10 in A1. I want to pass that cell value over to the routine. I would assume if A1 had a formula in it, it would take that value and pass it through. I hope I’m answering this right. Lol
Sure, that's doable, but you haven't shown all your code for that part of the task. You just showed ".......". You could set a breakpoint in your xl vba and step through execution to see where it's failing to send the cell value.
Title: Re: Sending Cell values to AutoCAD
Post by: MSTG007 on May 26, 2021, 10:53:35 AM
Just sent you a PM
Title: Re: Sending Cell values to AutoCAD
Post by: 57gmc on May 26, 2021, 03:58:29 PM
You can post your code or attach an xlsm.
Title: Re: Sending Cell values to AutoCAD
Post by: MSTG007 on May 26, 2021, 05:39:28 PM
Sounds good. Please see the attached and thanks again!
Title: Re: Sending Cell values to AutoCAD
Post by: 57gmc on May 27, 2021, 11:45:32 AM
You don't have any code that selects a range to get a cell's Value. When programming, you have to tell the application how to do every little step. It's like a recipe. You can't skip any steps. In good programming practice, it's common to first draw a flowchart of all the steps you need to perform. Think it out first before you start coding.

Right now, I don't have time to teach you Excel coding or to write all the code for you. But here is a place (https://docs.microsoft.com/en-us/office/vba/api/overview/excel) you can start to educate yourself. The Concepts (https://docs.microsoft.com/en-us/office/vba/excel/concepts/miscellaneous/concepts-excel-vba-reference) section gives you samples of how to do common things.
Title: Re: Sending Cell values to AutoCAD
Post by: MSTG007 on May 27, 2021, 12:38:16 PM
Sounds good. Let me dig into this and see what I can come up with. I will hopefully get this figured out! Thank though, for everything.
Title: Re: Sending Cell values to AutoCAD
Post by: MSTG007 on May 27, 2021, 05:24:01 PM
I have added code to copy the cell value. I think I am lost when taking the cell value into the USERS1 variable.

Again, I hope I am heading the right direction on this. lol.

Code: [Select]
Option Explicit


Public Sub PasteCurrentCell()

    Dim sh As Excel.Worksheet
    Dim rng As Excel.Range
   
    Set sh = GetExcelSheet()
    If sh Is Nothing Then
        MsgBox "Excel is not running, or" & vbCrLf & _
        "opened Excel file does not have ""SHEET1""."
        Exit Sub
    End If
   
    Set rng = sh.Range("A1") '<<<<<---- How can I make this select the current active cell?? it could be in any column or row.
    rng.Copy
   
    InsertCurrentCellValue

End Sub

Private Function GetExcelSheet() As Excel.Worksheet

    Dim theSheet As Excel.Worksheet
    Dim sh As Excel.Worksheet
    Dim xls As Excel.Application
   
    On Error Resume Next
   
    Set xls = GetObject(, "Excel.Application")
    If Not xls Is Nothing Then
   
        For Each sh In xls.ActiveWorkbook.Worksheets
            If UCase(sh.Name) = "SHEET1" Then
                Set xls.ActiveSheet = sh
                Set theSheet = sh
                Exit For
            End If
        Next
       
    End If
   
    Set GetExcelSheet = theSheet
   
End Function

Private Sub InsertCurrentCellValue()
 On Error Resume Next
 Set AcadApp = GetObject(, "AutoCAD.Application")
 If Err Then
 Err.Clear
 Set AcadApp = CreateObject("AutoCAD.Application")
 End If
 AppActivate AcadApp.Caption
 AcadApp.Visible = True
 AcadApp.Application.WindowState = acNorm
 AcadApp.ActiveSpace = acModelSpace
 If AcadApp.Documents.Count = 0 Then
 AcadApp.Documents.Add
 End If
    ThisDrawing.SetVariable "USERS1", strcname
    AcadApp.ActiveDocument.SendCommand "zm2st" & vbCr
   
End Sub
Title: Re: Sending Cell values to AutoCAD
Post by: 57gmc on May 28, 2021, 10:37:54 AM
This is where you need to learn more. You can't always just paste code samples and use them as they are. This code is meant to run inside AutoCAD's VBA, not Excel. Which is probably a better place to do this anyway. You need to read the code and understand what it's doing. Then you can use the parts that you actually need.
A quick internet search for "excel get active cell" turned this (https://www.extendoffice.com/documents/excel/2497-excel-get-active-cell.html#:~:text=Get%20address%20of%20active%20cell%20with%20VBA%20code,the%20address%20of%20the%20active%20cell%20listed%20inside.) up as the first hit.
Title: Re: Sending Cell values to AutoCAD
Post by: MSTG007 on June 07, 2021, 08:11:06 AM
I wanted to give you an update. I was able to dig around this weekend and look up some of the things you mentioned. With that, I was able to figure out how to send a activecell value to autocads command line and simplify the code.

I did have one more question on the AutoCAD side of things. The lisp I am calling (C:z2s). I am having trouble "hardcoding" that in the following line. I know its not shown correctly below, but that is the intent I hoping for. lol.

Code: [Select]
    If Val(acadApp.Version) < 20 Then
        Else
             acadDoc.SendCommand '''z2S''' acadCmd & vbCr
        End If       


Code: [Select]
Sub Commands()
    Dim acadApp     As Object
    Dim acadDoc     As Object
    Dim acadCmd     As String
    Dim sht         As Worksheet
   
    Set sht = ThisWorkbook.Sheets("Sheet1")
   
    With sht
        .Activate
         Set Rng = ActiveCell
    End With
       
    On Error Resume Next
    Set acadApp = GetObject(, "AutoCAD.Application")
    If acadApp Is Nothing Then
        Set acadApp = CreateObject("AutoCAD.Application")
        acadApp.Visible = True
    End If
    On Error Resume Next
    Set acadDoc = acadApp.ActiveDocument
    If acadDoc Is Nothing Then
        Set acadDoc = acadApp.Documents.Add
    End If
    On Error GoTo 0
   
    acadCmd = ""
        If Not IsEmpty(ActiveCell.Value) Then
             acadCmd = acadCmd & ActiveCell.Value & vbCr
        End If

    If Val(acadApp.Version) < 20 Then
        Else
             acadDoc.SendCommand acadCmd & vbCr
        End If         
End Sub

again thanks for your time!
Title: Re: Sending Cell values to AutoCAD
Post by: 57gmc on June 08, 2021, 06:26:51 PM
You must have modified (z2s), because what you have posted before (z2s) didn't have any arguments. Assuming that it does, it should look like this.
Code - Visual Basic: [Select]
  1. acadDoc.SendCommand "(z2s " & acadCmd & ")" & vbcr
Title: Re: Sending Cell values to AutoCAD
Post by: MSTG007 on June 10, 2021, 07:17:29 AM
Ok. That make alot more sense then what I cam up with. lol.

Again, thank you for the help! I think that takes care of my problem!