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
rmat = "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:
but I don't know what goes after the '=' to format it to show the date and time.
at ="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
Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing