• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
4

Illustrator VBA scripting 101 - via Excel

Community Expert ,
Mar 17, 2012 Mar 17, 2012

Copy link to clipboard

Copied

This post will attempt to introduce newcomers to Illustrator Visual Basic Scripting (well, not actually vbs, but rather tru VBA, Visual Basic for Applications). I personally prefer vba over bvs for a number of reasons. First, I always have Excel and Illustrator open, so it makes sense for me use it to drive Ai. Second, I usually need to transfer data between the two programs. Third...I love the Excel IDE...ok, let's get right into it.

- Open Excel

- hit Alt+F11, to bring up the editor

- in the Tools menu, click on References...

- add a reference to "Adobe Illustrator CS5 Type Library"

- in the Personal.xls (or in any other book) add a Module. Personal is a global workbook that is always available. If you don't see it, go back to Excel and record a macro, anything will do. That will create the Personal file.

- and type the following in that module

- we have to continue the tradition and do the "HelloWorld" script

Sub helloWorld()
    Dim iapp As New Illustrator.Application
    Dim idoc As Illustrator.Document
    Dim iframe As Illustrator.TextFrame
   
    Set idoc = iapp.ActiveDocument
    Set iframe = idoc.TextFrames.Add
   
    iframe.Contents = "Hello World from Excel VBA!!"
   
    Set iframe = Nothing
    Set idoc = Nothing
    Set iapp = Nothing
End Sub

- save Personal book

- open Illustrator and create a new document first

- to run, move the cursor anywhere inside the Sub...End Sub and hit F5

that's it for now...in the following posts we'll move the text to the middle of the page, create new documents, get data from an Excel Range to Illustrator, get data from Illustrator text frame to an Excel Range...and more, hopefully.

questions? comments?

TOPICS
Scripting

Views

33.4K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe
Explorer ,
Mar 17, 2012 Mar 17, 2012

Copy link to clipboard

Copied

That is a great start Carlos! I'll give it a go and let you know. Thanks!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Mar 17, 2012 Mar 17, 2012

Copy link to clipboard

Copied

Carlos- I tested this and got it to work. I'm a little familiar with VBA in Excel within Excel and that is it. That is sort of what  got me into scripting. I started messing around with JS because it was cross-platform. I understand that DIM is the same as VAR in JS and that SET adds a reference to the object. I also understand that "set object = nothing" frees up memory, correct? With that said, I know you don't want to move too fast with my 101, but I have a question.

In my last discussion about getting illustrator to read from an excel csv, I was wondering if the same thing could be done with Excel.

Could an XLSM file read a line from a CSV file in "A1" and depending on what was in the first column in the XLSM, search the CSV file and find the indexed item in the second column or third? I've done it before with standard VLookup functions and it was cumbersome and very slow, and for some reason, everytime I would save the file it would take forever although the xls file was a single sheet.

i've attacthed a sketch of what I'm talking about, because it's hard to explain and I think the sketch explains it better. Here it is, sorry if it is blurry, it looked fine before I posted:

xlsm example.PNG

I apologize to ask a non-Adobe question on their forum, but you are a big help and the guys I've come across on Excel forums have been very condesending. I'll eventually get to the step of saving to ADOBE PDF from Excel, but not just yet. Thanks in advance!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Mar 17, 2012 Mar 17, 2012

Copy link to clipboard

Copied

I understand that DIM is the same as VAR in JS and that SET adds a reference to the object. I also understand that "set object = nothing" frees up memory, correct? With that said, I know you don't want to move too fast with my 101, but I have a question.

all that is correct, including moving to fast in this post.

I'm a little familiar with VBA in Excel within Excel and that is it. That is sort of what  got me into scripting. I started messing around with JS because it was cross-platform.

me too, Basic got me hooked to programming, it was ok for my own needs, then I moved to JS to do cross-platform scripting.

Could an XLSM file read....?

yes, it can. Let's do this, to try to keep this organized, start a new thread with your more advanced question and I'll try to help you there. You can copy/paste from here to the new thread.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Mar 17, 2012 Mar 17, 2012

Copy link to clipboard

Copied

k Carlos, will do!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Mar 22, 2012 Mar 22, 2012

Copy link to clipboard

Copied

When can we expect the next class session to begin?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Mar 22, 2012 Mar 22, 2012

Copy link to clipboard

Copied

Lesson 2 - Move the Hello World to the center of the page

to move our "Hello World" textFrame to the center of the page, we need to know where the center is first...one way to do it is to get the Width and the Height of the document and divide by two to get half of it...let's assign those values to our variables to get to use them later in the code.


docWidth = idoc.Width

docHeight = idoc.Height

now we need to move our text to this new position, but there's no property to get the center of our text frame...we only have top and left properties, so we use the width and height properties and divide by two like we did with the document.


frameWidth = iframe.Width

frameHeight = iframe.Height

once we have both the document and frame measurements, we assign them to the new frame position


iframe.Top = (-docHeight / 2) + (frameHeight / 2)

iframe.Left = (docWidth / 2) - (frameWidth / 2)

the whole code looks like this, please note that Illustrator reversed the Y axis in CS5, for CS4 it will go the opposite direction.

Sub helloWorld2()

    Dim iapp As New Illustrator.Application

    Dim idoc As Illustrator.Document

    Dim iframe As Illustrator.TextFrame

    Set idoc = iapp.ActiveDocument

    Set iframe = idoc.TextFrames.Add

    docWidth = idoc.Width

    docHeight = idoc.Height

    iframe.Contents = "Hello World from Excel VBA!!"

    frameWidth = iframe.Width

    frameHeight = iframe.Height

    iframe.Top = (-docHeight / 2) + (frameHeight / 2)

    iframe.Left = (docWidth / 2) - (frameWidth / 2)

    Set iframe = Nothing

    Set idoc = Nothing

    Set iapp = Nothing

End Sub

to be continued....

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Mar 23, 2012 Mar 23, 2012

Copy link to clipboard

Copied

Excellent lesson Carlos! I've been trying to get my head around alignment for awhile.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Mar 23, 2012 Mar 23, 2012

Copy link to clipboard

Copied

thanks, more to come..

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Mar 23, 2012 Mar 23, 2012

Copy link to clipboard

Copied

Lesson 3: Create a new document, create a new layer, transfer data from Excel to AI

In lesson 2, I mentioned illustrator reversed the Y axis. That means that in CS4 and earlier, the origin was at the bottom left of the document and positive Y values went "up" from the bottom. In CS5, the origin moved to the top left corner of the document and positive values went "down" from the top.

are you confused yet? No? let me try harder. In order to keep existing scripts from breaking, if you create NEW documents in CS5, the axis will be at the bottom/left (just like CS4).

got it? cool, let's put our newfound knowledge to good use. In the next exercise, we will create a new document, add a second layer, a text frame to this new layer, and get the data in the first Cell in the Active Excel Document to add to the text frame

to create a new document with defaults, use the add method

idoc = iapp.documents.add

next we will create a new layer

Dim ilayer As Illustrator.Layer

Set ilayer = idoc.Layers.Add

and rename our newly created layer

ilayer.Name = "Data from Excel"

then add a text frame to the layer, as opposed to the document in the previous post

Set iframe = ilayer.TextFrames.Add

before we populate our text frame, go to Excel and type "Hello from Cell A1!", in Cell A1 in the Active Book.

declare a variable to hold Cell 1

    Dim myRange As Range

    Set myRange = Range("A1")

now use that to populate the text frame

iframe.Contents = myRange.Value

that's it!!,

last, we update the top position, to accommodate for the "new" document issue

    iframe.top = (docHeight / 2) + (frameHeight / 2)

here's the complete code

Sub helloWorld3()

    Dim iapp As New Illustrator.Application

    Dim idoc As Illustrator.Document

    Dim iframe As Illustrator.TextFrame

    Dim ilayer As Illustrator.Layer 'new

    Dim myRange As Range ' new

    Set myRange = Range("A1") ' new

    Set idoc = iapp.Documents.Add 'updated

    Set ilayer = idoc.Layers.Add 'new

    ilayer.Name = "Data from Excel" 'new

    Set iframe = ilayer.TextFrames.Add ' updated

    docWidth = idoc.Width

    docHeight = idoc.Height

    iframe.Contents = myRange.Value 'updated

    frameWidth = iframe.Width

    frameHeight = iframe.Height

    iframe.top = (docHeight / 2) + (frameHeight / 2) 'updated

    iframe.left = (docWidth / 2) - (frameWidth / 2)

    Set myRange = Nothing 'new

    Set ilayer = Nothing 'new

    Set iframe = Nothing

    Set idoc = Nothing

    Set iapp = Nothing

End Sub

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Mar 24, 2012 Mar 24, 2012

Copy link to clipboard

Copied

Excellent, I didn't know that about the origin reversing. That's odd.Thanks

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Mar 28, 2012 Mar 28, 2012

Copy link to clipboard

Copied

Anymore Carlos, my brain is hungry!!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Mar 29, 2012 Mar 29, 2012

Copy link to clipboard

Copied

Hi, thanks for being so enthusiastic about this post, I'll do lesson four soon, I've been a little busy lately...stay tuned!!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Mar 30, 2012 Mar 30, 2012

Copy link to clipboard

Copied

No problem, I love to learn.

Yeah, I haven’t seen you on any posts lately. I was wondering what happened. Hopefully you were writing a how to book. ☺

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Apr 01, 2012 Apr 01, 2012

Copy link to clipboard

Copied

I wish I could, I don't think I'm any good at writing...I mean properly.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Apr 02, 2012 Apr 02, 2012

Copy link to clipboard

Copied

Lesson 4: Creating Shapes, Working with Selections, writing data to Excel

In the Illustrator world a shape is a....well, I'm not going to bore you with technical terms only rocket scientists would understand....let's just say a Circle is a shape, as well as a Rectangle or a Star, there much better than the actual definition. Then in the scripting lingo all shapes are pathItems.

There are a number of ways of creating shapes, in this exercise well focus on using the various Methods of the PathItem Object.

to create a Circle we'll use the Ellipse Method, all arguments are optional, if we don't supply any, the method uses default values.

Ellipse

([top as Double]

[, left as Double]

[, width as Double]

[, height as Double]

[, reversed as Boolean]

[, inscribed as Boolean])

Dim icircle As Illustrator.PathItem

Set icircle = idoc.PathItems.Ellipse(300, 300, 100, 100)

similarly, to create a square, we use the Rectangle Method

Dim isquare As Illustrator.PathItem

Set isquare = idoc.PathItems.Rectangle(200, 200, 100, 100)

and to make a star we use the...hum...the Star Method

Dim istar As Illustrator.PathItem

Set istar = idoc.PathItems.Star(400, 400, 100, 50, 5)

now lets select the square and read some of its properties and write them down to Excel

isquare.Selected = True

get properties of the top most selection, in case we have many items selected, for now it should only be the square

    w = idoc.Selection(0).Width

    h = idoc.Selection(0).Height

    y = idoc.Selection(0).top

    x = idoc.Selection(0).left

and lets write those values to Excel, using the Cells object this time. Make sure you have a blank Excel book open, it will write data to the first 4 rows, 2 first columns

    Cells(1, 1) = "width: "

    Cells(1, 2) = w

    Cells(2, 1) = "height: "

    Cells(2, 2) = h

    Cells(3, 1) = "top: "

    Cells(3, 2) = y

    Cells(4, 1) = "left: "

    Cells(4, 2) = x

here's the complete code, from now on, we'll start every exercise with a blank Excel book and a blank Illustrator document, so please do that before runing.

Sub lesson4shapes()

    Dim iapp As New Illustrator.Application

    Dim idoc As Illustrator.Document

    Dim icircle As Illustrator.PathItem

    Dim isquare As Illustrator.PathItem

    Dim istar As Illustrator.PathItem

    Set idoc = iapp.ActiveDocument

    Set icircle = idoc.PathItems.Ellipse(300, 300, 100, 100)

    Set isquare = idoc.PathItems.Rectangle(200, 200, 100, 100)

    Set istar = idoc.PathItems.Star(400, 400, 100, 50, 5)

    isquare.Selected = True

    w = idoc.Selection(0).Width

    h = idoc.Selection(0).Height

    y = idoc.Selection(0).top

    x = idoc.Selection(0).left

    Cells(1, 1) = "width: "

    Cells(1, 2) = w

    Cells(2, 1) = "height: "

    Cells(2, 2) = h

    Cells(3, 1) = "top: "

    Cells(3, 2) = y

    Cells(4, 1) = "left: "

    Cells(4, 2) = x

    Set istar = Nothing

    Set isquare = Nothing

    Set icircle = Nothing

    Set idoc = Nothing

    Set iapp = Nothing

End Sub

Note that the code we just wrote is not the most efficient way of doing things, we don't have to select an object in order to work on it (get the properties for instance). We did it for illustration purposes, we could also use a loop to write data to Excel. We'll do that in the next lesson.

also, note that the top/left values don't match exactly with the values we entered (200, 200), homework, can you tell why?

writePropsToExcel.PNG

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Apr 02, 2012 Apr 02, 2012

Copy link to clipboard

Copied

Oh, that’s a good lesson. Thanks a lot for this one! I wish there was a way for me to give you points on this forum.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Apr 10, 2012 Apr 10, 2012

Copy link to clipboard

Copied

Hey Carlos-

I have a grasp on the shapes above, but how about drawing shapes from points? I've went through some tutorials that came with Illustrator VBScript, and I've figured out how to get the sizes right, but when I try to position them, the values that Illustrator show are wrong when I use methods lIke .top and .left for the x and y coordinates. I would assume that it would be an array, but is there a quick way to absolute reference where you want to draw something?

If you have this saved for a future lesson, I can wait, no problem. thanks

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Apr 13, 2012 Apr 13, 2012

Copy link to clipboard

Copied

Got anymore Carlos?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Apr 20, 2012 Apr 20, 2012

Copy link to clipboard

Copied

thanks for your interest, more will come for sure...shortly.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Apr 21, 2012 Apr 21, 2012

Copy link to clipboard

Copied

Alright Carlos. I eagerly await your next lesson!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Apr 22, 2012 Apr 22, 2012

Copy link to clipboard

Copied

note that the top/left values don't match exactly with the values we entered (200, 200), homework, can you tell why?

Is that because the shape was drawn with the default 1 pt stroke and the .top & .left values are based on visible Bounds?  (my only guess)  So why is that?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Apr 24, 2012 Apr 24, 2012

Copy link to clipboard

Copied

correct, top/left properties reflect visible bounds (includes stroke size), what we see in the UI when we select an item is Geometric Bounds in scripting.

thanks for posting, you get an extra credit

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Sep 25, 2013 Sep 25, 2013

Copy link to clipboard

Copied

Carlos! Thank you so much for all the different work that you have put on these forums. It really is a lifesaver for many I am sure!

I am looking to do something exactly like what you did in post 11 of the following forum: http://forums.adobe.com/thread/773143

But since I am more of a beginner with scripting I figured I'd try this out and already having what looks like software issues. I did what you instructed in the first post of this forum, however when I hit run, the window moved over to Illustrator and then never did anything and when I went back to the VBA Editor in Excel it showed the error message below:

SS of Active X Error.jpg

When I hit Debug, it highlights the following line:

Set idoc = iapp.ActiveDocument

So I searched the error on google, but I couldn't find anything specific to working with Excel and Adobe Illustrator, all of the other errors seemed to be with other applications. I saw one forum where it showed this error because two versions of Excel were installed, which I did have that (2010 and 2013) but I used a different hard drive that only has Excel 2010 and it still didn't work.

I am using Windows 7 64 Bit with Adobe Illustrator CS6. The second hard drive I tried with only one version of Excel is a brand new clean install of Windows 7 that has not been updated. The other hard drive is also Windows 7 but has been updated. Both show the same error.

Let me know if you need any more info and if you can help me I am really excited to get stuff like this running in the future as this could save myself and my colleagues a ton of time.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Sep 25, 2013 Sep 25, 2013

Copy link to clipboard

Copied

Hi tyedye09wab,

I don't mean to step on Carlos' conversation with you, but in case he is busy, I thought that I would throw in a quick comment or two.

First - to answer your question -- did you have an active document "open"? If there is no document open, it can't assign the variable to a non-open document. That would involve code to create a document.

Hope this helps,

TT

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines