Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format Excel Column via code
I am unhappy with the Access Transferspreadsheet and OutputTo actions because
they cutoff text at 255 characters. I've written my own routine to export an Access table or query to an excel spreadsheet, and it is working relatively well. However, I have found that it is not formatting my data correctly. Specifically, my Date fields are not being formatted as dates, but as integers. Assuming that I have a worksheet object (xlWks), how do I format Column 6 to display in a date format ("mm/dd/yyyy")? Thanks Dale |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format Excel Column via code
Hi Dale,
Cycle through the rows in your sheet xlWks.Cells(intRow, 6).NumberFormat = "mm/dd/yyyy" The quickest way to learn some of these things is to record a macro in Excel, and then look at the code it creates. That's helped me MANY times B-) HTH, Chris M. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format Excel Column via code
Chris,
I figured out the macro idea, but it recommended something along the lines of: wksht.Columns(6).Select Selection.FormatNumber "m/d/yyyy" When I brought that over to my code, I got an error (don't remember what it was) that highlighted "Selection" when I went to debug it. I tried: wksht.Column(6).Select 'this worked With Selection .FormatNumber "m/d/yyyy" end with But that generated an error too; #91, "Object variable or With block variable not set" I really don't want to do this row by row, but will if I have to. I would rather do it as I write the column headers. Thanks for the idea. Dale "mcescher" wrote: Hi Dale, Cycle through the rows in your sheet xlWks.Cells(intRow, 6).NumberFormat = "mm/dd/yyyy" The quickest way to learn some of these things is to record a macro in Excel, and then look at the code it creates. That's helped me MANY times B-) HTH, Chris M. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to conditional format all black after date specified in code? | Excel Discussion (Misc queries) | |||
Why the column code in my excel is 123... instead of ABC...? | Excel Discussion (Misc queries) | |||
Code Date Format Depending on Computer format | Excel Discussion (Misc queries) | |||
Excel - CSV Format and column width | Excel Discussion (Misc queries) | |||
code for converting data format in another format | Excel Programming |