Date format/value different when opening manually compare to VBA macro

[colmkav]

Hi,

for some reason when I run my macro today the dates in my file appear as 08/07/2012 when I open via the following VBA statement:

sub tmp

dim wbBalance as workbook
dim sBalancename as string

sBalanceName = "C:\temp\Balance.csv"
Set wbBalance = Workbooks.Open(sBalanceName)

end sub

However, if I open manually in Excel the dates appear as 07/08/2012. How can I solve this problem?

regards
Colm

 

[joeu2004]

when I run my macro today the dates in my file appear
as 08/07/2012 when I open via the following VBA statement: [....]
sBalanceName = "C:\temp\Balance.csv"
Set wbBalance = Workbooks.Open(sBalanceName) [....]
However, if I open manually in Excel the dates appear as
07/08/2012. How can I solve this problem?

I cannot duplicate this behavior.

Note that a CSV is simply ASCII text that Excel interprets each time as if
you entered the contents manually. So Excel will interpret 8/7/2012
according to the short-date form that is set in the Regional and Language
Options control panel. And that will be the default display format.

(But I vaguely and perhaps incorrectly recall that VBA has its own fixed
format. TBD.)

So the questions that come to mind are:
1. How does the date appear when you open the CSV file in Notepad?
2. How are you seeing the date after the Workbooks.Open statement? Are you
looking at the worksheet in Excel? Or are you looking at it in VBA; if so,
how: MsgBox, Debug.Print, Format, something else?
3. Are you seeing the different appearances of the date on the same
computer?

Can you upload example CSV and Excel files (devoid of any private data) that
demonstrates the problem to a file-sharing website? Post the "shared",
"public" or "view-only" link (aka URL; http://...) in a response here. The
following is a list of some free file-sharing websites; or use your own.

Box.Net: http://www.box.net/files
Windows Live Skydrive: http://skydrive.live.com
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidShare: http://www.rapidshare.com

 

[colmkav]

"colmkav" <[email protected]> wrote: > when I run my macro today the dates in my file appear > as 08/07/2012 when I open via the following VBA statement: [....] > sBalanceName = "C:\temp\Balance.csv" > Set wbBalance = Workbooks.Open(sBalanceName) [....] > However, if I open manually in Excel the dates appear as > 07/08/2012. How can I solve this problem? I cannot duplicate this behavior. Note that a CSV is simply ASCII text that Excel interprets each time as if you entered the contents manually. So Excel willinterpret 8/7/2012 according to the short-date form that is set in the Regional and Language Options control panel. And that will be the default display format. (But I vaguely and perhaps incorrectly recall that VBA has its own fixed format. TBD.) So the questions that come to mind are: 1. How doesthe date appear when you open the CSV file in Notepad? 2. How are you seeing the date after the Workbooks.Open statement? Are you looking at the worksheet in Excel? Or are you looking at it in VBA; if so, how: MsgBox, Debug.Print, Format, something else? 3. Are you seeing the different appearances of the date on the same computer? Can you upload example CSV and Excel files (devoid of any private data) that demonstrates the problem to a file-sharing website? Post the "shared", "public" or "view-only" link (aka URL; http://...) in a response here. The following is a list of some free file-sharing websites; or use your own. Box.Net: http://www.box.net/files Windows Live Skydrive: http://skydrive.live.com MediaFire: http://www.mediafire.com FileFactory: http://www.filefactory.com FileSavr: http://www.filesavr.com FileDropper: http://www.filedropper.com RapidShare: http://www.rapidshare.com

Thanks for your help. I managed to find a solution which seems to work. I use the method workbooks.OpenText to solve my problem. I needed to rename the file as a .txt file as it doesnt work on the CSV name file and then make the following vba statement

Application.Workbooks.OpenText sBalanceTxtFile, FieldInfo:=Array(Array(1,1), Array(2, xlDMYFormat)), DataType:=xlDelimited, _
TextQualifier:=xlTextQualifierDoubleQuote, Comma:=True

Still find it a bit bizarre that it should open differently in Excel to VBA.. But is sounds like you are saying you think that VBA has a separate fixedsetting that can't be changed.


In answer to your questions:
1) in notepad the date appears like "2622","11-07-2012"
2) Both visually and in the code it is displayed as "07-11-2012" (whereas manually opened it is "11-07-2012"
3) On just the one computer I am seeing the differences.

btw, I cant seem to see where I can attach a file to this post.

 

[joeu2004]

I cant seem to see where I can attach a file to this post.

That is why I gave you instructions for uploading to a file-sharing website.

sounds like you are saying you think that VBA has a
separate fixed setting that can't be changed.

That is a wild guess based on a vague recollection. I have not
double-checked.

Regardless, I would think that Workbooks.Open (as you did originally) would
simply pass the information to Excel to open the workbook. Therefore, I
would not think that would cause the cell to look differently in Excel (if
that is what you mean), unless Workbooks.Open chooses a different default
format for dates. (Seems unlikely.)

In any case, even if the __appearance__ of the date is different, I would
expect the actual date value assigned to a type Double variable or viewed
with a Number format would be the same.

1) in notepad the date appears like "2622","11-07-2012"
2) Both visually and in the code it is displayed as "07-11-2012"
(whereas manually opened it is "11-07-2012"

#2 is not explained precisely enough. Exactly what do you mean by
"visually" and "in the code"?

"Visually" in the Excel workbook when you look at the worksheet in Excel?

"In the code" how? Specifically what VBA statements are you using to see
the date "in the code"? Are you using type Date variables or type Double?

I usually avoid type Date because I have not liked how VBA interprets its
use in some contexts. (I forget the details.)

More importantly (perhaps; depends on context), what does Month(...the
date...) return in VBA?

I wonder if the __appearance__ is misleading and irrelevant, as long as the
__value__ is correct.

 

[colmkav]

<<<<<<<Regardless, I would think that Workbooks.Open (as you did originally) would
simply pass the information to Excel to open the workbook. Therefore, I
would not think that would cause the cell to look differently in Excel (if
that is what you mean), unless Workbooks.Open chooses a different default
format for dates. (Seems unlikely.)

In any case, even if the __appearance__ of the date is different, I would
expect the actual date value assigned to a type Double variable or viewed
with a Number format would be the same.>>>>>>

The actual value itself is different when I open it via VBA macro (workbooks.open). ie 41220 and 41101 if you convert to a number. There my code which is evaluating the value treats it as a different date. (7/11/2012 instead of 11/7/2012)

<<<<<<<<More importantly (perhaps; depends on context), what does Month(...the
date...) return in VBA?>>>>>>>>>>>>>

I indeed call the month function and get it returned as 11 instead of 7.

What I mean by visually is that when I step through the VBA code as look at opened file in Excel the date i 7/11/2012. If I just open the file manually in excel it is 11/7/2012.

Anyway, using workbooks.opentext after changing name to a txt file works fine it seems. So happy to use this.