Conditional Formatting & Exporting Access 97 query result to Excel 2000

[hadwin]

Hi all,

I am really stuck on this problem Hopefully someone can give me a few pointers.

I am running Access 97, I want to do some conditional formatting on a form. There is no conditional formatting feature (i.e. formatting such as different background colour dependent on value) in Access 97.

I tried to do it by code but since I am using a continuous form, the formatting applied to all textbox. I then try to do it as a normal (i.e. non-continuous form), but I reached the maximum limit number of textbox I can add to a form

So instead, I have the idea of exporting the result of a query (the data I used to create my form) to Excel 2000 first. Then do the conditional formatting under vba in Excel. Since under Excel I can do more formatting feature such as adding border etc..

I managed to do the exporting using:
DoCmd.OutputTo acOutputQuery, "qry_FaultGroup_Details", acFormatXLS, "FaultGroup.xls", True, C:\Test\FaultGroup.xlt"

This will generate the result of the query to a new excel spreadsheet and open excel.

However, I am stuck on attaching my conditional formatting vba code (in excel) to this newly created spreadsheet.. (or even better automatically run the formatting code). I tried saving the code inside a excel template, and calling the template under OutputTo ... but I think the code didn't get save in the template for some reason.

Anyone can give me some bright ideas on how I can get around this?


Thanks in advance,


Hadwin.

 

[WayneRyan]

Hadwin,

There have been a lot of threads here dealing with Conditional Formatting
in code (A97). Use the Search facility here and look for "Continuous".

You can get what you want in A97.

Wayne

 

[hadwin]

Thanks Wayne for your quick respond...

I did look into tricks to get around the lack of conditional formatting with continuous form, both on this forum and just by google serching..

All the ideas I run across seems to be along the lines of .... putting a textbox with background behind the real textbox, then toggle the visible option to perform the colour changing.

But I don't think this option will work for my form. For example, in one column I have various names. If a name is repeated, I want to assign a colour and highlight it, so I can quickly see where this name appears in my form. However, the name and the number of rows changes dependent on the output of the query. So I guess there is no way to work out how many colour textboxes I will need in advance.

Are there some other alternatives I've missed?? Can you perhaps provide some specific links?

Do you have any suggestion on how I can implement my idea of exporting to Excel then run conditional formatting code in excel?

 

[WayneRyan]

H,

Sorry, no help for Excel.

Here's some links for Access:

http://www.access-programmers.co.uk/forums/showthread.php?t=68837&highlight=conditional+wayne

http://www.access-programmers.co.uk...9556&page=1&pp=15&highlight=conditional+wayne


Wayne

 

[scottn]

Excel has a rich object model and is pretty easy to automate from VBA in access. You'll need to add a reference to the Excel object library from Tools->References in the VBE. Once you've done that, you can use the Object Browser (F2) and Help to dig into Excel's object model... it's extensive so I can't tell you everything you need to know, but here's a bit of code to give you an idea of how to get started:

Dim xl as Excel.Application
Dim wb as Excel.Workbook
Dim ws as Excel.Worksheet

'Start a new instance of Excel
Set xl = new Excel.Application

'Make a new workbook
Set wb = xl.Workbooks.Add

'Get a reference to the first worksheet in the new workbook so we can start playing around
Set ws = wb.Worksheets(1)

'Drop some data in cell A1
ws.Range("A1") = myValue

'A range of cells
ws.Range("A2:A4") = myOtherValue

'A formula
ws.Range("A5") = "=Sum(A1:A4)"    'the formula is exactly the string you'd type into excel as a user

'Some formatting
With ws.Range("A5").Font
    .Size = 8
    .Bold = True
End With

'Make the excel app visible to users
xl.Visible = True

'Cleaning up
Set ws = nothing
Set wb = nothing
Set xl = nothing     'Kills your reference to the app, but doesn't quit.  This way the users can take it from here...

When you instantiate excel, it doesn't display. If you want your users to be able to interact with the app, you have to make it visible as in the code above. If you don't do this, the user won't be able to quit excel except via the task manager, because the excel user interface won't be available. You can quit excel from code by calling xl.Quit. Normally when I automate excel, I will instantiate excel, write out a report to a worksheet, then make the app visible and leave it running, as in the code above. Then the user can look at the report, modify it, or whatever...

 

[hadwin]

Thanks Soctt & Wayne,

Scott, for some reason I can't find the Excel Object Library under the list of references available on Access 97. But under Excel 2000 I clearly see a Excel2000 object listed. Are there certain steps I need to take before I can reference the object?

 

[hadwin]

I should add I can already reference Excel through code such as

Dim xlbook As Object 'Excel.Workbook
Set xlbook = xlapp.Workbooks.Add

Dim oSheet As Object
Set oSheet = xlbook.Worksheets(1)

but some function such as CopyFromRecordset are not available...

 

[Smee]

I have an excel sheet, which is populated with data from various Access queries.

The excel sheet has some conditional formatting set - but it doesn't provide all that I need.

I need a way to manipulate and change the conditional formatting in Excel from the Access code.

i.e. something along the lines of :

xlsheet.cells(1,5).conditionalFormat(iif value > target, 5, 10)

The reason why I need to do this is that 'target' changes. If it was always 7 then the conditional Format works fine on its own. However, sometimes it will be 7, othertimes 4 or 6 and conditional format can't handle that.

Thanks