Excel VBA - cell format

I need to format the cell to be a date type

so far I am guessing I could use something like this string:

objSpreadsheet.Cells(1.1).Columns("D").NumberFormat="5"

but this doesn't do anything in my excel.  I am spitting out data from my ASP array into Excel and currently the dates are showing up in the wrong format.  For example, one date shows 38089.52997

so I need to format that column of data after my export from ASP to Excel to be  Date format in Excel.
LVL 1
dba123Asked:
Who is Participating?
 
moduloConnect With a Mentor Commented:
Closed, 500 points refunded.

modulo
Community Support Moderator
Experts Exchange
0
 
dsackerContract ERP Admin/ConsultantCommented:
You could set your spreadsheet to a date format as follows:

Cells(1,1).NumberFormat = "m/d/yy h:mm AM/PM"

                 or

Cells(1,1).NumberFormat = "mm/dd/yyyy"
0
 
Alfredo Luis Torres SerranoDevelopment Director / DBACommented:
The answer is

Book.Worksheets("SheetName").Columns("L").NumberFormat = "dd/mm/yyyy"

Hope this help
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
dba123Author Commented:
This doesn't work:

objSpreadsheet.Cells(1.1).Columns("D").NumberFormat="mm/dd/yyyy"
0
 
dba123Author Commented:
whoops, I meant this doesn't work. I had cells(1,1) in there and took that out

objSpreadsheet.Columns("D").NumberFormat="mm/dd/yyyy"
0
 
dba123Author Commented:
How do I essentially choose "date" format as if I was in Excel ?
0
 
dba123Author Commented:
If I purchased an Excel VBA book, is this basically Excel VBA then?
0
 
Alfredo Luis Torres SerranoDevelopment Director / DBACommented:
objSpreadsheet is the Excel object  or is the Shsst in the Excel object?

Which version of Excel library are u using, Microsoft Excel 10.0 object library?

The sintax for the instruciton is:

  ExcelObject.WorkSheets(Sheetname).Column("D")._
NumberFormat = "mm/dd/yyyy"

Hope this help


0
 
dsackerContract ERP Admin/ConsultantCommented:
Or you simply could have used Cells(1,1).NumberFormat = "mm/dd/yyyy"

That will default to your active sheet.
0
 
dba123Author Commented:
I am using an Office Web Component in my ASP page.  So if you were to look at my page, here is how I set the object and use it:

dim objSpreadsheet
      Set objSpreadsheet = Server.CreateObject("OWC.Spreadsheet")

      If reportname = "NewResponses" then

             objSpreadsheet.Range("A1:E1").Interior.Color = RGB(204,204,213)
             objSpreadsheet.Columns("D").NumberFormat="Date"
             objSpreadsheet.Cells(1,1).Value = "Company Name"
             objSpreadsheet.Cells(1,2).Value = "Email To"
             objSpreadsheet.Cells(1,3).Value = "CE Name"
             objSpreadsheet.Cells(1,4).Value = "Date Sent"
             objSpreadsheet.Cells(1,5).Value = "Date Responded"

Call objSpreadsheet.ActiveSheet.Export("C:\testing.xls",0)


I am not directly in Excel, rather the opposite.  I am in ASP exporting data from my VB array into a new Excel doc and need to format the data that is Exported to Excel.
0
 
dba123Author Commented:
I don't want to format individual cells here, but ranges of cells which is much more efficient.  This works just fine objSpreadsheet.Range("A1:E1").Interior.Color = RGB(204,204,213) so I don't see why this wouldn't work:

objSpreadsheet.Columns("D").NumberFormat="Date"

or

objSpreadsheet.Columns("D").NumberFormat="mm/dd/yyyy"

but it is not working when I open my newly created Excel Doc


0
 
dba123Author Commented:
In ended up doing a CONVERT(CHAR(10), dbfield, 101) in my sql statement in my ASP to get it to show right in Excel.

thanks for your efforts.  I will delete this post now since non of your responses solved my problem here.
0
 
Alfredo Luis Torres SerranoDevelopment Director / DBACommented:
The question belong to ASP not to VB.

For me no problem delete it
0
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.

All Courses

From novice to tech pro — start learning today.