Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date format/value different when opening manually compare to VBA macro
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date format/value different when opening manually compare to VBA macro
"colmkav" 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 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 a 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 MediaFi http://www.mediafire.com FileFactory: http://www.filefactory.com FileSavr: http://www.filesavr.com FileDropper: http://www.filedropper.com RapidSha http://www.rapidshare.com |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date format/value different when opening manually compare to VBA macro
On Wednesday, August 8, 2012 3:42:21 PM UTC+2, joeu2004 wrote:
"colmkav" 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 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 a 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 MediaFi http://www.mediafire.com FileFactory: http://www.filefactory.com FileSavr: http://www.filesavr.com FileDropper: http://www.filedropper.com RapidSha 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 fixed setting 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date format/value different when opening manually compare to VBA macro
"colmkav" wrote:
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. "colmkav" wrote: 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. "colmkav" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date format/value different when opening manually compare to VBA macro
<<<<<<<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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Incorrect date format when opening txt or csv files via macro | Excel Programming | |||
Compare dates (one cell not in date format) | Excel Discussion (Misc queries) | |||
switched date format opening dbf file | Excel Programming | |||
Date format when opening a .CSV file programmatically | Excel Programming | |||
Opening Add-in files manually | Excel Programming |