?
Solved

Excel VBA - cell format

Posted on 2004-04-29
15
Medium Priority
?
2,375 Views
Last Modified: 2008-01-16
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.
0
Comment
Question by:dba123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 2
  • +1
15 Comments
 
LVL 20

Expert Comment

by:dsacker
ID: 10952897
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
 
LVL 12

Expert Comment

by:Alfredo Luis Torres Serrano
ID: 10952917
The answer is

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

Hope this help
0
 
LVL 1

Author Comment

by:dba123
ID: 10952962
This doesn't work:

objSpreadsheet.Cells(1.1).Columns("D").NumberFormat="mm/dd/yyyy"
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:dba123
ID: 10952998
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
 
LVL 1

Author Comment

by:dba123
ID: 10953006
How do I essentially choose "date" format as if I was in Excel ?
0
 
LVL 1

Author Comment

by:dba123
ID: 10953009
If I purchased an Excel VBA book, is this basically Excel VBA then?
0
 
LVL 12

Expert Comment

by:Alfredo Luis Torres Serrano
ID: 10953249
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
 
LVL 20

Expert Comment

by:dsacker
ID: 10953563
Or you simply could have used Cells(1,1).NumberFormat = "mm/dd/yyyy"

That will default to your active sheet.
0
 
LVL 1

Author Comment

by:dba123
ID: 10953661
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
 
LVL 1

Author Comment

by:dba123
ID: 10953668
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
 
LVL 1

Author Comment

by:dba123
ID: 10954008
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
 
LVL 12

Expert Comment

by:Alfredo Luis Torres Serrano
ID: 10958620
The question belong to ASP not to VB.

For me no problem delete it
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 10990161
Closed, 500 points refunded.

modulo
Community Support Moderator
Experts Exchange
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month14 days, 9 hours left to enroll

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question