Jump to content

Problem with AutoCAD VBA creating named range in ecxell work bork


pjotrki

Recommended Posts

I want tho create a named rangein excel from autocad.

I can not call the workbook.names.add method and pass the arguments.

This works from within Excel VBA but not from within AutoCad VBA.

I am working with AutoCAD 2009 and excel 2007.

Following VBA code doesn't work in AutoCAD VBA:

 

Sub Test()

Dim Excel As Excel.Application
 Dim ExcelSheet As Object
 Dim ExcelWorkbook As Object

 Dim RowNum As Integer
 Dim Header As Boolean
 Dim elem As AcadEntity
 Dim Array1 As Variant
 Dim Count As Integer

 ' Launch Excel.
 Set Excel = New Excel.Application

 ' Create a new workbook and find the active sheet.
 Set ExcelWorkbook = Excel.Workbooks.Add
 Set ExcelSheet = Excel.ActiveSheet

'This line causes the error
ExcelWorkbook.Names.Add Name:="myName", RefersToR1C1:= "=Sheet1!R1C1:R10C10"
'This line causes the error

ExcelWorkbook.SaveAs "Attribute.xls"
 Excel.Application.Quit
End Sub

 

 

Can someone help me ?

 

Thank You very much,

 

Peter Van der Velden

Deinze

Belgium

Edited by SLW210
Code Tags!
Link to comment
Share on other sites

  • 3 months later...

hope still helps

 

your code works with Autocad 2014 and Excel 2013. only noticed it starts a hidden Excel session, so I had to add

Excel.Visible=true 

statement

 

maybe your problem could derive from your default new workbook sheets management options, since:

- you first set "ExcelSheet" as the Active Sheet of the new Excel application

- but when creating the named range you reference it to "Sheet1" sheet

so maybe the new Workbook you created doesn't have any sheet called "Sheet1" (or any sheet at all!)

I'd suggest to add code for:

- checking for the existence of the sheet you need to point to

- and then refer to it

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...