I have to export some data with DoCmd.OutputTo or DoCmd.TransferSpreadsheet into Excel and format the Data (Colors, Cell width, etc).

But there's one problem, the color palette changes from default to something else (see links). Now, when I try to set backgroundcolors with .Interior.Color or .Interior.ColorIndex, the Colors are all crap.

Anyone can help?

right: http://img228.imageshack.us/img228/5...rsright2zz.jpg
wrong: http://img258.imageshack.us/img258/3...rswrong5ns.jpg

It looks like Access has a different custom color pallette than Excel. What values are you using for the pallatte? Can you cahnge to RGB values, for example?


mmcdonal

Same thought here.

I tried using RGB, but that's not working either.

I have to use ColorIndex 46 (that's light orange or #FF9900 or 255,153,0). Problem here is that I can't define that color, it is rounded in some sort. I get the same yellow down to somewhere around 255,130,0, then it changes to red.

There are some funny things too:
- when I create a new Excel File in Excel, Color Pallette is OK.
- when I export the data manually (with export function from Access, not VBA), Color Pallette is OK
- only when exporting (through DoCmd.OutputTo or DoCmd.TransferSpreadsheet) in VBA, Color Pallette is not OK

I really hate things like this...

simon

Try this article: http://support.microsoft.com/default...288412&sd=tech

My understanding is that if you deviate from Excel's limited color pallette, you will get an approximation by Excel. Think of it as dumb dithering. See if the article helps to set the pallette. I am not sure it will.


mmcdonal

Well, if anyone knows a way to change the default color pallette by VBA, that would it be ;) But thanks anyway.

I tried to use decimal value for FF9900, but that didn't work either.

I had the same problem with VBA creating an Excel spreadsheet from Access.

Use this Excel command in VBA... .ActiveWorkbook.ResetColors

This will reset the color pallette to what you want.

Good Luck,

MGreenInOKC

mgreen