Author Topic: Sending Cell values to AutoCAD  (Read 16193 times)

0 Members and 1 Guest are viewing this topic.

MSTG007

  • Gator
  • Posts: 2598
  • I can't remeber what I already asked! I need help!
Sending Cell values to AutoCAD
« 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=50025.0

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


Civil3D 2020

n.yuan

  • Bull Frog
  • Posts: 348
Re: Sending Cell values to AutoCAD
« Reply #1 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.
« Last Edit: May 24, 2021, 09:40:55 AM by John Kaul (Se7en) »

MSTG007

  • Gator
  • Posts: 2598
  • I can't remeber what I already asked! I need help!
Re: Sending Cell values to AutoCAD
« Reply #2 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!
Civil3D 2020

57gmc

  • Bull Frog
  • Posts: 358
Re: Sending Cell values to AutoCAD
« Reply #3 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".
« Last Edit: May 24, 2021, 03:31:23 PM by 57gmc »

MSTG007

  • Gator
  • Posts: 2598
  • I can't remeber what I already asked! I need help!
Re: Sending Cell values to AutoCAD
« Reply #4 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?
Civil3D 2020

57gmc

  • Bull Frog
  • Posts: 358
Re: Sending Cell values to AutoCAD
« Reply #5 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
« Last Edit: May 25, 2021, 10:30:42 AM by 57gmc »

MSTG007

  • Gator
  • Posts: 2598
  • I can't remeber what I already asked! I need help!
Re: Sending Cell values to AutoCAD
« Reply #6 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.


Civil3D 2020

57gmc

  • Bull Frog
  • Posts: 358
Re: Sending Cell values to AutoCAD
« Reply #7 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".

MSTG007

  • Gator
  • Posts: 2598
  • I can't remeber what I already asked! I need help!
Re: Sending Cell values to AutoCAD
« Reply #8 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
....
Civil3D 2020

57gmc

  • Bull Frog
  • Posts: 358
Re: Sending Cell values to AutoCAD
« Reply #9 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.
« Last Edit: May 25, 2021, 04:33:05 PM by 57gmc »

MSTG007

  • Gator
  • Posts: 2598
  • I can't remeber what I already asked! I need help!
Re: Sending Cell values to AutoCAD
« Reply #10 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
Civil3D 2020

57gmc

  • Bull Frog
  • Posts: 358
Re: Sending Cell values to AutoCAD
« Reply #11 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.

MSTG007

  • Gator
  • Posts: 2598
  • I can't remeber what I already asked! I need help!
Re: Sending Cell values to AutoCAD
« Reply #12 on: May 26, 2021, 10:53:35 AM »
Just sent you a PM
Civil3D 2020

57gmc

  • Bull Frog
  • Posts: 358
Re: Sending Cell values to AutoCAD
« Reply #13 on: May 26, 2021, 03:58:29 PM »
You can post your code or attach an xlsm.

MSTG007

  • Gator
  • Posts: 2598
  • I can't remeber what I already asked! I need help!
Re: Sending Cell values to AutoCAD
« Reply #14 on: May 26, 2021, 05:39:28 PM »
Sounds good. Please see the attached and thanks again!
Civil3D 2020