Alter Format Of Spreadsheet Column to Date using VBA

I have a routine (in the code window) which I use to alter the format of columns in a spreadhseet after I create it.  Currently it uses the statement

xlWs.Range("F:P").NumberFormat = "0"
 to alter the rows F thru P to numeric.

I want to do pretty much the same thing on another spreadsheet, only I want to alter column A to show the contents in Date time format.  Currently the contents of the cells are long dates such as 3/12/11 9:15:00 AM.  However, they display on the spreadsheet as 3/12/11 with no time shown.  I can manually alter each created spreadsheet in Excel using themenu options: Format, Cells, Category = 'Date', Type = "3/14/01 1:30PM' But this is very time consuming.  So I want to do it in VBA code, from Access after I create the spreadhseet.

I'm guessing my new statement will look something like this:

xlWs.Range("A:A").DateFormat =


but I don't know what goes after the '=' to format it to show the date and time.

Anyone know?

I tried
 xlWs.Range("A:A").DateFormat ="Long Date" but it wouldn't run.  Error 438 Object doesn't suppor this property or method.



Is there some where where these formats are documented?



Private Sub fixSpreadsheet(passedNameAndLoc As String)
 
Dim xlApp As Object, xlWb As Object, xlWs As Object
 
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open(passedNameAndLoc)
 
Set xlWs = xlWb.Worksheets(1)
xlWs.Range("F:P").NumberFormat = "0"
'repeat as needed
 
With xlWb
    .Save
    .Close
End With
 
Set xlWs = Nothing
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub

Open in new window

LVL 1
mlcktmguyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick67Commented:
xlWs.Range("F:P").NumberFormat = "dd-mmm-yy HH:mm AMPM"
is my preference--it'll give 01-Jan-11 12:00 AM

You could try
xlWs.Range("F:P").NumberFormat = "mm/dd/yy HH:mm AMPM"
0
mlcktmguyAuthor Commented:
Thank you, they both worked with one wrinkle.  The contents of the cell is:

3/14/2011  9:15:00 AM  

Using the first format this displayed as:

14-Mar-11 9:15:00 A3P3

using the second format iti displayed as:

3/14/11 9:15:00 A3P3

The date and time are correct but they are followed in both cases by 'A3P3' instead of 'AM'

Any ideas?


0
Nick67Commented:
Playing with it, it appears that AM/PM is the required format
"dd-mmm-yy HH:mm AM/PM"
or
"mm/dd/yy HH:mm AM/PM"

You could try
xlWs.Range("F:P").NumberFormat = "mm/dd/yy HH:mm AM/PM"

Dates are funny things.  That syntax changes for Word to Excel to Access!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mlcktmguyAuthor Commented:
That's it, thank you very much.  Are these formats documented anywhere?
0
mlcktmguyAuthor Commented:
Exactly what I needed
0
Nick67Commented:
It's always a swamp
The Excel ones are here (for now!)
http://office.microsoft.com/en-us/excel-help/number-format-codes-HP005198679.aspx

Access uses the same letters (y,m,d,h,s,AM/PM) but you use the format function
http://office.microsoft.com/en-us/access-help/format-date-and-time-values-HP001099015.aspx

Sometimes they use n for minutes because it's different than m for months

The Word ones for formulas don't google up to an MS page, they can be case sensitive
http://www.gmayor.com/formatting_word_fields.htm#basic_switch

Write a note to yourself and save it!
That way when the swamp shifts--and it will--you can look up your note to figure out how it's done :0
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.