Hi,

I wonder if it would be possible to use some kind of conditional
formating with an excel worksheet in a DAP? (using excel component)

I have a table in access.  Since the data I want to publish is a
complex summary, I cannot use traditionnal DAP`s.  The solution is a
linked excel table.  The problem is that I want my users to access it
trough the web so I dont what to load an excel file.  Since I need to
have the information live I cannot use a html file (using publish
function in excel) so dap's is the only way to go.  Even if the excel
componend for web pages offers limited capacities, it works very well.
 The only thing I need is to use conditionnal formating.

Is there any way to use it?  In cell formulas? Scrips?  Obviously VBA
wont work but if scrips works for simple DAPS could I make it work for
a spreedsheet?  If so, could I have a sample code so I can apply it to
my page.

( I dont know much about script, but I can adapt existing code to my
stuff.  I have been using it quite a bite for conditionnal formating,
linked queries, search functions etc.  So I cannot create stuff, just
adapt it! )

Thank you in advance!

---

Request for Question Clarification by mathtalk-ga on 13 Nov 2002 14:07 PST

Hi, alex4211:

I've done (OLE) automated generation of Excel spreadsheets with
conditional formatting from Access, so chances are good I might know
how to do what you want.  But I need you to clarify something.

What is a DAP?  It sounds like you are using Excel in some fashion to
compose a Web page.

regards, mathtalk-ga

---

Clarification of Question by alex4211-ga on 14 Nov 2002 05:45 PST

a DAP stands for "DATA Access Page" it`s basicaly an MS ACCESS report
in the form of WEB page.  This is the best way to use MS ACCESS
application in the Intranet.  It is much less user friendly than
tranditionnal reports but its possible to replace most of the
functions by the usage of Scripts. (NOT VBA)

IF you load access 2002 or 2000 you will see a section under reports
called "page" that is the DAP`S.  You build the report there and you
simply access the database with your browser.  Of course you need to
instable Office web components for this to work properly.

You can also do the same effect by using directly Frontpage.  If you
stat from a blank page do:
Insert -) Web component  -) spreedsheet and charts  -)Office
spreedsheets

You will then have an excel sheedsheet embeded in your web page.  This
way, your user will access Excel without load the full excel. 
Combined with others tools of the web, you get amazing reports.

Now, to clarify my problem, I just need a sample of what to do, so I
will write an example of my chart insted of uploading it.

Year   Dealer1      Dealer2   Total(qt)
2000     20%          30 %     33
1999     15%          25%      45
1998     10$           5%      21
etc

Now, lets say I would like any percentage to be higher than 20% to be
in red, between 15% and 20% in blue and the rest stays the same, in
black.  How do I do this?  there is no Conditional formating option
for this and the conditional formation throug script that I know
refers to MS access fields, not excel components.

This is a text explaning how to do a conditional formating in DAP`S
throug scrips.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k2/html/odc_customizecontrols.asp

(note: there are several article on this link. the example is for the
first one: "Apply conditional formatting to a control")

Hope this helps and awnsers the question you might have.

---

Request for Question Clarification by mathtalk-ga on 18 Nov 2002 09:25 PST

Hi, alex4211:

I do have a server that I can use to send you stuff, but I think it's
pretty much a one-way exchange.  However I'll use your example to
provide the Excel spreadsheet and code.

One question though: is there a requirement to do a multiple page
versus a single page report?  It looks like the event in which you tie
the formatting code would be different for a multi-page DAP.

regards, mathtalk-ga

---

Clarification of Question by alex4211-ga on 18 Nov 2002 10:03 PST

I am not certain I understand what is you question.

I think you are refering to the daps header section.  The script
example is for one section of the dap but could be extended to
multiple sections.   Dont worry with this, I will handle any ajustment
necessary.

All I need is a sample code that will enable my script to reconise
excel components fields.

Thank you very much!

Hi,
 To understand your problem correctly, can you please post a sample
xls sheet in a common server. This will help to give a solution.

Regards,
RAC

I gave an example and clarification of what i need in the message
above.  Please refer to it.  If you need further information, I will
provide it to you but I think this will help you.

Thank you

Thanks for the information, alex4211:

I suspect that the Excel spreadsheet is to be "accessed" more or less
statically, as a data source.  It follows that the VBScript
conditional formatting must apply to the Access "DAP", regardless of
where the ODBC data source might have been.

Since the MSDN library article you mention takes just this approach,
I'm wondering how I can best answer your question.  I could post a
more detailed explanation of what the code's logic and functionality
in the article amounts to and how to apply it to your example above,
for example.  Since Google Answers in its current form does not allow
for uploading and downloading code, generally this is handled by
including code samples in the the body of the answer (or question).

Perhaps if a "working example" is sufficiently important to your
needs, I can discuss with the Google Answers editors how best to
arrange for this.

regards, mathtalk-ga

Hi,
Some time ago I answered a question on conditional formatting for
excel. This is to do total based on select cell background color. Your
problem seems to be reverse of it. I given solution using VBA. The
question ref is
https://answers.google.com/answers/main?cmd=threadview&id=67275

As you are telling that vba won't work in DAP, I am not sure whether
cell formula will do that work. Will try and come back if I got it.

Hope it helps.
RAC

Thanks for the interest in my problem guys.

Ok, I am quit sure that the VBA solution will not work are WEB page do
not read VBA but they do read SCRIPTS.  In others words I would need a
similar solution that works with SCRIPTS or CELL formula (is that
possible?).

I think the way to go is to recycle the codding I sent in the article.
 Technicly there is not much to change, the header section is easy,
the problem is only to indentify the field.  Instead of using:
 
bandHTML.children("UnitPrice").style.color = "red"

I would need someting that refers to Cells in the Excel component.  I
have no freaking clue on how to program this however.

????
Excel.application.sheet1!(A1:A100).style.color="red"
??? 
Someting like that??!!???

If I have a working code here I can do the rest!!!!

Do you guys have a server I can use, I will send 2 daps one with a
sample excel and another with a working script of condititional
format!