Solved

Excel VBA - cell format

Posted on 2004-04-29
15
2,333 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
Technology Partners: 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

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!

Question has a verified solution.

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

Suggested Solutions

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

756 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