Matt,
We use excel lookups all the time (V5.2) without a problem, I have pasted
the call from one of our rules below. We call Visual Basic using a .p and
the Call Program function and then generate an excel spreadsheet from Visual
Basic for Vantage to look up and return data. confid is an input in the
configurator and is used to create the spreadsheet name, hence the
difference in the way we identify the spreadsheet in our call. The only real
difference I can see is that we use column headings (Comment) to identify
the column we wish to return to Vantage and the contents of a cell in column
A to identify the row (confid + "_CtrlStr1") rather than the actual cell
addresses. In the example below column A is called JobIDPartID and column B
is Comment. If A6 = confid + "_CtrlStr1" then the contents of B6 will be
returned.
(ExcelLookup("D:\VTGData\" + confid + ".xls", "Sheet4", "Comment", confid +
"_CtrlStr1"))
I have set up a new Icon/Vantage.vtgfile called Vantage Develop and removed
the -q parameter from the vtg file. This means no caching and rule changes
are seen immediately. Whenever I am making configurator changes I log in
using this icon.
Re the slowness, it is an indication that Vantage cannot find either the
spreadsheet or the cell address and is spending a long time trying. (In your
case the cell address is confusing it) If it reads the path correctly you
would not notice any performance issues for a single lookup. There is a
performance hit but it would take a lot of lookups to have an impact. I have
one configuration with over 150 lookups which takes around 30 seconds to
run, other configurations with a similar number of rules but no lookups run
in 10 seconds or less. Yet another configuration with less rules overall and
around 15 - 20 lookups runs in 10 seconds or less.
Regards
John Walter
Hufcor Pty Ltd
-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
Matt Sturm
Sent: Thursday, 14 July 2005 11:48 PM
To: 'vantage@yahoogroups.com'
Subject: [Vantage] Configurator question vantage 6.1
I have been trying to have configurator add comments to an operation without
any success.
I have setup an excel file (Q:\notes.xls) with all of our finishing notes
that would be added to the operation.
I added the following rule to the operation in configurator.
JobOper.CommentText = ExcelLookup("Q:\notes.xls", "sheet1", "A", "1")
This is a simplified rule, it will eventually have a few if-then statements.
When I test it by doing a get details on a job, all that is in the comments
is "?" The data in cell A1 is "test"
I must be missing something. Does anyone have any suggestions?
Two more side notes.
I have been noticing that any new rules I add to the configurator do not
take effect until I restart vantage. Anyone else have this happen?
Since adding this excel lookup rule, the speed of the configurator has taken
a major hit. It is to the point of losing any speed benefits of having
configurator. Possibly related to the not working rule?
TIA
-------------------------------------------------------
Matt Sturm
IT Administrator
Winona Lighting
Phone: 507-454-5113 ext. 361
Fax: 507-452-8528
Web: <http://www.winonalighting.com/> Http://www.winonalighting.com
E-mail: <mailto:msturm@...> msturm@...
[Non-text portions of this message have been removed]
Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
Yahoo! Groups Links
We use excel lookups all the time (V5.2) without a problem, I have pasted
the call from one of our rules below. We call Visual Basic using a .p and
the Call Program function and then generate an excel spreadsheet from Visual
Basic for Vantage to look up and return data. confid is an input in the
configurator and is used to create the spreadsheet name, hence the
difference in the way we identify the spreadsheet in our call. The only real
difference I can see is that we use column headings (Comment) to identify
the column we wish to return to Vantage and the contents of a cell in column
A to identify the row (confid + "_CtrlStr1") rather than the actual cell
addresses. In the example below column A is called JobIDPartID and column B
is Comment. If A6 = confid + "_CtrlStr1" then the contents of B6 will be
returned.
(ExcelLookup("D:\VTGData\" + confid + ".xls", "Sheet4", "Comment", confid +
"_CtrlStr1"))
I have set up a new Icon/Vantage.vtgfile called Vantage Develop and removed
the -q parameter from the vtg file. This means no caching and rule changes
are seen immediately. Whenever I am making configurator changes I log in
using this icon.
Re the slowness, it is an indication that Vantage cannot find either the
spreadsheet or the cell address and is spending a long time trying. (In your
case the cell address is confusing it) If it reads the path correctly you
would not notice any performance issues for a single lookup. There is a
performance hit but it would take a lot of lookups to have an impact. I have
one configuration with over 150 lookups which takes around 30 seconds to
run, other configurations with a similar number of rules but no lookups run
in 10 seconds or less. Yet another configuration with less rules overall and
around 15 - 20 lookups runs in 10 seconds or less.
Regards
John Walter
Hufcor Pty Ltd
-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
Matt Sturm
Sent: Thursday, 14 July 2005 11:48 PM
To: 'vantage@yahoogroups.com'
Subject: [Vantage] Configurator question vantage 6.1
I have been trying to have configurator add comments to an operation without
any success.
I have setup an excel file (Q:\notes.xls) with all of our finishing notes
that would be added to the operation.
I added the following rule to the operation in configurator.
JobOper.CommentText = ExcelLookup("Q:\notes.xls", "sheet1", "A", "1")
This is a simplified rule, it will eventually have a few if-then statements.
When I test it by doing a get details on a job, all that is in the comments
is "?" The data in cell A1 is "test"
I must be missing something. Does anyone have any suggestions?
Two more side notes.
I have been noticing that any new rules I add to the configurator do not
take effect until I restart vantage. Anyone else have this happen?
Since adding this excel lookup rule, the speed of the configurator has taken
a major hit. It is to the point of losing any speed benefits of having
configurator. Possibly related to the not working rule?
TIA
-------------------------------------------------------
Matt Sturm
IT Administrator
Winona Lighting
Phone: 507-454-5113 ext. 361
Fax: 507-452-8528
Web: <http://www.winonalighting.com/> Http://www.winonalighting.com
E-mail: <mailto:msturm@...> msturm@...
[Non-text portions of this message have been removed]
Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
Yahoo! Groups Links