Excel Date Format Change with Vbscript

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Squashman

Thread Starter
Trusted Advisor
Joined
Apr 4, 2003
Messages
19,786
This is a continuation from this previous thread I started but is more specific to changing one more thing before I export the worksheet to a tab delimited file.

Need to add one more thing to this script and it has to do with the way the date is formatted in some of the cells. The cell gets exported as to what the cell format is versus what the true cell value is.

So for instance. The actual cell value that is typed into the cell is 12/23/2013 but the client put in a custom format of d-mmm so it displays as 23-Dec and that is how it gets exported to the tab delimited file. I need the cell to export to the tab delimited file as 12/23/2013.

So I tried this code to change the Date Formatting but this did not work.
Code:
If IsDate(rCell.Value) Then
     rCell.Value = DateValue(rCell.Value)
End If
Anyone else got any ideas.
 

Squashman

Thread Starter
Trusted Advisor
Joined
Apr 4, 2003
Messages
19,786
Well this is odd. I decided to print the cell value every time it finds a date and sure enough it prints the date correctly to the screen: 12/23/2013
Apparently I am not changing the correct value within the cell.
 

Squashman

Thread Starter
Trusted Advisor
Joined
Apr 4, 2003
Messages
19,786
Was looking at his code on StackOverFlow but not sure how to implement it because I want it to go through each cell and check if it is a date before it applies the new number format. This code has hard coded columns.
Code:
Dim objXLApp, objXLWb, objXLWs

Set objXLApp = CreateObject("Excel.Application")

objXLApp.Visible = True

Set objXLWb = objXLApp.Workbooks.Open("E:\Docs\Invoice.csv")

'~~> Working with Sheet1
Set objXLWs = objXLWb.Sheets(1)

With objXLWs
    .Columns("G:G").NumberFormat = "m/d/yyyy"
    .Columns("H:H").NumberFormat = "0.00"
End With
My current block of code.
Code:
For Each rCell In objSheet.UsedRange
	On Error Resume Next
	If IsDate(rCell.Value) Then
		rCell.Value = DateValue(rCell.Value)
	End If					
Next
 
Joined
Jul 25, 2004
Messages
5,458
It's unfortunate VBScript can't make use of the Format() function. I use the DatePart() often in lieu of it. But with the Excel application object referenced we can just utilize its properties/methods.

Unless I'm missing something, you could just use...
Code:
For Each rCell In objSheet.UsedRange
	On Error Resume Next
	If IsDate(rCell.Value) Then
		rCell.NumberFormat = "m/d/yyyy"
	End If					
Next
Remember, if you want what the cell is showing in the cell use the Text property. If you want the underlying value, use the Value property. Does this help?
 

Squashman

Thread Starter
Trusted Advisor
Joined
Apr 4, 2003
Messages
19,786
Thanks Zack. I guess I was over thinking that one.

I don't think I have any control over the way the Save As works. I believe it saves the files with the formatting which is why I needed to fix the number format.

But maybe you can answer one more question for me. While I was debugging this I added this line of code inside my Loop.
Code:
wscript.echo objExcel.ActiveCell.Address
It continually output the same output: $A$1
I thought it would output the row and column of the cell it was working on.
 
Joined
Jul 25, 2004
Messages
5,458
Well it doesn't look like your code activates any other cell, which is fine, activating a cell isn't needed to work with the object(s), so that makes sense.

Re SaveAs, if it's saved in Excel format, no, there's not much you can do, because it retains formatting of individual cells. You could always format the cells as text first, or precede cell data entry with a single apostrophe, which would force that cell to read as text regardless of the format applied.
 

Squashman

Thread Starter
Trusted Advisor
Joined
Apr 4, 2003
Messages
19,786
I like the apostrophe idea but there are some cells that the dates are calculated within the worksheet. If it is hard coded in the cell that seems to take care of any formatting but would kill the formula. Better to standardize all the dates to one format. This should work for exporting it to a tab delimited file. Just gave our Mainframe SAS programmer a test file. All the dates look formatted the same. Hope she doesn't have anything else to add to this.
 
Joined
Jul 25, 2004
Messages
5,458
You could wrap the date formulas in the TEXT() function? It works like the Format() function does (and is non-existant in VBScript). This would keep it calculated, but specify the format. The end result (Value) would, however, be textual, so dependent calculations may need adjustment(s).

But changing the NumberFormat property of the cell should work as well, so long as it's done after the date entry. Excel likes to change the formatting if it *thinks* it knows what you want, especially with dates. ;)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top