LINKING EXCEL WITH ACAD BLOCKS

[MikeEng]

Is there a simple way to assign attributes blocks using an Exel reference?

I've attached a dwg with a block that has different pieces of info on it. I'd like to just put the Detail Reference in and then run a VBA and it would put the rest of the info in for me by referencing an Excel spreadsheet. That way I can set up the formulas on the spreadsheet and let VBA fill in the attributes.

[arcticad]

'This will retrieve values from the first 3 lines in an excel file
'The tab is labeled "DATA"
'Then you can assign D1 to the textstring of the attribute.

Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
Private Const WM_CLOSE = &H10

Function getExcelData() As Collection
Dim xlsFile As String
xlsFile = "c:\test.xls"

Dim ExcelTab As String
ExcelTab = "DATA"

Dim D1, D2, D3

Dim xlWB As Excel.Workbook
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
Dim lngHwnd As Long
lngHwnd = FindWindow("XLMAIN", objExcel.Caption)
Set xlWB = objExcel.Workbooks.Open(Notify:=False, FileName:=xlsFile, Readonly:=True)

For Each item In xlWB.Sheets
If UCase(item.name) = UCase(ExcelTab) Then ' Set the correct sheet
item.Activate
Set app = objExcel.Application.ActiveSheet
D1 = app.Cells(1, 1).value ' get values from the first 3 lines
D2 = app.Cells(2, 1).value
D3 = app.Cells(3, 1).value
Exit For
End If
Next

On Error GoTo theend
xlWB.Close ' Close Sheet
objExcel.Quit ' Try to close Excel
Set objExcel = Nothing ' Clear Reference
SendMessage lngHwnd, WM_CLOSE, 0, 0 ' Force Excel to Close if still open

theend:
End Function

[MikeEng]

Thanks for that...

I could probably figure this out from here but I'm afraid my method would not be... "the most elegant.." My question/problem is ths:

With the help of your code we have data from Excel in Acad VBA code with values D1, D2, D3, Dn....

What is the best way to say..

if the block name is "THIS_BLOCK" then ' we are looking at the right block type (the one that we want to set)

if THIS_BLOCK = A then
make Attribute 1 = D1 (from xls)
make Attribute 2 = D2 (from xls)
make Attribute 3 = D3 (from xls)
make Attribute n = Dn (from xls)
End if

Is there a way that you can ref the block attributes specifically? For instance, once you get ahold of the block that you want to set attributes, can you effectivley say:

block("THIS_BLOCK").attribute("DATA1").tagstring = D1 (from xls)??

That would be nice and clean. The alternative is a bit.. 'loopy'

Thanks,
MSR

[Anonymous]

Try it, Mike

~'J'~

[MikeEng]

We posted at about the same time....

I'm in your file now... THANKS!!

MSR

[Anonymous]

Let me know If you need
some changes there 🙂

~'J'~

[Anonymous]

Don't know if you know this but you can export block information to a tab delimited text file and open it up in excel, make changes to the file save it and reload it in auto cad using the express tools if you have them.

-Jakob Message was edited by: JMarasch

[MikeEng]

I have been messing with this but I need some help.
Some of thei following works ok in the attached bas file.

I know how I want this to work but I just need more VBA practice...

I would like to set up a spreadsheet with the attribute properties and then use VBA in ACad to set the attributes

The rules of the spreadsheet are:

1 - The spreadsheet is always named: "BlockAttData.xls"

2 - The spreadsheet is located in the same folder as the drawing that the command is invoked.

3 - The worksheets are titled with the name of the blocks they will be used to set.
ie. Worksheet "BOM" will contain the attribute parameters for the "BOM" blocks in CAD.

4 - If this helps: The refereence cell will always be 'named' "BlockName" & "_REF"

-----------------------

Here is how the command works (in my mind - I know what I want it to do! 🙂

in cad - run the macro

the excel spreadsheet is loaded. (unseen, read only)
If the spreadsheet is not found message box; exit.

Acad prompts: "Select Block: "
user selects block

VBA takes block name and activates the appropriate worksheet in the opened workbook
if w-sheet not found; msgbox and exit.

looks at the attribute name in the B2 column of the workbook and then gets that tagstring of the selected block.

Use excel vlookup function or however ?? to look up the value in the acad block and then assign the rest of the attributes acccording to the excel spreadsheet.

If the variable is not defined then msgbox: "Attributes for " block.tag " not defined in worksheet. Define them?"
Y - VBA inputs the attributes into the next open row of excel and resorts the table so that the vlookup works.
N - Restart command

The command runs itself again until you press escape.

Seems like this is something you could do with you 'magic' 🙂

[Anonymous]

Okay,
Give me a time to rewrite it
by this suit

Later,

~'J'~

[Anonymous]

Here is first part of project
you need
I want to get some explantions
My address at very bottom of the
code in module

~'J'~

[MikeEng]

-J-,

Hey thanks for looking at this. I revised the .dwg and the .xls to make it a little simpler during the dev.

When I run the macro - it isn't finding the file 'BlockAttData.xls'

The test for this is thing working is:

1) run the macro from cad and it finds & loads the xls file.
2) Command line says: "Select Block: "
3) When you select the BOM block it will fill in the attributes for the BOM block according to the attribute value listed in col.B of the xls file in the worksheet titled with the block name.

EXAMPLE : For the files that I sent you.

Click on the BOM block it will select the BOM worksheet and then note that the attribute to reference is MARK b/c it is the title of attribute in B2. It then sets the other attributes to:

MARK PCS DESC LENGTH REMARK
a1 1 L4"x3"x5/16" 1'-3" a1 TEST

If the block attribute would have been a4 then it would set them to:

MARK PCS DESC LENGTH REMARK
a4 4 L6"x4"x3/4" 2'-0" a4 TEST

If the block would have been the AnchorBolt block and the DT attribute was A, the rest would be set to:

DT COL-SZ BP-ELEV
A HSS10x10x[6 99'-0"

Right now the spreadsheet is loading but I'm getting a message that it isn't loading and I kill the process with TaskManager.

Hope this helps. See the new files.
Thanks Again,
MSR

[Anonymous]

Okay, I'm still working with it

~'J'~

[Anonymous]

Mike, I could't to open attached drawing,
I think it's higher version than
my A2005

~'J'~

[Anonymous]

I tested this project on the machine
of my neighbour (A2007)
Select in Excel all values entire row!
Change in Excel all sheet names equivalent
to your block names
i.e. BOM_LINE instead of BOM,
ANCHOR_BOLT instead of AnchorBolt etc.
Seems to be work good
Try it and let me know, what's
else

~'J'~ Forget to say about Excel sheets renaming
Message was edited by: Fatty

[MikeEng]

J -

Thanks for working with this... I had surgery last week so I haven't been able to look at this. I tried to look at it this am and I am getting 'Unable to load Active X control'

What references should I be including in the project?

Thanks,
MSR

[Anonymous]

What is the version of your Acad and Excel?

~'J'~

[MikeEng]

I'm using ACAD 2007 and Excel 2003

I've included library objects for AC-17 and EX-11.

Anything else?

Thanks,
MSR

[Anonymous]

I tested it with the same
libraries/ versions as I wrote above
but on another machine
I don't know where is bug 😞

Did you rename Excel sheets by your certain
block names?

~'J'~

[Anonymous]

J-

Tis me...

I'm still trying to get this thing to work. I have AC2006 and Excel 2007. Is there a way that I can get the Active X control to work here? I think that if I just get it to load at home then I can play with it here until it works - then use it at work-

Thanks!!
Mike

[Anonymous]

Mike,
Unfortunately, I can't test it on Excel 2007
just on Excel 2003
Try to change references there
Let me know, if you need something else

~'J'~