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 |
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. |
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. |
All times are GMT +1. The time now is 01:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com