Using SendKeys VBA command from Excel

Joe Dunfee · January 2017

My VBA is more than a little rusty. I was hoping to use Excel to create a series of commands to draw something in BricsCAD. But, I am having some difficulty. Here is my start to test if I am using the commands properly.

[code]

Sub SendScript()

AppActivate "BricsCAD", True 'this works

'Application.Wait 1000 'I have remarked-out this command, but it does not matter if it is there or not

SendKeys "line 0,0 12,20 {ENTER}", True

MsgBox ("keystrokes have been sent") 'this message box does come up.

End Sub

[/code]

My comments show what the results are. It does activate BricsCAD, but there is no evidence that BricsCAD gets any keystrokes.

I have also tried another version that came up when I search the VBA help;

Application.SendKeys ("line 0,0 12,20 {ENTER}", True)

But, if I include the "True" to indicate I want the Wait option, it generates the error "Expected =".

By the way, I know that there are better methods than using SendKeys, but using that method I can use the knowledge I have about scripting, and don't have to learn the other approaches for each command.

Any ideas what I am doing wrong?

-joe

Roy Klein Gebbinck · January 2017

Using the SendCommand method of the document object is the obvious choice here:
[code]Dim app As AcadApplication
Dim doc As AcadDocument

Set app = GetObject(, "BricsCadApp.AcadApplication")
Set doc = app.ActiveDocument

doc.SendCommand ("_line 0,0 400,400 ")[/code]

Joe Dunfee · January 2017

Thank you very much for the reply, but I cannot get it to work. I imagine I need to load the references for AutoCAD.

I get the error;

Compile error: User-defined type not identified. at the line,

Dim app As AcadApplication

I do try loading all the AutoCAD (there are 4) and BricsCAD references, there are two for my v14 BricsCAD)

Then, I get the error message;

Run-Time error '429': ActiveX component can't create object. at the line,

Set app = GetObject(, "BricsCadApp.AcadApplication")

-Joe

Joe Dunfee · January 2017

Note that I have spent a lot of time in the developer reference, VBA section, trying to find a tutorial or any sort of reference about talking to BricsCAD from VBA running in other programs. But, I cannot find any. It seems to focus exclusively on running VBA within BricsCAD itself. Even then, I cannot find any documentation references, but perhaps it is already loaded if you are using VBA within BricsCAD.

-Joe

Roy Klein Gebbinck · January 2017

The code snippet is a very basic example and requires that BricsCAD is active and that there is an active dwg.
The documentation may be limited but there are many examples out there:
http://www.myengineeringworld.net/2014/10/send-autocad-commands-from-excel-vba.html

Joe Dunfee · January 2017

Thank you for that referral to "My Engineering World" blog. I had actually come across that page before, but had not really gone into it much, because I though I needed to find the correct commands to send to BricsCAD. I last programmed for BricsCAD a number of years ago, when it was not so AutoCAD compatible when using VBA.

His approach uses a number of features that I am not familiar with, but I can read up on them.

On my v14, his routine does not generate perfect results. 1st, it will always open a new instance of BricsCAD, so it apparently needs to be customized for BricsCAD in that regard.

I realize that his routine is supposed to use various types of objects to spell out the name of his blog, "My Engineering World". But, not all of the commands seem to work, because I am missing some letters. I am also puzzled why none of the numeric values show in the command prompt history. Here is part of the command history;

[code]

Start of polyline:

Arc/Distance/Halfwidth/Width/:

Arc/Distance/Follow/Halfwidth/Width/Undo/:

Arc/Close/Distance/Follow/Halfwidth/Width/Undo/:

:

ENTER to use last point/Follow/:

Angle/Length/Undo/:

Angle/Length/Follow/Undo/:

:

ENTER to use last point/Follow/:

Angle/Length/Undo/:

Angle/Length/Follow/Undo/:

:

ENTER to use last point/Follow/:

Angle/Length/Undo/:

Angle/Length/Follow/Undo/:

--end snip---

[/code]

There were no errors shown there either, which makes it hard to debug. But, obviously something is not working in the script on my v14. Attached is a screen grab of what I get.

-Joe

Excel to Bricscad from My Engineering World.JPG

Roy Klein Gebbinck · January 2017

Well it is good to know you got part of it working.

Looking over the link I believe that one of the issues the author was struggling with is the number of 'Enters' a command requires. I would consider a different approach. I would get rid of the 'vbCr' portion of the code and instead use ";" (or any other special string) as the 'Enter' argument and translate that to " " (single space) before sending it to the CAD application. The reason for this solution is that otherwise spaces can be hard to detect.

I do not have Excel installed, but can give you a VBScript (a script language similar to VBA). The script will activate an existing BC session or start a new one if required.

Instructions:
Paste the code in a text file and save using the .vbs extension. Double click the file to start the script.
[code]Dim app, doc

On Error Resume Next

Set app = GetObject(, "BricscadApp.AcadApplication")
If (Err <> 0) Then
Err.Clear
Set app = CreateObject("BricscadApp.AcadApplication")
app.Visible = True
End If

Set doc = app.ActiveDocument
If (Err <> 0) Then
Err.Clear
Set doc = app.Documents.Add
End If

' To escape running command:
' doc.SendCommand(Chr(27) & Chr(27) & ...)

doc.SendCommand("_line 50,50 400,400 ")
doc.SendCommand("(setq myLine (entlast)) ")
doc.SendCommand("_circle 0,0 400 ")
doc.SendCommand("_copy !myLine (entlast) 0,0 800,0 ")

Set doc = Nothing
Set app = Nothing[/code]

Joe Dunfee · February 2017

Thank you for the code example. I am fully jumping into the VBA coding now. However, your example also opens multiple instances of BricsCAD.

I decided to try to simply your start up, and decided I would only proceed if BricsCAD is already running, and just warn the user if it is not.

But, I am getting errors. With BricsCAD already up and running, whether or not there is a document open, my code generates the following error,

ActiveX component can't create object or return reference to this object (Error 429) on the line that says;

Set acadApp = GetObject(, "BricscadApp.AcadApplication")

This is the pertinent part of the code for checking if BricsCAD is running.

[code]

Dim acadApp

Dim acadDoc

On Error Resume Next

Set acadApp = GetObject(, "BricscadApp.AcadApplication")

If (Err <> 0) Then

MsgBox ("BricsCAD must be running. Error:" & Err)

Err.Clear

End

End If

[/code]

Here is the full code for reading a script horizontally across the row. It firsts looks for a cell that says "Start Script" and then keeps going until it finds "End Script". Then it is supposed to send whatever is in the cells between them to BricsCAD.

[code]Option Explicit

'Declaring the Sleep subroutine so I can use it in the program.

#If VBA7 And Win64 Then

'For 64 bit Excel.

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

#Else

'For 32 bit Excel.

Public Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)

#End If

Sub SendScript()

' Finds the 1st column that says "Start Script" and then sends each cell to

' BricsCAD until it finds "End Script".

'

Dim acadApp

Dim acadDoc

Dim CountX As Integer

Dim CountY As Integer

Dim CurrentCol As Integer

Dim CurrentRow As Integer

Dim StartofScript As Integer

Dim EndofScript As Integer

'On Error Resume Next

Set acadApp = GetObject(, "BricscadApp.AcadApplication")

If (Err <> 0) Then

MsgBox ("BricsCAD must be running. Error:" & Err)

Err.Clear

End

End If

'proceed with the process

Set acadDoc = acadApp.Documents.Add

acadDoc.SendCommand (Chr(27) & Chr(27)) ' To cancel any running command in BricsCAD

'cycle through the cells of the script

CurrentRow = ActiveCell.Row 'set current row

'find the 1st and last command by searching for "Start Script"

CountX = 1

Do

If Cells(CurrentRow, CountX) = "Start Script" Then

StartofScript = CountX + 1

ElseIf Cells(CurrentRow, CountX) = "End Script" Then

EndofScript = CountX - 1

CountX = 200

End If

CountX = CountX + 1

Loop While CountX < 200

'check if there is a valid start and end of script marker. If not end the program.

If StartofScript < 5 And EndofScript < 8 Then

MsgBox ("the text 'Start Script' indicates the start of a scriput. But it is not found in this row. Program ended")

End

End If

'Loop through the script and send to BricsCAD

For CountX = StartofScript To EndofScript

acadDoc.SendCommand (Cells(CurrentRow, CountX).Value)

Sleep 20 'pause the program for a few milliseconds to make sure BricsCAD has time to process

Set acadDoc = Nothing

Set acadApp = Nothing

End Sub[/code]

-Joe

Joe Dunfee · February 2017

I haven't had much luck with the "insert code" tags. And there is no preview button to check what it should look like on the forum. Am I doing something wrong, or is it just not working reliably on this forum?

When I click on the "insert code" button, I get what you see in the attached screen grabs.

I have inserted "zzz" to avoid the html actually doing the formatting.

[zzzcode]PASTE CODE HERE[zzz/code]

[zzzcode]

this is my code

[zzz/code]

Though sometimes I have done it this way.

[zzzcode]

this is my code[zzz/code]

What am I doing wrong?

-Joe

paste code.JPG

Roy Klein Gebbinck · February 2017

Code tags test:
[code](setq str "\nWill this work? "[/code]

Roy Klein Gebbinck · February 2017

Well, I have no problem posting code (I did forget a closing parenthesis though).

I have retested the .vbs script and in my tests it works exactly as I have described (V14 on Windows 32 and 64 bit). Maybe something is wrong with your installation causing the COM interface to not work properly.
Judging from your post #8 it seems the .vbs script has actually lead to confusion. If so, please disregard it and return to the example in the link which you managed to get partially working. Sorry, but I can't offer much more assistance (as mentioned I do not use Excel myself).

terry priest · February 2017

I also got my first code connect example from my engineering world. I strictly use excel to run autocad or bricsys vba. it works very well. I create a couple global variables and insert this standard module into every project. this one is changed from autocad to bricscad. of course you do have to TOOLS REFERENCE the BricsCad Library (maybe two? I don't have it in front of me)

[ code]

Public acadApp As AcadApplication
Public acadDoc As AcadDocument

Sub connect_acad_brix()
'if autocad is open, obtain a reference to the autocad application
'if autocad is not open, open autocad and obtain a reference to it
'check that the application object is set
'if there is an active drawing, obtain a reference to it
'if there is no active drawing, create one and obtain a reference
On Error Resume Next
Set acadApp = GetObject(, "BricscadApp.AcadApplication")
'if autocad not running then error - component cannot create object
If acadApp Is Nothing Then
Set acadApp = New AcadApplication
acadApp.Visible = True
End If

If acadApp Is Nothing Then
    MsgBox "Autocad did not start"
    Exit Sub
End If
On Error GoTo 0

On Error Resume Next
Set acadDoc = acadApp.ActiveDocument
If acadDoc Is Nothing Then
    Set acadDoc = acadApp.Documents.Add
    acadApp.Visible = True
End If
On Error GoTo 0

If acadDoc.ActiveSpace = 0 Then
   acadDoc.ActiveSpace = 1
End If

End Sub

[/code]

so in sample code anywhere it says THISDRAWING change it to ACADDOC. run CONNECT_ACAD_BRIX one time at the head of your routine.

I am having trouble previewing code correctly, I will post and hope it is decipherable, should be two globals followed by one sub.

Joe Dunfee · February 2017

Is this code designed to work from inside BricsCAD, or from another application like Excel?

-Joe

terry priest · February 2017

excel

Joe Dunfee · February 2017

Terry, thank you for the example. Especially for the instructions on how to adapt an existing routine designed for AutoCAD.\

I do however have some inconsistent behavior. But, I think it is because I am making several attempts to test things while Excel stays open, but I open and close BricsCAD, or create and close drawings. I do fully stop the Excel VBA, which should reset the variables. But, I really don't have enough knowledge of VBA to diagnose what is going on.

-Joe

Joe Dunfee · February 2017

Terry, I am not certain I am understanding part of how your routine should be used.

I vaguely recall that it is possible to put some optional code lines in a module that will always run. But, I can't recall how that is done, and today I have not been able to find an internet tutorial that mentions it. Following that code, there is always the main loop, plus optional sub-routines.

Should your code example should be used in the area that is outside the main routine?

Currently, I am simply removing the line,
Sub connect_acad_brix()
and the line
End Sub

Then, pasting the middle part into my existing sub routine that transfers script commands from Excel

-Joe

terry priest · February 2017

you can initialize everything in one sub, or you can create some public variables and initialize as a separate sub routine. either way both excel and autocad are running at the same time. there is no exe or class module in autocad, its old style where you are running a routine in excel that is running cad. typically you would have a form in excel, but it runs fine just from the module.

first step is to add the bricscad library under tools reference.

then somewhere you create this, whether public or dim

Public acadApp As AcadApplication
Public acadDoc As AcadDocument

then you would call the connect acad routine,

I have extensively run this with autocad and tested and ran briefly but entirely predictably with bricscad.

at that point if those variables are visible to your routine you can use them and intellisense will show you they have objects and methods available.

I have a wordpress blog, not trying to promote, its not maybe that readable, google frfly autocad vba excel.

Using SendKeys VBA command from Excel

Comments