Adjust excel-columnformat on export

[fluidmind]

Hi guys!

I've succesfully exported a lot of data to excel, but when I export date-columns the format in Excel remain standard-numbers... Is there any way I can change the column-format to date with an SQL-line.

I've got a line in my code that adjusts the column-width and works

.Columns("A").ColumnWidth = 9

So I guess I'm looking for something like:

.Columns("A").ColumnFormat = Date

... which by the way doesn't work

Thanks in advance

//JR

 

[pbaldy]

A helpful trick I learned in situations like this is to use the macro recording feature in Excel to perform the task, and then examine the code that was created. I can usually adapt it to my Access code.

 

[fluidmind]

Thanks

Thanks...

It's absolutely worth a try...

//JR

 

[fluidmind]

Nope...

Hi there!

I just tried your idea, but the code is not working in Access. Excel produces this line in the edit-macro function:

Columns("D:D").Select
Selection.NumberFormat = "m/d/yyyy"

Any ideas on how to apply this to Access???

//JR

 

[elbweb]

so you want to make the datatype for this field in the db to be a date instead of just a number? my advice is to copy everything and make a back up and then go into the design view of one of them and change the type of the field to date/time instead of number.

after you do that you can change the format (i.e. long date/short date) on the bottom pane of the table design window.

if this doesn't work (i.e. the data isn't regular enough for access to change it) you would need to write code to cycle through the records and put the number into a new field that was a date time to begin with.

 

[fluidmind]

The format is allready set to date/time

Hi again!

Thanks very much for replying. But my format is allready set to date/time... A report shows the data as it's supposed to be, but when exported the date-columns are totally strange...

They can always be changed manually, but there must be a simple line out there that adjusts the column-format...

But thanks again, though!

 

[elbweb]

heres an option, try to fix it once, and then just link an excel documentto the data source within the access file, then you wont have to do it every time you export it...

could you give an example of what it does? then maybe we can figure out what is going on?

 

[pbaldy]

Not sure; this worked in a test (xl is the Excel variable):

xl.Columns("F:F").Select
xl.Selection.NumberFormat = "m/d/yyyy"