Solved

Excel VBA - cell format

Posted on 2004-04-29
15
2,288 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

911 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

17 Experts available now in Live!

Get 1:1 Help Now