You are not logged in. [Log In] KiXtart.org website » Forums » KiXtart » Basic Scripting » KiXtart Front-End: COM Automation, Excel data lookup
Register User       Forum List       Calendar       FAQ
Page 1 of 1 1
Topic Options
#209966 - 2015-02-17 02:45 AM KiXtart Front-End: COM Automation, Excel data lookup
Lipman Offline
Getting the hang of it

Registered: 2005-05-09
Posts: 52
Loc: Jersey Shore USA
I have been thinking about using KiXtart as a front-end to search an Excel ( XLS ) file.

The idea is to use KiXtart as a front-end to query for Tag Number in a column. KiXtart would use COM Automation and search the column for a match and provide the Row that number has been found in. Then either Highlight the identified row or extract data of that row and display it.

Any ideas ?
Top
#209967 - 2015-02-17 03:04 AM Re: KiXtart Front-End: COM Automation, Excel data lookup [Re: Lipman]
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22346
Loc: OK
Just do it.
_________________________
!

download KiXnet

Top
#209968 - 2015-02-17 03:24 AM Re: KiXtart Front-End: COM Automation, Excel data lookup [Re: Lonkero]
Lipman Offline
Getting the hang of it

Registered: 2005-05-09
Posts: 52
Loc: Jersey Shore USA
OK...

Any suggested resources to find what COM Objects are available to use for MS Excel ?
Top
#209969 - 2015-02-17 04:58 AM Re: KiXtart Front-End: COM Automation, Excel data lookup [Re: Lipman]
Allen Administrator Offline
KiX Supporter
*****

Registered: 2003-04-19
Posts: 4574
Loc: USA
While it should be located here, Glenn has a nice library of Excel Functions on his website that should have no problem doing what you want.

http://www.innotechcg.com/resources/resources.asp?ID=0:KixLib:xlLib.htm
_________________________
(... better days ahead)

Top
#209970 - 2015-02-17 05:22 AM Re: KiXtart Front-End: COM Automation, Excel data lookup [Re: Allen]
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22346
Loc: OK
MSDN. Excel application reference. Excel actually comes with reference as well
_________________________
!

download KiXnet

Top
#209971 - 2015-02-17 08:11 PM Re: KiXtart Front-End: COM Automation, Excel data lookup [Re: Lonkero]
Lipman Offline
Getting the hang of it

Registered: 2005-05-09
Posts: 52
Loc: Jersey Shore USA
Thanx gentlemen.


Top
#209972 - 2015-02-18 04:46 PM Re: KiXtart Front-End: COM Automation, Excel data lookup [Re: Lipman]
Jochen Administrator Offline
KiX Supporter
*****

Registered: 2000-03-17
Posts: 6380
Loc: Stuttgart, Germany
Also,
try searching "Excel.Application" (with the qoutation marks) in the search box right above the shout box in here .. should give you some examples to play with
_________________________



Top
#209973 - 2015-02-18 05:15 PM Re: KiXtart Front-End: COM Automation, Excel data lookup [Re: Jochen]
Lipman Offline
Getting the hang of it

Registered: 2005-05-09
Posts: 52
Loc: Jersey Shore USA
Good idea.

Obrigado.
Top
#209974 - 2015-02-18 06:21 PM Re: KiXtart Front-End: COM Automation, Excel data lookup [Re: Lipman]
Glenn Barnas Administrator Offline
KiX Supporter
*****

Registered: 2003-01-28
Posts: 4403
Loc: New Jersey
If you do wind up using the XlLib function library and make any improvements or additions that you'd be willing to share, reach out to me with the changes and I'll update the core library. As you might have seen, it's already a collaborative effort. \:\)

Glenn
_________________________
Actually I am a Rocket Scientist! \:D

Top
#209975 - 2015-02-18 07:13 PM Re: KiXtart Front-End: COM Automation, Excel data lookup [Re: Glenn Barnas]
Lipman Offline
Getting the hang of it

Registered: 2005-05-09
Posts: 52
Loc: Jersey Shore USA
You bet.

I'm playing with the functions now.
Top
#209976 - 2015-02-18 08:13 PM Re: KiXtart Front-End: COM Automation, Excel data lookup [Re: Lipman]
Lipman Offline
Getting the hang of it

Registered: 2005-05-09
Posts: 52
Loc: Jersey Shore USA
KiX v4.64

Unfortunately I keep getting "ERROR : error in expression!" "Line : 205"
using; Function xlRangeSearch()
where
$DogTagCol="N1:N999" is the Range
when
$DogTagCol="N" I get empty results
Top
#209977 - 2015-02-18 10:40 PM Re: KiXtart Front-End: COM Automation, Excel data lookup [Re: Lipman]
ShaneEP Moderator Offline
MM club member
*****

Registered: 2002-11-29
Posts: 2127
Loc: Tulsa, OK
Do you have an example excel spreadsheet to test with? Also the code you're using to call the function? I might have time to try to help troubleshoot.
Top
#209978 - 2015-02-18 10:44 PM Re: KiXtart Front-End: COM Automation, Excel data lookup [Re: ShaneEP]
ShaneEP Moderator Offline
MM club member
*****

Registered: 2002-11-29
Posts: 2127
Loc: Tulsa, OK
Also, make sure that all of the library functions are in your script, and not just the ones you think you need. For example, the xlRangeSearch function that you are referencing also utilizes the xlColRow function etc...
Top
#209979 - 2015-02-18 11:26 PM Re: KiXtart Front-End: COM Automation, Excel data lookup [Re: ShaneEP]
Lipman Offline
Getting the hang of it

Registered: 2005-05-09
Posts: 52
Loc: Jersey Shore USA
That may be it. xlColRow() was not in the KiX Script.

I'd have to extract data or create a dummy file as the XLS has PII in it.

Let me fix what i have.

Thank you !
Top
#209980 - 2015-02-19 12:44 AM Re: KiXtart Front-End: COM Automation, Excel data lookup [Re: Lipman]
Lipman Offline
Getting the hang of it

Registered: 2005-05-09
Posts: 52
Loc: Jersey Shore USA
No more errors. But I am also not getting results.

I created a dummy XLS with real data but I get NULL results.

I have the "test" XLS and KiX files.
Top
#209981 - 2015-02-19 06:10 AM Re: KiXtart Front-End: COM Automation, Excel data lookup [Re: Lipman]
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22346
Loc: OK
It really isn't that complex.
You need createobject line.
Workbooks open line
Active sheet line and then you can read cells:
$sheet.cells(1,1).value

I can't write an example on my phone but the web and this board has plenty.
_________________________
!

download KiXnet

Top
#209982 - 2015-02-19 06:38 AM Re: KiXtart Front-End: COM Automation, Excel data lookup [Re: Lonkero]
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22346
Loc: OK
Here are various examples. As you shall see, you get by with 3-4 lines if need be...
http://www.kixtart.org/forums/ubbthreads.php?ubb=showflat&Number=171406
_________________________
!

download KiXnet

Top
#209986 - 2015-02-19 04:50 PM Re: KiXtart Front-End: COM Automation, Excel data lookup [Re: Lonkero]
Lipman Offline
Getting the hang of it

Registered: 2005-05-09
Posts: 52
Loc: Jersey Shore USA
Awesome.

Once you break it down to its simplistic constructs, it's easy and once "in-tune", it is simple.

Thanx !
Top
Page 1 of 1 1


Moderator:  Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Arend_, Mart 
Hop to:
Shout Box

Who's Online
1 registered (Henriques) and 2516 anonymous users online.
details
Newest Members
fgarcia, Viginette, ManuvdWielNL, Sir_Barrington, batdk82
17889 Registered Users
My Cookies · Mark all read Powered by UBB.threads™ Contact Us · KiXtart.org website · Top

Generated in 0.148 seconds in which 0.107 seconds were spent on a total of 13 queries. Zlib compression enabled.
Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org