Author Topic: Running Excel from Acad  (Read 3106 times)

0 Members and 1 Guest are viewing this topic.

MikeJarosz

  • Guest
Running Excel from Acad
« on: September 05, 2006, 03:39:18 PM »
I'm working my way through a program to create an Excel drawing list from acad drawing titleblocks. I have successfully read the .dwg files and dumped them into Excel. However, I decided that formatting the Excel file in Acad VBA wasn't a good idea, as the Excel VBA formatting code is extremely verbose and would be better off run native to Excel. What I would like to do is load a file "FormatDwgList.bas" into the Excel file and run it there, so the user gets a finished product. It comes down to two lines of code:

FormatList = "c:\scripts\FormatDwgList.bas"
.
.
.
.
XL.VBE.ActiveVBProject.VBComponents.Import FormatList
XL.Run "FormatDwgList", WSheet


The .bas file actually loads and is present in the sheet. However, when VBA reaches the XL.Run command I get run time error 1004 "The macro FormatDwgList cannot be found" I got this syntax from Microsoft knowledge base.
Could it be a synchcronize problem?



Malik641

  • Guest
Re: Running Excel from Acad
« Reply #1 on: September 06, 2006, 01:01:56 AM »
It could be a synchronizing problem. I've had trouble with this with userforms in excel (programming in excel vba).

I would have the program wait for like a second before executing the procedure....something like:

XL.VBE.ActiveVBProject.VBComponents.Import FormatList
XL.Wait(Now + TimeValue("00:00:01"))
XL.Run "FormatDwgList", WSheet

And I was going to suggest the XL.Ontime method....but I'm not sure how to pass variables that way..... :|

The .OnTime method I think would be better...but give the .Wait a try for now.

Good luck!
-Joseph

MikeJarosz

  • Guest
Re: Running Excel from Acad
« Reply #2 on: September 06, 2006, 10:01:51 AM »
I tried your suggestion. Still same error. I bumped it up to 5 seconds, still no luck. I also moved the import as far away from the run as possible.

I looked up ONTIME. Apparently it is meant for procedures running in the same space. I launch Excel as a separate process from the Acad session.

The NOT FOUND error seems to be the key here. Am I not giving it enough info to find the code module in Excel, is it an asychronous problem or something else? I have triple checked all the names. The file is FormatDwgList.bas, the sub is named FormatDwgList and the module is named FormatDwgList. This same program gave me a lot of grief until I discovered the "unqualified references" condition in Office automation. Maybe I can't just refer to FormatDwgList, but need to use a fully qualified object variable? (Just thinking out loud)

David Hall

  • Automatic Duh Generator
  • King Gator
  • Posts: 4075
Re: Running Excel from Acad
« Reply #3 on: September 06, 2006, 10:11:06 AM »
I am just curious what you mean by formatting the dump in excel?  I read in my titleblock info into excel, and it comes in right where I want it, and looks like what I expect.  Maybe I dont expect much (haha).  Anyway, I would be interested in seeing what your doing to compare to my method.  I can post my code if that would help you.
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second.
Sometimes the question is more important than the answer. (Thanks Kerry for reminding me)

MikeJarosz

  • Guest
Re: Running Excel from Acad
« Reply #4 on: September 08, 2006, 05:29:09 PM »
By formatting, I mean fonts, colors, cell outlines etc. A small sample of the  Excel VBA looks like this:

' format title row
With Range(Cells(1, 1), Cells(1, xCols))
    .Interior.ColorIndex = 15
    .Interior.Pattern = xlSolid
    .Font.Bold = True
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
End With
   
'format revision titles
Rows("1:1").RowHeight = 95
With Range(Cells(1, 6), Cells(1, xCols))
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .Orientation = 90
    .WrapText = True
    .Font.Size = 8
End With

As I pointed out, this kind of stuff in Excel VBA is very verbose. It gets even worse running Excel code from acad because of the unqualified references problem in Office automation. ( see MS KB # 319832) That's why I want to run it native in Excel.

David Hall

  • Automatic Duh Generator
  • King Gator
  • Posts: 4075
Re: Running Excel from Acad
« Reply #5 on: September 08, 2006, 05:51:24 PM »
so can you use one set of code to extract from Autocad, dump it into excel template that has your formatting already done, and go from there?  Or dump from autocad, then format from excel?
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second.
Sometimes the question is more important than the answer. (Thanks Kerry for reminding me)

MikeJarosz

  • Guest
Re: Running Excel from Acad
« Reply #6 on: September 14, 2006, 11:24:04 AM »
I use acad VBA to extract info from drawings and dump it into Excel. Then still under Acad VBA control, I load an Excel VBA module into Excel and attempt to get Excel to run it natively in Excel. There are two good reasons to approach it this way. First the rows and columns in the Excel sheet vary widely. It is easier for excel to detemine the extent of the sheet.

Secondly, more inportantly, the formatting commands in Excel VBA are exceedingly verbose. In theory, they can be issued from Acad VBA, but they must be fully qualified, or you will launch a phantom copy of Excel. (see MS knowledge base 319832). This is an enormous amount of typing.

What is really annoying is I can get the BASIC file loaded into the Excel code module, but I cannot get it to run.

Malik641

  • Guest
Re: Running Excel from Acad
« Reply #7 on: December 03, 2006, 11:58:48 PM »
Hey Mike, sorry it took me so long to post back...

Try explicitly calling out the workbook first using this syntax:

Application.Run "MyWorkbook.xls!FormatDWGList", WSheet

Hope this does it.
« Last Edit: December 03, 2006, 11:59:57 PM by Malik641 »