Author Topic: Block Insert via Excel Spreadsheet?  (Read 4365 times)

0 Members and 1 Guest are viewing this topic.

acadsupv

  • Mosquito
  • Posts: 4
Block Insert via Excel Spreadsheet?
« on: October 24, 2012, 03:36:11 PM »
Hello, I need to pick your collective brains for advice.

I have a spreadsheet which serves as our block library, with categories, names, part numbers, descriptive info, .wmf images. Under each image is a cell with the block name. What I'd like to do is have the user "pick" on the block name cell, and pass the name to Acad for the insert command.

This the outline:

1) User opens block library spreadsheet via command or menu selection.
2) Insert command is initiated and paused for input.
3) User selects block by clicking block name.
4) Block name is passed to insert command.

I've looked at writing an Excel VB macro that would write the block name to a text file, and having lisp check for the file, read the string in it, then erase the file. But this brought up the issue of needing to automatically enable macros in the spreadsheet (to avoid the macro alert box) when the file is opened. I've never had to add a macro the signed, trusted source list before, it looks like a hassle.

I also need a way to make sure that only the block name cells are the only ones passed to lisp - the user might click on other cells in the spreadsheet, any of which may also contain text.

Any ideas? Thanks in advance!

Using vanilla Autocad 2002 & Excel 2002.
« Last Edit: October 24, 2012, 03:48:18 PM by acadsupv »

Lee Mac

  • Seagull
  • Posts: 12906
  • London, England
Re: Block Insert via Excel Spreadsheet?
« Reply #1 on: October 24, 2012, 04:00:26 PM »
Are Tool Palettes available in 2002? That is what I would recommend  :-)

CAB

  • Global Moderator
  • Seagull
  • Posts: 10401
Re: Block Insert via Excel Spreadsheet?
« Reply #2 on: October 24, 2012, 04:01:54 PM »
May find some help here:
http://goo.gl/eETya
I've reached the age where the happy hour is a nap. (°¿°)
Windows 10 core i7 4790k 4Ghz 32GB GTX 970
Please support this web site.

acadsupv

  • Mosquito
  • Posts: 4
Re: Block Insert via Excel Spreadsheet?
« Reply #3 on: October 24, 2012, 04:46:16 PM »
Are Tool Palettes available in 2002? That is what I would recommend  :-)

There is Design Center, but the images are too small and we'd be missing all the other information in the spreadsheet as well. Thanks anyway, though!

acadsupv

  • Mosquito
  • Posts: 4
Re: Block Insert via Excel Spreadsheet?
« Reply #4 on: October 24, 2012, 04:51:13 PM »
May find some help here:
http://goo.gl/eETya

Thanks, I'd done a bit of searching here prior to posting. Mostly the topics had to do with populating a spreadsheet with data from Acad, or using data from a known cell or range to populate a list in lisp, etc. I couldn't find anything that discussed how to pass a string from Excel to Acad based on a user's selection.

CAB

  • Global Moderator
  • Seagull
  • Posts: 10401
Re: Block Insert via Excel Spreadsheet?
« Reply #5 on: October 24, 2012, 07:17:49 PM »
Maybe one of these?
http://goo.gl/qWD3M
I've reached the age where the happy hour is a nap. (°¿°)
Windows 10 core i7 4790k 4Ghz 32GB GTX 970
Please support this web site.

jbuzbee

  • Swamp Rat
  • Posts: 851
Re: Block Insert via Excel Spreadsheet?
« Reply #6 on: October 25, 2012, 11:00:05 AM »
I have a keynote routine that reads a basic .txt file and I had planned to switch that to .xls (easier editing).

My point is to keep everything in AutoCAD.  You could create a lisp that "reads" the .xls file and propagates a listbox in a dcl form with the black names. Then selected blockname can be passed to a lisp function.  This could be further enhanced using a remote "container" drawing file and utilize objectDBX to import blocks if not current.

There is plenty of code here and on the web to put something like this together.

Good Luck!!

jb
James Buzbee
Windows 8

irneb

  • Water Moccasin
  • Posts: 1794
  • ACad R9-2016, Revit Arch 6-2016
Re: Block Insert via Excel Spreadsheet?
« Reply #7 on: November 07, 2012, 01:12:01 PM »
I'm really not sure how to have a cell selection trigger a lisp code or send something to acad, that is - without going the VBA route from inside Excel. Unfortunately you can't set an event for the excel from lisp, so you can't wait for the user to pick the cell after opening the XLS.

An alternative way might be to open the Excel file, then show a dialog box telling the user to select the relevant item. Then swap back to acad and run another code to see what range is selected. E.g. use code from here: http://forums.augi.com/showthread.php?132825-Excel-selected-range

Anyhow, that's perhaps one step inserted which might cause a bit of user irritation. But I'm not too sure how to do this from only lisp. You might have better luck using dotnet - though I think 2002 doesn't work with Dotnet. You could always make a DVB file from Excel's VBA as n addon, so you can install it separate from the XLS - i.e. the macro dialog doesn't pop up anymore.

Unfortunately not many of us have those 2 versions anymore. So testing is a bit difficult.
Common sense - the curse in disguise. Because if you have it, you have to live with those that don't.

acadsupv

  • Mosquito
  • Posts: 4
Re: Block Insert via Excel Spreadsheet?
« Reply #8 on: November 07, 2012, 04:20:47 PM »
I'm really not sure how to have a cell selection trigger a lisp code or send something to acad, that is - without going the VBA route from inside Excel. Unfortunately you can't set an event for the excel from lisp, so you can't wait for the user to pick the cell after opening the XLS.

An alternative way might be to open the Excel file, then show a dialog box telling the user to select the relevant item. Then swap back to acad and run another code to see what range is selected. E.g. use code from here: http://forums.augi.com/showthread.php?132825-Excel-selected-range

Anyhow, that's perhaps one step inserted which might cause a bit of user irritation. But I'm not too sure how to do this from only lisp. You might have better luck using dotnet - though I think 2002 doesn't work with Dotnet. You could always make a DVB file from Excel's VBA as n addon, so you can install it separate from the XLS - i.e. the macro dialog doesn't pop up anymore.

Unfortunately not many of us have those 2 versions anymore. So testing is a bit difficult.

Thanks for responding. Right now I'm working on using VBA in Excel to write the block name to a cell when the user presses a command button labeled "Insert" (one under each image). I think I can make it fairly seamless using the Getexcel.lsp functions I found here (or somewhere). If I get this working I will post my code here.