Format Excel Column via code

[Guest]

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

 

[mcescher]

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.

 

[Guest]

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