Formatting Excel from Access using VBA

[Malcy]

Hi Guys
I am getting is a muddle here. I have got two columns where I want a £, a thousands comma and no decimal places, and other columns where I want similar but with the pence shown.
The code I am using in Access is

' Set the cell format for Column F & G
    oSheet.Columns("F:G").NumberFormat = "£###,###"
    
' Set the cell format for Column H to O
    oSheet.Columns("H:O").NumberFormat = "£##,###.##"

If you look at the image I uploaded the results are less than satisfactory. Somehow I need to make it work so that if the cell is empty, or 0 then the £ doesn't show, and also if there are one or two 0 in the pence part they should show as 0
I have a hunch that my use of # is not right but I very rarely do this kind of thing, especially as I hate Excel!!!
I have several other bits I need to do, like set row heights and column widths to suit what client wants.
Also does anyone know how to set a range of cells so that they will text wrap?
Any thoughts or pointers would be really useful
Thanks

 

[shades]

As for the text wrap, you could define the range (MyRng) and then use it as follows:

With MyRng
   .WrapText = True
End With

While it doesn't seem to make sense using the With clause, it allows more flexibility if you need to set up other aspects of alignment as well.

Regarding the first part. Perhaps you could record your macro in Excel and do what you want with formatting, then see how that code looks and incorporate it into your Access code.
________
UNIVERSAL HEALTH

 

[Malcy]

Thanks Shades
I got the text wrap OK with a bit of Googling and experimentation.
Might experiment with the macro idea - never actually done on in Excel - and not in Access for the last five years!!!