Solved

Excel VBA - cell format

Posted on 2004-04-29
15
2,279 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
  • 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
 
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now