Excel question from a Storyboard Artist working in Animation

[pweinstein]

Hello all,
I as work as a storyboard artist in cartoon animation. I'm building a template in Excel to organize my drawings and I need some help.

The sketches I do are part of a storyboard that is organized by scene numbers.

Within each scene there can be 1 or more drawings that make up the scene.
Here's an example:
Scene 1 Panel 1 Is a drawing of Mickey Mouse walking in to a scene
Scene 1 Panel 2 Is a drawing of Mickey stoping to look at his watch.
CUT TO:
Scene 2, Panel 1 A close-up drawing of Mickey looking at his watch.
Scene 2 Panel 2 Mickey taps his watch with his finger etc.

I'm trying to write a formula that will read the photoshop file names automatically numbering Scene and Panel numbers.

You might be thinking, why not just name the file with the correct number when you save? The reason is that these storyboards go through many, many changes before they are finalized. If I could create a template that automatically renumbers my Scenes when a change is made, well that would be amazing.

To automatically number the Scenes, the Excel formula might do something like this:

If "filename" contains "N" go to last occurance of a "filename" with "N" in the spreadsheet and add "1". If "filename" does not contain "N" then go to the last occurance of a filename with an "N" and use that number for the entry.

(*My thought was to use the letter "N" in a filename to indicate a "new" scene.)

The formula for generating the panel numbers would be similar:
IF "filename" contains "N" then the result = 1
If "filename" does not contain "N" then go to the last "filename" number and add "1"

So Excel would automatically generate the scene numbers:
ie: scene 1 panel 1, scene 1 panel 2, scene 1, panel 3, scene 2 panel 1, scene 2 panel 3, etc...

Anybody that can figure this one out? I can send you an original drawing of your favorite Disney character for your troubles!! Thanks. Phil

 

[BrianB]

Your description is obviously not close enough to "Excelese" language for us to understand what you want. Your need to change filenames based on existing content looks reasonably straightforward. You might need a macro to do the job because you talk about changing existing cell

1. We firstly need to know where your data is and what it looks like. With an exact example of a before/after name.

2. Don't understand "To automatically number the Scenes ..". To get Excel to do anything there must be some sort of user input or macro button click.

3. You need to break down your method into its component steps -that's how Excel, and its programmers work. Then say which ones you want automated. How do you do the job now ?

 

[pweinstein]

Storyboard

Thanks. Let me try to clarify. I've built template in Excel that looks like this

Scene ____ Panel __ Scene ___Panel___ Scene ____ Panel____
______________ ________________ ____________________
Photoshop file Photoshop file Photoshop file


The first thing I'm trying to do is automatically have excel insert the photoshop files in to the template. The files are saved like this in the folder; N001.psd, 002.psd, N003.psd

Scene ____ Panel _____ Scene ___Panel_____ Scene ____ Panel____
_____________________ _________________ ________________
N001.psd (image) 002.psd (image) N003.psd (Image)



Next would be a formula for numbering the scene numbers and panel numbers. Every time the formula sees the letter "N" in a filename it will start a new scene number, with the exception of Scene 1 this means looking at the previous scene number and adding "1." If it does not see the letter "N" then the scene number remains the same.

Scene __1__ Panel __ Scene __1 Panel______ Scene __2__ Panel____
___________________ __________________ ___________________
N001.psd (image) 002.psd (image) N003.psd (image)



Lastly, there would be a formula to number the panels. Everytime the formula sees the letter "N" in the associated photshop image file then this becomes panel 1. If there is no letter "N" then it looks at the previous panel number and adds "1"

Scene __1__ Panel 1__ Scene __1_ Panel__2_ Scene __2__ Panel_1
_____________________ ____________________ ________________
N001.psd (image) 002.psd (image) N003.psd (Image)



In answer to your other question. Traditionally this work is done by hand. Apologies, I can't seem to get the .psd to line up exactly inder it's appropriate scene number and panel number (it looks good in preview mode) Anyway, I hope you get the idea..

 

[BrianB]

This is the basis of what you want. I have tried to supply/indicate where you can change the setup. I suggest you copy/paste from here to a module and run it as is on a small set of files - after making the 2 necessary changes indicated in the macro.

'=========================================================
'- GET PICTURES FROM A FOLDER INTO A WORKSHEET
'- Brian Baulsom May 2005
'=========================================================
'- Assumes pictures are in the correct order in the folder.
'- "N" in the file name :-
'-     1. Changes Scene Number
'-     2. Resets Panel to 1
'-     3. Starts a new row of pictures in the sheet
'=========================================================
'- *** NB. Amend code below to
'- *** 1. Set PictureFolder variable
'- *** 2. Set picture file suffix (eg. *.bmp, *.wmf etc.)
'- Run macro from the target worksheet
'=========================================================
Dim PictureSourceFolder As String
Dim ToBook As String
Dim ToSheet As Worksheet
Dim ToRow As Long
Dim ToColumn As Long
Dim PictureFname As String
Dim NewPicture As String
Dim Scene As Integer
Dim Panel As Integer
'-
'=========================================================
'- MAIN ROUTINE
'=========================================================
Sub PICTURES_FROM_FOLDER()
    '=====================================================
    '-*** NB SET THIS TO THE CORRECT PICTURE FOLDER ***
    '=====================================================
    PictureSourceFolder = "c:\test\"
    '=====================================================
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    ToBook = ActiveWorkbook.Name
    '----------------------------------------------------
    '- clear sheet for test purposes
    Set ToSheet = ActiveSheet
    ToSheet.Cells.ClearContents
    ToSheet.Columns("A").ColumnWidth = 4
    For Each s In ToSheet.Shapes
        s.Delete
    Next
    '----------------------------------------------------
    '- initialise variables
    ToRow = 4
    ToColumn = 2
    Scene = 0
    Panel = 0
    '====================================================
    ' *** NB. AMEND LINE BELOW FOR CORRECT FILE SUFFIX
    PictureFname = Dir(PictureSourceFolder & "*.bmp")
    '===================================================
    '- loop to get each picture file from the folder
    NewPicture = PictureSourceFolder & PictureFname
    While PictureFname <> ""
        Application.StatusBar = PictureFname
        AddNewPicture   ' subroutine below
        PictureFname = Dir
    Wend
    '-----------------------------------------------------
    '-- close
    ToSheet.Range("A1").Select
    Application.ScreenUpdating = True
    MsgBox ("Done.")
    Application.StatusBar = False
    Application.Calculation = xlCalculationAutomatic
End Sub
'== eop =====================================================

'===========================================================
'- SUBROUTINE TO ADD A NEW PICTURE
'===========================================================
Private Sub AddNewPicture()
    '------------------------------------------------------
    '- set variables Scene/Panel & Row/Column
    If InStr(1, PictureFname, "N", vbTextCompare) > 0 Then
        Scene = Scene + 1
        Panel = 1
        ToRow = IIf(Scene > 1, ToRow + 16, ToRow)
        ToColumn = 2
    Else
        Panel = Panel + 1
        ToColumn = ToColumn + 3
    End If
    '-----------------------------------------------------
    '- labels to worksheet
    With ToSheet
        .Cells(ToRow - 2, ToColumn).Value = "Scene : " & Scene
        .Cells(ToRow - 2, ToColumn + 1).Value = "Panel : " & Panel
        .Cells(ToRow + 10, ToColumn).Value = PictureFname
    End With
    '------------------------------------------------------
    '- insert picture
    With ToSheet
        .Columns(ToColumn).ColumnWidth = 10
        .Columns(ToColumn + 1).ColumnWidth = 10
        .Columns(ToColumn + 2).ColumnWidth = 4
        .Cells(ToRow, ToColumn).Select  ' topleft cell
        .Pictures.Insert(NewPicture).Select
    End With
    '------------------------------------------------------
    '- Format Picture
    Selection.ShapeRange.LockAspectRatio = msoTrue
    With Selection
        .Placement = xlFreeFloating
        .PrintObject = True
        .Width = 100
        .Height = 100
    End With
    '------------------------------------------------------
End Sub
'=== EOP ==================================================

 

[pweinstein]

Thanks very much! I'm not familiar with how to run macros on excel, so it's going to take me some time for me to understand how to run the routine you provided. If you have any advice for a newbie in this regard that would be great. Thanks for your time and effort. I've already had several people stop by to see what the heck I'm doing in here with this digital workstation. The storyboard process is still primarily a pencil and paper manual process. Even on big CG movies like "The Incredibles" the storyboard artists still draw on paper.

 

[TheToeOfPooh]

Brian, Phil

This is a great idea for us -
We handle loads of print jobs per month and need to keep several images per job for traceability.

How would you trigger the Module?
I tried to use a command button on a Worksheet but it was removed as soon as the code had finished running.

Bernard

 

[BrianB]

This bit removes the pictures (and the button). It is possible to code so the button remains but I do not have any time at present. I suggest you run the macro from the Excel menu Tools/Macros ...... or add a toolbar button at top via Tools/Customize ....

    For Each s In ToSheet.Shapes 
        s.Delete 
    Next

EDIT :
=============
Of course you can remove the code in that section completely (except for the first line) so long as you start with a clean worksheet.