Topic: Get info from Excel file

[GDF]

Ok, here is one for you gurus...

I want to type in the routine function below "C:project" and have it read an excel file "Job List for Gary.xls" and find the matching project number and produce an alert dialog box with the excel info for project name only. Any hints, examples
or whatever would be greatly appreciated.

The project routine produces the project number like 060512.

Thanks

Code: [Select]

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;; New Project Contract Number Functions ;;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
(defun C:PROJECT ()
  (PROJECTIT)
  (cond       
    ((/= (getvar "userr1") 0)(princ (strcat "\n*** The Project CONTRACT Number is   " (rtos (getvar "userr1") 2 0) " ***")))
  )
  (princ)
)
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; New Project Set ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
(defun PROJECTIT (/ project realnom nom)
  (setvar "cmdecho" 0)
  (setq PROJECT (substr (getvar "dwgprefix") 14 6))       
  (cond       
    ((/= (atoi PROJECT) 0)(setvar "userr1" (atoi PROJECT)))
  ) 
  (princ) 
)
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
[code/]


Thanks

Gary

[GDF]

I found this link  http://www.theswamp.org/index.php?topic=8127.0
Ok, I've gotten this far...using Tim Willey's GrabAlleXcelCells routine.

Here is the result of opening up my excel file. Now I want to search all cells in A to find a matching
project number and when a match is found, get cell B which has the project name. Then place
that name into an alert dialog box.

(LOAD "E:/ExcelRoutines.lsp") C:GRABALLEXCELCELLS

Command: GrabAlleXcelCells
(("A" 1 2002.0) ("A" 2 "Project #") ("A" 3 20206.0) ("A" 4 20207.0) ("A" 5
20208.0) ("A" 6 2003.0) ("A" 7 "Project #") ("A" 8 30204.0) ("A" 9 30405.0)
("A" 10 30406.0) ("A" 11 31123.0) ("A" 12 31226.0) ("A" 13 2004.0) ("A" 14
"Project #") ("A" 15 40212.0) ("A" 16 40213.0) ("A" 17 40620.0) ("A" 18
40823.0) ("A" 19 40824.0) ("A" 20 41130.0) ("A" 21 41131.0) ("A" 22 41233.0)
("A" 23 2005.0) ("A" 24 "Project #") ("A" 25 50103.0) ("A" 26 50204.0) ("A" 27
50207.0) ("A" 28 50309.0) ("A" 29 50614.0) ("A" 30 50615.0) ("A" 31 50716.0)
("A" 32 50717.0) ("A" 33 50818.0) ("A" 34 50819.0) ("A" 35 50820.0) ("A" 36
50921.0) ("A" 37 51022.0) ("A" 38 51023.0) ("A" 39 51024.0) ("B" 2 "Project
Name") ("B" 3 "Stonewood Apartments") ("B" 4 "Cedar View Apartments") ("B" 5
"Peaks at Pine Bluff ") ("B" 7 "Project Name") ("B" 8 "Dripping Springs Senior
Village") ("B" 9 "Red River Senior Village") ("B" 10 "Cedar View Apartments")
("B" 11 "Chisholm Trail Sr. Village") ("B" 12 "Spring Oaks Apartments") ("B" 14
"Project Name") ("B" 15 "Villas At Bethel") ("B" 16 "Villa del Arroyo") ("B" 17
"Park Manor") ("B" 18 "Lakeside Manor") ("B" 19 "Stratton Oaks") ("B" 20
"Creekside Manor") ("B" 21 "Plano Housing Authority") ("B" 22 "Residence at the
Oaks-Phase II") ("B" 24 "Project Name") ("B" 25 "The Gardens at Conway
Commons") ("B" 26 "Balboa Apartments") ("B" 27 "San Gabriel Senior Village")
("B" 28 "Residences @ Lake May") ("B" 29 "Pebblebrook Townhomes") ("B" 31
"Hillcrest Manor Senior Community") ("B" 32 "Cobblestone Manor Sr Community")
("B" 33 "TownParc at Amarillo Apartments") ("B" 34 "Office Building") ("B" 38
"ARI-TEX Warehouse") ("B" 39 "Autumn Ridge ") ("C" 2 "Location") ("C" 3 "Little
Rock, AR") ("C" 4 "Mineral Wells, TX") ("C" 5 "Pine Bluff, AR") ("C" 7
"Location") ("C" 8 "Waco, TX") ("C" 9 "Vernon, TX") ("C" 10 "Mineral Wells,
TX") ("C" 11 "Belton, TX") ("C" 12 "Balch Springs, TX") ("C" 14 "Location")
("C" 15 "Bethel, TX") ("C" 16 "Midland, TX") ("C" 17 "Sherman, TX") ("C" 18
"Little Elm, TX") ("C" 19 "Seguin,TX") ("C" 20 "Killeen, TX") ("C" 21 "Plano,
TX") ("C" 22 "Dallas,TX") ("C" 24 "Location") ("C" 25 "Conway, AR") ("C" 26
"Houston, TX") ("C" 27 "Georgetown, TX") ("C" 28 "Florida") ("C" 29 "Sherman,
TX") ("C" 30 "DeSoto, TX") ("C" 31 "Lubbock, TX") ("C" 32 "Ft. Worth, TX") ("C"
33 "Amarillo, TX") ("C" 36 "Tomball, TX") ("C" 37 "Aledo, TX") ("C" 38 "Plano,
TX") ("C" 39 "Granbury, TX") ("D" 2 "Client") ("D" 3 "Brian Parent") ("D" 4
"Leslie Donaldson") ("D" 5 "Joe Kemp") ("D" 7 "Client") ("D" 8 "DF Affordable
Housing") ("D" 9 "DF Affordable Housing") ("D" 10 "DF Affordable Housing") ("D"
11 "Leslie Holleman") ("D" 12 "Ron Pegram") ("D" 14 "Client") ("D" 15 "Joe
Kemp") ("D" 16 "David Diaz") ("D" 17 "Richard Shaw") ("D" 18 "Richard Shaw")
("D" 19 "Colby Dennison") ("D" 20 "Richard Shaw") ("D" 21 "Helen Macey") ("D"
22 "John Martin") ("D" 24 "Client") ("D" 25 "Kenneth Fambro") ("D" 26 "John
Martin") ("D" 27 "Colby Dennison") ("D" 28 "Brian Parent") ("D" 29 "Richard
Shaw") ("D" 30 "Richard Shaw") ("D" 31 "Richard Shaw") ("D" 32 "Richard Shaw")
("D" 33 "Finlay") ("D" 34 "Kenneth Fambro") ("D" 36 "Kenneth Fambro") ("D" 37
"Kenneth Fambro") ("D" 38 "Rick Pickard") ("D" 39 "Ken Landers") ("E" 2 "# of
Units") ("E" 3 52.0) ("E" 4 64.0) ("E" 5 72.0) ("E" 7 "# of Units") ("E" 8
100.0) ("E" 9 76.0) ("E" 10 72.0) ("E" 11 60.0) ("E" 12 160.0) ("E" 14 "# of
Units") ("E" 15 156.0) ("E" 16 52.0) ("E" 17 196.0) ("E" 18 178.0) ("E" 19
100.0) ("E" 20 180.0) ("E" 22 200.0) ("E" 24 "# of Units") ("E" 25 100.0) ("E"
26 248.0) ("E" 27 100.0) ("E" 28 "56/80") ("E" 29 220.0) ("E" 30 50.0) ("E" 31
220.0) ("E" 32 220.0) ("E" 33 144.0) ("E" 37 112.0) ("E" 38 1.0) ("E" 39 94.0))
[quote/]

Thanks

Gary

[T.Willey]

I don't know if I have time, but I couldn't open the xcel file in xcel.

[GDF]

Ok, how about a different approach.

How would I do the following:

Open up a text file that has the following list:

012345; Job Name A
012346; Job Name B
012347; Job Name C
and so on...

The routine would open up the text file and search for the matching number, like "012345"
and get the name after the semicolon, like "Job Name A"

Gary

[dan19936]

I don't have your direct answer, but you could use doslib's INI reading routines.

Dan

dos_getini
Returns a string from the specified section in the Windows-style initialization (.INI) file.

Syntax
(dos_getini section entry filename [default])

Parameters
section
The section containing the entry.

entry
The entry whose associated string is to be returned.

filename
The filename of the initialization file.

default
A default return value. If entry is not found in the initialization file, default is returned.

Note, parameters are not case sensitive, so section and entry may be in any combination of uppercase and lowercase characters.

[T.Willey]

That wouldn't be to hard Gary.  I would just prompt for the project number, or get it how you do.  Then I would add a semi-colon to the tail end of it.  Then prompt the user to select the text file.  Then step through the text file until the end or you find a match.

[Kerry]

If you have control of the data file ....

::File C:\Projects.INI
[PROJECTS]
12345=Job Name A
012346=Job Name B
012347=Job Name C
012348=Job Name D
012349=Job Name E
012350=Job Name F
987=Job Name G
654=Job Name H

Code: [Select]

(SETQ ProjectName
        (DOS_GETINI "PROJECTS" "987" "C:\\Projects.ini")
)
;;-> "Job Name G"

(SETQ ProjectName
        (DOS_GETINI "PROJECTS" "012347" "C:\\Projects.ini")
)
;;-> "Job Name C"

[GDF]

If you have control of the data file ....

::File C:\Projects.INI
[PROJECTS]
12345=Job Name A
012346=Job Name B
012347=Job Name C
012348=Job Name D
012349=Job Name E
012350=Job Name F
987=Job Name G
654=Job Name H

Code: [Select]

(SETQ ProjectName
        (DOS_GETINI "PROJECTS" "987" "C:\\Projects.ini")
)
;;-> "Job Name G"

(SETQ ProjectName
        (DOS_GETINI "PROJECTS" "012347" "C:\\Projects.ini")
)
;;-> "Job Name C"

Kerry

Sweet, thanks, this will work perfectly...

Gary

[Jeff_M]

Hi Gary,
Can you repost your Excel file? I cannot open it either.

You asked for hints, so here goes......Excel has a Find method, it returns the cell it is found in. It's pretty simple to get the following cell from there........here's a quickie example

Code: [Select]

(cond ((setq excelobj (vlax-get-object "Excel.Application"))
       (setq excelexist T)
       )
      ((setq excelobj (vlax-create-object "Excel.Application"))
       (setq excelexist nil)
       )
      (t (Alert "Unable to initialize Excel!"))
      )
(setq itm2find 12345)
(vlax-put excelobj 'visible :vlax-true);;for testing.....
(setq wkbks (vlax-get excelobj 'workbooks))
(setq wkbk (vlax-invoke wkbks 'open "c:\\Test.xls"))
(setq wksht (vlax-get wkbk 'activesheet))
(setq used (vlax-get-property wksht 'usedrange))
(if (setq found (vlax-invoke used 'find itm2find))
  (vlax-get (vlax-get found 'next) 'value)
  )

Edit...Oh well, a bit late to the party.

[Kerry]

I was going to post similar stuff Jeff .. but the spec's changed, so ...

[VVA]

and have it read an excel file "Job List for Gary.xls"

I found this
XL  Get data from Excel into AutoCAD using Visual Lisp. Version 3.1
and this
KozMos VLXLS Project
    By using ActiveX, we can control Microsoft Excel from within Visual LISP. VLXLS is a fully open source project to demonstrate and execute the procedure of data IO (Input/Output) between Excel and AutoCAD.
    ActiveX uses variant but Visual LISP use normal list, VLXLS will translate between the two variable types very well. By doing so, the speed of reading data from Excel and writing data to Excel can be same as VB. We can pass HUGE list data into Excel within seconds! 

[GDF]

Code: [Select]

[quote author=Jeff_M link=topic=15809.msg191764#msg191764 date=1175555167]
Hi Gary,
Can you repost your Excel file? I cannot open it either.


Edit...Oh well, a bit late to the party.
[/quote]

Here is the excel file, change the file extension to xls. I didn't mean to change the subject. Just looking for the easiest solution.

Thanks to everyone for the help.

Gary

[terrycadd]

I highly recommend the KozMos Vlxls Project as well.
VVA, thanks for the link information.

[GDF]

Sorry I change the subject, but thanks to Kerry I now have a solution that works great.

Whenever I open a drawing that existing in the ini file it adds the info to the statusbar line.

Code: [Select]

::File V:\ARCH\Custom_Architettura\FILE\Projects.INI
[PROJECTS]
60101=Legacy Sr Housing
60205=Picadilly Estates

and so on.....


(defun modeexe (calling-reactor commandinfo) (C:MODE) (princ))

(defun C:MODE () (ARCH:ARCH#UNIT-READER) (MODEIT) (princ))
(defun MODEIT  (/ vis xre ProjectNum ProjectName ProjectDesc) 
  (setvar "cmdecho" 0)
  (setq ProjectNum (substr (getvar "dwgprefix") 14 6))
  (cond ((/= (atoi ProjectNum) 0) (setvar "userr1" (atoi ProjectNum))))
  (cond ((/= (getvar "userr1") 0)
         (setq ProjectName
                (dos_getini
                  "PROJECTS"
                  (rtos (getvar "userr1") 2 0)
                  (strcat ARCH#CUSF "FILE/ARCH_Projects.ini"))))) 
  (cond
    ((= ProjectName nil)(setq ProjectDesc ""))
    ((/= ProjectName nil)(setq ProjectDesc (strcat "    Project Name : [ " ProjectName " ]  ")))
  ) 
  (ARCH:EXPRESS-TOOLS)
  (if (member "acetutil.arx" (arx)) (ACET-UI-PROGRESS-DONE))
  ;;(C:SSBR) 
  (setq vis (strcat "Visre: " (rtos (getvar "visretain") 2 0)))
  (setq xre (strcat "Xload: " (rtos (getvar "xloadctl") 2 0)))
  (if (= ARCH#LREA nil)
    (setq ARCH#LREA "Off"))
  (setq ARCH#LUID (rtos (getvar "LUNITS") 2 0))
  (cond ((< (distof (rtos (getvar "ltscale") 2 0))
            (distof (rtos (getvar "ltscale") 2 1)))
         (setq ARCH#SLTS (rtos (getvar "ltscale") 2 1)))
        ((> (distof (rtos (getvar "ltscale") 2 0))
            (distof (rtos (getvar "ltscale") 2 1)))
         (setq ARCH#SLTS (rtos (getvar "ltscale") 2 1)))
        ((<= (distof (rtos (getvar "ltscale") 2 1))
             (distof (rtos (getvar "ltscale") 2 0)))
         (setq ARCH#SLTS (rtos (getvar "ltscale") 2 0))))
  (setq ARCH#OFFD (rtos (getvar "offsetdist")))
  (setq ARCH#PLTS (rtos (getvar "psltscale") 2 0))
  (setq ARCH#LTSD (strcat "[ " ARCH#SLTS " ]"))
  (ARCH:MODE-OSMODE)
  (cond
    ((/= ARCH#SCMD "")
     (setvar "modemacro"
             (strcat
               ARCH#LOGO "   Tx: $(getvar,TEXTSTYLE)    Dm: $(getvar,DIMSTYLE)"
               "$(if,$(getvar,SNAPMODE),   Sn:$(rtos,$(index,0,$(getvar,SNAPUNIT))))"
               "$(eval,$(getvar,users1))" "$(eval,$(getvar,users2))"
               "$(eval,$(getvar,users3))" "  Sc: " ARCH#SCMD "   Lts: " ARCH#LTSD
               ;;"    Off: " ARCH#OFFD
               ;;"    PsLts: " ARCH#PLTS "   Un: " ARCH#LUID " " ARCH#UNID
               "    LayR: " ARCH#LREA
               ProjectDesc
                            ;;"   " " vis "   " xre
               )))
    ((= ARCH#SCMD "")
     (setvar "modemacro"
             (strcat ARCH#LOGO
                     "    "
                     ;;"Tx: $(getvar,TEXTSTYLE)"
                     ;;"    "
                     ;;"Dm: $(getvar,DIMSTYLE)"
                     ;;"$(if,$(getvar,SNAPMODE),    Sn:$(rtos,$(index,0,$(getvar,SNAPUNIT))))"
                     ;;"$(eval,$(getvar,users1))"
                     ;;"$(eval,$(getvar,users2))"
                     ;;"$(eval,$(getvar,users3))"
                     ;;"    "
                     "< Please Run [Drawing Setup]...Enter ''FILE'' at the command line >" ProjectDesc))))
  (princ))
[code/]

Gary

[DanB]

I think I may want to add a few things of my own to this. However, looking over your code I do not see how you accomplished displaying the OSMODE settings. Is "(ARCH:MODE-OSMODE)" running from another snipet of code you have? Could you please explain what I may be over-looking?

Thanks