More exporting to EXCEL questions

[antihippy]

I'm experimenting with an export to Excel. Previously I got a column header backgrounds in the cells on the spreadsheet to be given a colour - on the CR they still have the colour. When I export this has stopped happening. I can't figure out why ...

 

[mbarron]

If you are using one of the named colors such as red, green, yellow, or magenta (among others) Excel will display the color. Or if you are using the Color() function and use one of the named colors value such as Color(255,0,255) for magenta, Excel will display the color. Excel will not display the color if you are only one digit off. Color(255,1,255) will not display in Excel.



Mike

 

[antihippy]

Hmm, so I can't use a custom colours in that case? (for example for corporate colours that are non-excel-standard)

 

[mbarron]

Eureka!!! There is a way. It only took a couple hours (would have been less if I knew macros a little better) It takes some manipulation of Excel's color pallette.

What the macros will be doing is changing one of Excel's default colors to the custom color and calling that new color by it's old name. My example below changes the Cyan (pallette position 8 in Excel and the named color Aqua in Crystal) to RGB of 100,200,200. In Crystal I set the background color to Aqua for exporting to the new color.

In Excel, launch the macro module (alt - F11) press Ctrl+R to show the project explorer window. There should be a VBAProject(Personal.xls) tree. Expand it until you see ThisWorkbook. Double click on ThisWorkBook to launch the code window. Place the following:

Public Sub Workbook_Open()
Call setcolor
End Sub

Next right clink on ThisWorkbook choose Insert/module... double click on the Module1 that was created. Paste the following into the code window.

Sub setcolor()
Application.OnWindow = "colorswitch"
End Sub

Sub colorswitch()
If ActiveSheet.Name = "test.rpt" Then
ActiveWorkbook.Colors(8) = RGB(100, 200, 200)
Else
ActiveWorkbook.Colors(8) = RGB(0, 255, 255)
End If
End Sub

In Crystal change you background to Aqua. When you export the aqua will change to your new color. Change the "test.rpt" to your fiel name, otherwise your Cyan will be changed to your custom color whenever you launch Excel.

I'm testing now to see if one of the more "obscure" Excel colors would work too.




Mike

 

[antihippy]

Thanks! Will give this a try. We use non standard coours in some of the formatting so it was going to be a pain to have to alter the fields by hand.

 

[mbarron]

One thing I forgot to mention - mostly because I didn't think about until much later. If the Excel file is going to distrbuted, it needs to be open once on a machine that has the Excel macros. This can be done either by saving it to disk and then opening (and saving of course) or by exporting it to the Application and then doing a Save As... Once the file is saved in Excel the pallette info is saved with the file.

Mike

 

[XIBOTRUM]

Can you someone show me how to export MEMO field from DBF format to EXCEL spreadsheet? Thanks.

I used Export to c:\temp type XLS
everything..looks ok, but the memo field from DBF (visual Foxpro) does not show in Excel file? hmm?

Thanks.