apply style to excel spreadsheet column 1

[GarryC]

Hi,

Can anyone tell me how I apply a style to an excell column. ie currency to column A, percentage to column B etc.

 

[baltman]

Do you want to create a formatted Excel file, or automate a pre-existing one?

If your creating one, HTML is faster. See FAQ184-4704 for sample code.

If pre-existing, you can specify ranges and apply formats using via automation. See the code at the bottom of the FAQ and within the Excel automation add code like:

WITH .Range("A3:C5&quot
.WrapText = True
.Font.Bold = .T.
.NumberFormat ='#,##0.00'
ENDWITH


Brian

 

[GarryC]

Thanks Brian, Actually need to both so I will have a go at html as well.

 

[jimoo]

SET BRAINSTORMING ON

If you are sending data to the spreadsheet, and it has consistent format, open a blank spreadsheet and format it so it fits your nees (colunms, rows, datatypes, etc.) and then save it as a template.

Open this template, whether using VFP or another method, and send the data to it rather than a blank spreadsheet.

Remember, all documents are created in a template - even the standard document.

SET BRAINSTORMING OFF

Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[baltman]

Jim,

Writing an HTML file (even with formatting) is going to beat the pants off of sending (just) data to Excel.

Try my FAQ vs. sending the data to Excel via automation on a few thousand records and you'll see what I mean. And note the FAQ is demonsrating how to apply conditional formatting too.

The one performance hit is when Excel is opening and interpreting the HTML. In the files I create this way I've automated the open-and-save-as-Excel before .visible=.t.

And of course, Excel 2000 + is required to read load the resultant HTML files...

Brian

 

[GarryC]

Sorry, Back Again.

Unfortunatley most of my users are still on Office 97. Still cant get the code to work

oSheet.Columns("A:Q&quot.EntireColumn.AutoFit --Works OK
oSheet.Columns("A:B&quot.Select --Works OK
osheet.Cells("A1:B100&quot.NumberFormat = "$##,##0.00" --type mismatch
osheet.Selection.NumberFormat = '$##,##0.00' --Unknown name
oSheet.Cells.NumberFormat = "$##,##0.00" -- Entire sheet
oSheet.Selection.Style="Currency" --Unknown name

Any suggestion? Dosn't want to know about what is selected.

 

[AirCon]

Try this,

oSheet.Range('A1:B100').NumberFormat = '$##,##0.00'


-- AirCon --

 

[GarryC]

Thanks AirCon. That worked.

 

[AirCon]

You're welcome GarryC

-- AirCon --