Formula Property Definition and Excel

[Delforge]

Hello,

I am working to Property Set. I want to read Excel to determinate the Property.
I have make a Formula Property Definition but it doesn't work.

Where is a problem with this line : Set wb = Workbooks.Open(Filename:=Chemin & Feuille, corruptload:=xlRepairFile)

Do you know why ?

My code :

Dim Chemin
Dim Feuille
Dim wb 
Chemin = "C:\Users\QuentinD\Documents\vba_autocad\BIM\excel\"
Feuille = "BIM_v2.xls"
Set wb = Workbooks.Open(Filename:=Chemin & Feuille, corruptload:=xlRepairFile)
RESULT=wb.ActiveSheet.Cells(2, 1).Value

[norman.yuan · Accepted solution]

Probably your question might get better attention if you post in forum for AutoCAD Architecture/Civil3D discussion where PropertySet is used.

I do not have much experience of using PropertySet, but do know a little bit of it. Here is what my thought on your question:

1. In your VBScript code (as the property's formular source), the code DOES NOT know what WorkBooks is in this line:

Set wb = WorkBooks.Open(....)

You need to obtain Excel.Application object first, and then use its property "WorkBooks" to open *.xls/*.xlsx file. So, you need to use the combination of CreateObject()/GetObject() to test if Excel application is running, if not, start it. Then test if the *.xls file is already open, if not, open it. Well, this whole thing would make the code a lot more complicated.

2. From my test, it seems that if Excel application, as external app, is not running, it simply cannot be used as formular property source, even you have VBScript code to start Excel app, such as 

Set xlsApp=CreateObject("Excel.Application")

While you are writing this code in the Formular composing window, Excel would be started. After the formular is saved, and the property is attached to an AutoCAD entity, whenever the entity is selected, if Excel is not running, you would see AutoCAD is temporarily frozen/busy, waiting Excel to be started. Yet, after Excel started (in the background, not visible. But you can see it in the Task Manager), it shut down automatically.

So, from what I can see, using an external application as formular property source is a bad idea. If you have to use it, the external application should be running. The VBScript code, shown in attached picture works for me.

As you can see, I use GetObject("Excel.Application") to obtain running Excel application, which already has the XLS file open, then my code can go ahead to the desired worksheet/cell for the required value. I also firstly set a default value to the RESULT and then use "On Error Resume Next". This way, if the Excel app is not running, the formular would still has its default value. So, this formular property works OK in my Civil3D: if Excel is not running, the PROP2 always 0; if Excel runs with the desired sheet open, it get the value; if I change the value in the cell of that sheet, the Prop2 value of the Acad entity changes accordingly.

Again, in most cases, I'd not use external application as formular source

HTH

[Delforge]

Than you for your reponse.

Finally, I have make an array in the code for not read the excel.

It is not easy to create an array. If someone has this question, this code doesn't work :

Dim Tableau(1 To 4, 1 To 50) As String

We have tu use the fonction

Array()