Topic: Generate Index Text from Excel Spreadsheet.

[DIW_CADtech]

The client is forcing us to use their antiquated template for all index sheets  It is basically an array of individual pieces of center aligned text.  They send us and we modify an excel spreadsheet, but the spreadsheet is not in the same order as the index sheet.  Furthermore, one would like to use a table, but the client wants NO MTEXT in the drawing.  We've already agreed to this. 

I could use a table.. then format.. then explode.. then rearrange. This still takes a great deal of time.

The thing is the log file is an excel spreadsheet and it never changes in format.  Would it be possible to Script the placement of the text and pull the text from the cells in the spreadsheet?

I have to do this and re-do this several times throughout the design process and it's a tremendous pain in the keester.

The spacing is always the same vertically and for the most part horizontally, although the distance from one column to the other varies.

Each individual piece of text(not mtext) is aligned center.

column   "X"
1   0.5000"
2   1.2500"
3   2.6895"
4   4.5411"
5   5.6375"
6   6.2121"
7   7.8273"
8   11.4238"
9   14.4935"
10   16.00"

The first "Y" is at 9.85" and each following row is .2" lower than the previous

Example DWG
https://dl.dropboxusercontent.com/u/20790817/theswamp/index/SOME_DRAWING_INDEX_TEMPLAT.dwg

Example XLS
https://dl.dropboxusercontent.com/u/20790817/theswamp/index/SOME_PROJ_LOG.xls

Example Completed Index
https://dl.dropboxusercontent.com/u/20790817/theswamp/index/58-03-0296.dwg

I don't mind copying and pasting the header info.. takes a few min..

Manually entering 300 lines and 10 columns is a pain.  I often have to do this multiple times throughout a project because the order will change, or drawings will be added or removed from the project log.

In any case this seems like something that both should and could be scripted.  Especially since each piece of text is formatted exactly the same.

The main problem being that the columns in the DWG are in a different order than the columns in the XLS.

Sometimes the project is 20 DWG's  and once 1300 dwgs.

The engineers always update the EXCEL LOG, but neglect to inform me so I can update the INDEX.  If I could somehow find a LSP routine that would allow me to clear the text and then repost from a log file I can choose.. that would be freek'n awesome. 

I have spent at least 6 to 10 hours of each project filling these things out and then backchecking for fat finger mistakes...

At my wits end....   Please HELP
PLEASE do not suggest TABLES, OLE objects  OR MTEXT..  those avenue's are not available to me.


I am not apt at writing LSP.  BUt I am pretty good at figuring out what I'm looking at...  Just not good enough to write or debug. 

[ronjonp]

Can you post a drawing with before & after?

[DIW_CADtech]

I revised the above text...  and added some links.

[danallen]

Can't you keep a master file that has mtext or table, then explode for sending to client? And/or use Dotsoft excel to cad tools? http://www.dotsoft.com/xl2cad.htm

[DIW_CADtech]

Forgetting the fact that the colomn order is different from the log to the index, Tables are not an option because it leads to individually re-adjusting position of text..  special characters like slashes tend to reposition the text, resize cells and can change the location of the MTEXT.  Furthermore, I have to explode the MTEXT and I loose the center anchor as everything defaults to left.  so my positioning is whacked. 

I end up resetting TEXT alignment and manually repositioning text...  Not so bad if you have 20 rows.. but when you have 300 or 600 or even 1300 rows, fixing text position and alignment is NOT something i want to spend my time doing.  OR RE-DOING. 

Every log file is formatted exactly the same,
and so are these indexes.

SHoudl be a way to read column X from XCEL then script inserting text then follow suit on the next column. 

I'm trying to avoid having to track, maintain, reformat, re-adjust any intermediate files.   This always leads to more issues.  I want to go from the log... directly to CAD.

Take a look at the files posted you will see what I mean.. feel free to try your table idea. I have done it and I can never get it perfect.  I got a steak dinner for you if you can make it line up properly in less than a min.  I've tried this...  it is just too many steps away from point and click.. too much manual work. In my current method I am already doing a lot of manual work.  I'm looking for a method around the manual placement adjustment.  These pieces of text always have the same anchor points and center alignment.. it's just a matter of grabbing the data from XCEL and droping the text in the predetermined location.

I have scripted the entry before using a database.. simple code, word and the mail merge function... All of this works, but takes a great deal of time to set up... and is tantamount to programming.


Also, I can not have LINKS in the drawing it must be 100% stand alone.  The program you mention seems great at taking what is formatted in excel and generating linework in AUTOCAD and linking...  that's great.. but my LOG in excel looks nothing like my INDEX in CAD.  So again.. manual formatting and relocating text.  In fact I don't want any formatting to carry over from Excel. 

[danallen]

I don't have a trick for excel to text, but within excel you could use a pivot table to rearrange the information, see attached. I imagine you could tweak this pivot table excel format to be a good match to CAD, then use Dotsoft tool to import/link.

[DIW_CADtech]

Current method is a multi step process.

I start by copying the spreadsheet into a new spreadsheet.  Then in the new spreadsheet I have a separate tab that grabs data from the first tab.   

So in the Second Tab all the columns are in the correct order.

Then I select that data and copy

Switch to Autocad and Paste as a table

I strip all formatting from the table

I set the text size 1/2 what the final size needs to be...  (this is just for convenience)

Then I set the cell height to .2 which is exactly the spacing on the final table.

Then I explode the table and delete any borders.

Then I drag each column close to it's respective column  in the final layout.

At this time all the pieces of text are layed out in the final layout, but they all have a value of "~"

Then I use a LSP command STX to swap data from each MTEXT to the TEXT I already have formatted and laid out.  (got this from LEE MAC)
(it is a sweet LSP)

I select all MTEXT and delete

Finally, I do a search and replace and replace all "~" with nothing (eliminating any cells that should be blank)

This method works, and means no fat fingering, and all the text are in exactly the correct location and they match the log exactly.

But this is tedious... and for any more than one sheet.. it takes forever.  For me at least this has the lowest amount of error.

[danallen]

I imagine Dotsoft's tool would speed things up and you could tweak your excel to minimize or eliminate much CAD work. The pretty low price would easily be made up in the hours you've mentioned. In the past I used a tool called Spanner and could get tables synchronized well.

[DIW_CADtech]

again.. tables.. can't use em
Client cad standards.. and exploding changes the location of the insertion points on all the MTEXT..

Tables are not a solution.

[danallen]

what happens to tables when you save as R12 version and reopen?

and in Bricscad v14, nothing moves/changes when you explode a table into lines & mtext

[danallen]

also Dotsoft says lines & text, not tables. Email Terry Dotson, he is very reachable and offers free evaluation
http://www.dotsoft.com/xl2cad.htm

[DIW_CADtech]

Again the problem will be control of the location of the text.  The client wants the text in very exacting locations.  I'm not sure that can be dictated from Excel.  No matter how advanced the software, the cells in Excel are determined by pts not inches or mm.. and the fonts are TTF's or other.. not SHX or AutoCAD Fonts.  I don't want to introduce that into the drawing. 

I will check out the link and see what the capabilities of the software are.

Again, speaking to tables... our client doesn't tolerate Mtext.   When you explode a table, all the text becomes MTEXT.  I then have to explode it... Assuming the Mtext has an alignment of bottom center, when you explode it the text doesn't move, but by default, the anchor point becomes "LEFT" not center.  I have to manually change the text from "LEFT" to "CENTER" and then re-align all the text to their expected positions.  That's a lot of work for 10 columns by 300 dwgs.  Rea-positioning 3000 pieces of text is not my idea of a fun time.

So tables are not an option.

Again, I will check out the software you recommend.. but my goal is not to transfer any formatting from the LOG to the CAD file. Just the data from each cell.

[ronjonp]

Maybe an easy way would be to save the log as a csv ( so it's just text ) file then parse the data. Then it's just placing the text in the right place.

[DIW_CADtech]

essentially that's what I am manually doing now.  I create the array of formatted individual text with a value of "~", and by clicking from an exploded table I transfer the data from MTEXT to TEXT.

but the log is always exactly the same format.  so I am wondering why a program couldn't be written to start at a specific X,Y in CAD start a piece of TEXT with a specific Style, and alignment of center. and pick from a cell .. say A:1 and drop in the data.. then move on to the next programed cell and preprogramed X,Y and do the same.   

This is a process I would use on every one of our client's projects.. the data will different on each project, but the Excell spreadsheet format and data locations will be exactly the same. 

I know what data is in what column.  that will never change.. what I don't know is how many rows.. so maybe that would be a variable I'd have to enter? 
Prompt for Project Log:  (navigate to file --> click open)
Prompt:  How many drawings in the set? 300

drops A:4 thru  A:304 as text in AutoCAD from  a specific X,Y in .2" intervals going down.. then follows suit with C:4   at a different X,Y  and D:4   and so on and so fourth. 

This is what I'm looking for. 

The text will always be of the same client provided style, height, layer, and alignment (center). 

So it should be easy to program a LSP for this.  I just don't have those programming skills.

 

[DIW_CADtech]

I have found this CODE, but i don't know how to modify it.. or call functions from it to perform the task I'm trying to accomplish

Code: [Select]

;-------------------------------------------------------------------------------
; Program Name: GetExcel.lsp [GetExcel R4]
; Created By:   Terry Miller (Email: terrycadd@yahoo.com)
;               (URL: http://web2.airmail.net/terrycad)
; Date Created: 9-20-03
; Function:     Several functions to get and put values into Excel cells.
;-------------------------------------------------------------------------------
; Revision History
; Rev  By     Date    Description
;-------------------------------------------------------------------------------
; 1    TM   9-20-03   Initial version
; 2    TM   8-20-07   Rewrote GetExcel.lsp and added several new sub-functions
;                     including ColumnRow, Alpha2Number and Number2Alpha written
;                     by Gilles Chanteau from Marseille, France.
; 3    TM   12-1-07   Added several sub-functions written by Gilles Chanteau
;                     including Cell-p, Row+n, and Column+n. Also added his
;                     revision of the PutCell function.
; 4    GC   9-20-08   Revised the GetExcel argument MaxRange$ to accept a nil
;                     and get the current region from cell A1.
;-------------------------------------------------------------------------------
; Overview of Main functions
;-------------------------------------------------------------------------------
; GetExcel - Stores the values from an Excel spreadsheet into *ExcelData@ list
;   Syntax:  (GetExcel ExcelFile$ SheetName$ MaxRange$)
;   Example: (GetExcel "C:\\Folder\\Filename.xls" "Sheet1" "L30")
; GetCell - Returns the cell value from the *ExcelData@ list
;   Syntax:  (GetCell Cell$)
;   Example: (GetCell "H15")
; Function example of usage:
; (defun c:Get-Example ()
;   (GetExcel "C:\\Folder\\Filename.xls" "Sheet1" "L30");<-- Edit Filename.xls
;   (GetCell "H21");Or you can just use the global *ExcelData@ list
; );defun
;-------------------------------------------------------------------------------
; OpenExcel - Opens an Excel spreadsheet
;   Syntax:  (OpenExcel ExcelFile$ SheetName$ Visible)
;   Example: (OpenExcel "C:\\Folder\\Filename.xls" "Sheet1" nil)
; PutCell - Put values into Excel cells
;   Syntax:  (PutCell StartCell$ Data$) or (PutCell StartCell$ DataList@)
;   Example: (PutCell "A1" (list "GP093" 58.5 17 "Base" "3'-6 1/4\""))
; CloseExcel - Closes Excel session
;   Syntax:  (CloseExcel ExcelFile$)
;   Example: (CloseExcel "C:\\Folder\\Filename.xls")
; Function example of usage:
; (defun c:Put-Example ()
;   (OpenExcel "C:\\Folder\\Filename.xls" "Sheet1" nil);<-- Edit Filename.xls
;   (PutCell "A1" (list "GP093" 58.5 17 "Base" "3'-6 1/4\""));Repeat as required
;   (CloseExcel "C:\\Folder\\Filename.xls");<-- Edit Filename.xls
;   (princ)
; );defun
;-------------------------------------------------------------------------------
; Note: Review the conditions of each argument in the function headings
;-------------------------------------------------------------------------------
; GetExcel - Stores the values from an Excel spreadsheet into *ExcelData@ list
; Arguments: 3
;   ExcelFile$ = Path and filename
;   SheetName$ = Sheet name or nil for not specified
;   MaxRange$ = Maximum cell ID range to include or nil to get the current region from cell A1
; Syntax examples:
; (GetExcel "C:\\Temp\\Temp.xls" "Sheet1" "E19") = Open C:\Temp\Temp.xls on Sheet1 and read up to cell E19
; (GetExcel "C:\\Temp\\Temp.xls" nil "XYZ123") = Open C:\Temp\Temp.xls on current sheet and read up to cell XYZ123
;-------------------------------------------------------------------------------
(defun GetExcel (ExcelFile$ SheetName$ MaxRange$ / Column# ColumnRow@ Data@ ExcelRange^
  ExcelValue ExcelValue ExcelVariant^ MaxColumn# MaxRow# Range$ Row# Worksheet)
  (if (= (type ExcelFile$) 'STR)
    (if (not (findfile ExcelFile$))
      (progn
        (alert (strcat "Excel file " ExcelFile$ " not found."))
        (exit)
      );progn
    );if
    (progn
      (alert "Excel file not specified.")
      (exit)
    );progn
  );if
  (gc)
  (if (setq *ExcelApp% (vlax-get-object "Excel.Application"))
    (progn
      (alert "Close all Excel spreadsheets to continue!")
      (vlax-release-object *ExcelApp%)(gc)
    );progn
  );if
  (setq ExcelFile$ (findfile ExcelFile$))
  (setq *ExcelApp% (vlax-get-or-create-object "Excel.Application"))
  (vlax-invoke-method (vlax-get-property *ExcelApp% 'WorkBooks) 'Open ExcelFile$)
  (if SheetName$
    (vlax-for Worksheet (vlax-get-property *ExcelApp% "Sheets")
      (if (= (vlax-get-property Worksheet "Name") SheetName$)
        (vlax-invoke-method Worksheet "Activate")
      );if
    );vlax-for
  );if
  (if MaxRange$
    (progn
      (setq ColumnRow@ (ColumnRow MaxRange$))
      (setq MaxColumn# (nth 0 ColumnRow@))
      (setq MaxRow# (nth 1 ColumnRow@))
    );progn
    (progn
      (setq CurRegion (vlax-get-property (vlax-get-property
        (vlax-get-property *ExcelApp% "ActiveSheet") "Range" "A1") "CurrentRegion")
      );setq
      (setq MaxRow# (vlax-get-property (vlax-get-property CurRegion "Rows") "Count"))
      (setq MaxColumn# (vlax-get-property (vlax-get-property CurRegion "Columns") "Count"))
    );progn
  );if
  (setq *ExcelData@ nil)
  (setq Row# 1)
  (repeat MaxRow#
    (setq Data@ nil)
    (setq Column# 1)
    (repeat MaxColumn#
      (setq Range$ (strcat (Number2Alpha Column#)(itoa Row#)))
      (setq ExcelRange^ (vlax-get-property *ExcelApp% "Range" Range$))
      (setq ExcelVariant^ (vlax-get-property ExcelRange^ 'Value))
      (setq ExcelValue (vlax-variant-value ExcelVariant^))
      (setq ExcelValue
        (cond
          ((= (type ExcelValue) 'INT) (itoa ExcelValue))
          ((= (type ExcelValue) 'REAL) (rtosr ExcelValue))
          ((= (type ExcelValue) 'STR) (vl-string-trim " " ExcelValue))
          ((/= (type ExcelValue) 'STR) "")
        );cond
      );setq
      (setq Data@ (append Data@ (list ExcelValue)))
      (setq Column# (1+ Column#))
    );repeat
    (setq *ExcelData@ (append *ExcelData@ (list Data@)))
    (setq Row# (1+ Row#))
  );repeat
  (vlax-invoke-method (vlax-get-property *ExcelApp% "ActiveWorkbook") 'Close :vlax-False)
  (vlax-invoke-method *ExcelApp% 'Quit)
  (vlax-release-object *ExcelApp%)(gc)
  (setq *ExcelApp% nil)
  *ExcelData@
);defun GetExcel
;-------------------------------------------------------------------------------
; GetCell - Returns the cell value from the *ExcelData@ list
; Arguments: 1
;   Cell$ = Cell ID
; Syntax example: (GetCell "E19") = value of cell E19
;-------------------------------------------------------------------------------
(defun GetCell (Cell$ / Column# ColumnRow@ Return Row#)
  (setq ColumnRow@ (ColumnRow Cell$))
  (setq Column# (1- (nth 0 ColumnRow@)))
  (setq Row# (1- (nth 1 ColumnRow@)))
  (setq Return "")
  (if *ExcelData@
    (if (and (>= (length *ExcelData@) Row#)(>= (length (nth 0 *ExcelData@)) Column#))
      (setq Return (nth Column# (nth Row# *ExcelData@)))
    );if
  );if
  Return
);defun GetCell
;-------------------------------------------------------------------------------
; OpenExcel - Opens an Excel spreadsheet
; Arguments: 3
;   ExcelFile$ = Excel filename or nil for new spreadsheet
;   SheetName$ = Sheet name or nil for not specified
;   Visible = t for visible or nil for hidden
; Syntax examples:
; (OpenExcel "C:\\Temp\\Temp.xls" "Sheet2" t) = Opens C:\Temp\Temp.xls on Sheet2 as visible session
; (OpenExcel "C:\\Temp\\Temp.xls" nil nil) = Opens C:\Temp\Temp.xls on current sheet as hidden session
; (OpenExcel nil "Parts List" nil) =  Opens a new spreadsheet and creates a Part List sheet as hidden session
;-------------------------------------------------------------------------------
(defun OpenExcel (ExcelFile$ SheetName$ Visible / Sheet$ Sheets@ Worksheet)
  (if (= (type ExcelFile$) 'STR)
    (if (findfile ExcelFile$)
      (setq *ExcelFile$ ExcelFile$)
      (progn
        (alert (strcat "Excel file " ExcelFile$ " not found."))
        (exit)
      );progn
    );if
    (setq *ExcelFile$ "")
  );if
  (gc)
  (if (setq *ExcelApp% (vlax-get-object "Excel.Application"))
    (progn
      (alert "Close all Excel spreadsheets to continue!")
      (vlax-release-object *ExcelApp%)(gc)
    );progn
  );if
  (setq *ExcelApp% (vlax-get-or-create-object "Excel.Application"))
  (if ExcelFile$
    (if (findfile ExcelFile$)
      (vlax-invoke-method (vlax-get-property *ExcelApp% 'WorkBooks) 'Open ExcelFile$)
      (vlax-invoke-method (vlax-get-property *ExcelApp% 'WorkBooks) 'Add)
    );if
    (vlax-invoke-method (vlax-get-property *ExcelApp% 'WorkBooks) 'Add)
  );if
  (if Visible
    (vla-put-visible *ExcelApp% :vlax-true)
  );if
  (if (= (type SheetName$) 'STR)
    (progn
      (vlax-for Sheet$ (vlax-get-property *ExcelApp% "Sheets")
        (setq Sheets@ (append Sheets@ (list (vlax-get-property Sheet$ "Name"))))
      );vlax-for
      (if (member SheetName$ Sheets@)
        (vlax-for Worksheet (vlax-get-property *ExcelApp% "Sheets")
          (if (= (vlax-get-property Worksheet "Name") SheetName$)
            (vlax-invoke-method Worksheet "Activate")
          );if
        );vlax-for
        (vlax-put-property (vlax-invoke-method (vlax-get-property *ExcelApp% "Sheets") "Add") "Name" SheetName$)
      );if
    );progn
  );if
  (princ)
);defun OpenExcel
;-------------------------------------------------------------------------------
; PutCell - Put values into Excel cells
; Arguments: 2
;   StartCell$ = Starting Cell ID
;   Data@ = Value or list of values
; Syntax examples:
; (PutCell "A1" "PART NUMBER") = Puts PART NUMBER in cell A1
; (PutCell "B3" '("Dim" 7.5 "9.75")) = Starting with cell B3 put Dim, 7.5, and 9.75 across
;-------------------------------------------------------------------------------
(defun PutCell (StartCell$ Data@ / Cell$ Column# ExcelRange Row#)
  (if (= (type Data@) 'STR)
    (setq Data@ (list Data@))
  )
  (setq ExcelRange (vlax-get-property *ExcelApp% "Cells"))
  (if (Cell-p StartCell$)
    (setq Column# (car (ColumnRow StartCell$))
          Row# (cadr (ColumnRow StartCell$))
    );setq
    (if (vl-catch-all-error-p
          (setq Cell$ (vl-catch-all-apply 'vlax-get-property
            (list (vlax-get-property *ExcelApp% "ActiveSheet") "Range" StartCell$))
          );setq
        );vl-catch-all-error-p
        (alert (strcat "The cell ID \"" StartCell$ "\" is invalid."))
        (setq Column# (vlax-get-property Cell$ "Column")
              Row# (vlax-get-property Cell$ "Row")
        );setq
    );if
  );if
  (if (and Column# Row#)
    (foreach Item Data@
      (vlax-put-property ExcelRange "Item" Row# Column# (vl-princ-to-string Item))
      (setq Column# (1+ Column#))
    );foreach
  );if
  (princ)
);defun PutCell
;-------------------------------------------------------------------------------
; CloseExcel - Closes Excel spreadsheet
; Arguments: 1
;   ExcelFile$ = Excel saveas filename or nil to close without saving
; Syntax examples:
; (CloseExcel "C:\\Temp\\Temp.xls") = Saveas C:\Temp\Temp.xls and close
; (CloseExcel nil) = Close without saving
;-------------------------------------------------------------------------------
(defun CloseExcel (ExcelFile$ / Saveas)
  (if ExcelFile$
    (if (= (strcase ExcelFile$) (strcase *ExcelFile$))
      (if (findfile ExcelFile$)
        (vlax-invoke-method (vlax-get-property *ExcelApp% "ActiveWorkbook") "Save")
        (setq Saveas t)
      );if
      (if (findfile ExcelFile$)
        (progn
          (vl-file-delete (findfile ExcelFile$))
          (setq Saveas t)
        );progn
        (setq Saveas t)
      );if
    );if
  );if
  (if Saveas
    (vlax-invoke-method (vlax-get-property *ExcelApp% "ActiveWorkbook")
      "SaveAs" ExcelFile$ -4143 "" "" :vlax-false :vlax-false nil
    );vlax-invoke-method
  );if
  (vlax-invoke-method (vlax-get-property *ExcelApp% "ActiveWorkbook") 'Close :vlax-False)
  (vlax-invoke-method *ExcelApp% 'Quit)
  (vlax-release-object *ExcelApp%)(gc)
  (setq *ExcelApp% nil *ExcelFile$ nil)
  (princ)
);defun CloseExcel
;-------------------------------------------------------------------------------
; ColumnRow - Returns a list of the Column and Row number
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 1
;   Cell$ = Cell ID
; Syntax example: (ColumnRow "ABC987") = '(731 987)
;-------------------------------------------------------------------------------
(defun ColumnRow (Cell$ / Column$ Char$ Row#)
  (setq Column$ "")
  (while (< 64 (ascii (setq Char$ (strcase (substr Cell$ 1 1)))) 91)
    (setq Column$ (strcat Column$ Char$)
          Cell$ (substr Cell$ 2)
    );setq
  );while
  (if (and (/= Column$ "") (numberp (setq Row# (read Cell$))))
    (list (Alpha2Number Column$) Row#)
    '(1 1);default to "A1" if there's a problem
  );if
);defun ColumnRow
;-------------------------------------------------------------------------------
; Alpha2Number - Converts Alpha string into Number
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 1
;   Str$ = String to convert
; Syntax example: (Alpha2Number "ABC") = 731
;-------------------------------------------------------------------------------
(defun Alpha2Number (Str$ / Num#)
  (if (= 0 (setq Num# (strlen Str$)))
    0
    (+ (* (- (ascii (strcase (substr Str$ 1 1))) 64) (expt 26 (1- Num#)))
       (Alpha2Number (substr Str$ 2))
    );+
  );if
);defun Alpha2Number
;-------------------------------------------------------------------------------
; Number2Alpha - Converts Number into Alpha string
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 1
;   Num# = Number to convert
; Syntax example: (Number2Alpha 731) = "ABC"
;-------------------------------------------------------------------------------
(defun Number2Alpha (Num# / Val#)
  (if (< Num# 27)
    (chr (+ 64 Num#))
    (if (= 0 (setq Val# (rem Num# 26)))
      (strcat (Number2Alpha (1- (/ Num# 26))) "Z")
      (strcat (Number2Alpha (/ Num# 26)) (chr (+ 64 Val#)))
    );if
  );if
);defun Number2Alpha
;-------------------------------------------------------------------------------
; Cell-p - Evaluates if the argument Cell$ is a valid cell ID
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 1
;   Cell$ = String of the cell ID to evaluate
; Syntax examples: (Cell-p "B12") = t, (Cell-p "BT") = nil
;-------------------------------------------------------------------------------
(defun Cell-p (Cell$)
  (and (= (type Cell$) 'STR)
    (or (= (strcase Cell$) "A1")
      (not (equal (ColumnRow Cell$) '(1 1)))
    );or
  );and
);defun Cell-p
;-------------------------------------------------------------------------------
; Row+n - Returns the cell ID located a number of rows from cell
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 2
;   Cell$ = Starting cell ID
;   Num# = Number of rows from cell
; Syntax examples: (Row+n "B12" 3) = "B15", (Row+n "B12" -3) = "B9"
;-------------------------------------------------------------------------------
(defun Row+n (Cell$ Num#)
  (setq Cell$ (ColumnRow Cell$))
  (strcat (Number2Alpha (car Cell$)) (itoa (max 1 (+ (cadr Cell$) Num#))))
);defun Row+n
;-------------------------------------------------------------------------------
; Column+n - Returns the cell ID located a number of columns from cell
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 2
;   Cell$ = Starting cell ID
;   Num# = Number of columns from cell
; Syntax examples: (Column+n "B12" 3) = "E12", (Column+n "B12" -1) = "A12"
;-------------------------------------------------------------------------------
(defun Column+n (Cell$ Num#)
  (setq Cell$ (ColumnRow Cell$))
  (strcat (Number2Alpha (max 1 (+ (car Cell$) Num#))) (itoa (cadr Cell$)))
);defun Column+n
;-------------------------------------------------------------------------------
; rtosr - Used to change a real number into a short real number string
; stripping off all trailing 0's.
; Arguments: 1
;   RealNum~ = Real number to convert to a short string real number
; Returns: ShortReal$ the short string real number value of the real number.
;-------------------------------------------------------------------------------
(defun rtosr (RealNum~ / DimZin# ShortReal$)
  (setq DimZin# (getvar "DIMZIN"))
  (setvar "DIMZIN" 8)
  (setq ShortReal$ (rtos RealNum~ 2 8))
  (setvar "DIMZIN" DimZin#)
  ShortReal$
);defun rtosr
;-------------------------------------------------------------------------------
(princ);End of GetExcel.lsp
I could use some assistance.