EXCEL COLUMN FORMATTING

I’m writing a report that uses an excel automation to output a workbook of 4 worksheets. I’d like to format the worksheets from the beginning (since I’ve read on the forum that if I don’t - my numbers will be currency…which is what’s currenly happenning[xx(]). Since I won’t know how much data I’ll have at this point, I’d like to just format the entire column. BUT…I can’t get the syntax right on referring to a column. It blows up! Here’s what I’ve got: xlSheetIC.Range(3).Columns.NumberFormat := ‘#,##0.00’; and I also tried: xlSheetIC.Range(‘C’).Columns.NumberFormat := ‘#,##0.00’; Can anyone give me a working example? I’ve read the VB help in Excel, and it’s not helping[:(].

Faithie, I haven’t tried this myself, but this is how I would do it in Delphi; Format := ‘$#,##0.00’; WkSheet.Range[‘C1’, ‘C1’].EntireColumn.NumberFormat := Format; Try this and see. Hope it helps. [:)]

I reccomend using style sheets instead of hardcording the format into each cell. This saves a little memory but more importantly this simplifies things when someone decides to change the formatting. And use named ranges too!

I’ve never heard of style sheets. Is this an Excel function, or Navision? (I’ve got plenty of Excel experience, but only a few months on Navision). Can you explain the “Style Sheets”? I’m outputting the data within a report, and the user can select whether or not they want the Excel file. The user then selects (using CodeUnit 412) where the to store the workbook and what to call it. I’d rather not have to have an object (like an Excel template) existing to support the report if I can manage not to. Thanks! [:)]

Faithie, I think what horrigan means is to use an already “styled” spreadsheet. This is what I recently did in a project where I was automating Microsoft Word via Navision. Open a template document (with all the formatting already in place), and then “save as” a different filename (so you can use the template again in the future). [;)]

Hi

quote:


Originally posted by faithiejlewis
… Here’s what I’ve got: xlSheetIC.Range(3).Columns.NumberFormat := ‘#,##0.00’; and I also tried: xlSheetIC.Range(‘C’).Columns.NumberFormat := ‘#,##0.00’; …


Did you also try: xlSheetIC.Range('C:C').Columns.NumberFormat := '#,##0.00'; bye André

THERE’S THE ANSWER[:D][:D][:D] (I just tried it Andre - gracias mucho!) xlSheetIC.Range(‘C:C’).Columns.NumberFormat := ‘#,##0.00’; It correctly formats the entire column, then I populate my data from the OnGetRecord and my problem with it looking like currency is solved! Thanks all!!! Ya know - the Excel Automation could become an addiction![:p]

Hi Faithie Nice to hear (read) this [:)]. One hint: It is (almost) always helpful to make the things first in Excel and record it with the macro recorder. So you can see what Excel expect. bye André

Hi Connull

quote:


Originally posted by CMDunbar
Faithie, I think what horrigan means is to use an already “styled” spreadsheet. This is what I recently did in a project where I was automating Microsoft Word via Navision. Open a template document (with all the formatting already in place), and then “save as” a different filename (so you can use the template again in the future). [;)]


It is nice to work with templates. I do so in many cases. But you (I [;)]) have to consider the path problem (if you don’t want to distribute this template to all computers). The UNC path (\MyServer\MyDirectory\MyFile) is the only way to avoid problems with e.g. E:. I don’t know whether UNC work with terminal server. bye André