Home |
Search |
Today's Posts |
#1
|
|||
|
|||
days and months swapping when copying using VB
When running the code:
Set rTable = refCell.CurrentRegion Set NewSheet = Workbooks("ehealthgen1").Worksheets.Add rTable.Copy NewSheet.Range("a1").PasteSpecial (xlPasteValues) Excel 2003 swaps the days and months for half of the work area and just imports the rest of the dates as text. The source is a CSV file and the first column is dates. This code works fine in office 97 but not 2003. I have just noticed similar problems with other code where copying from one worksheet to another or workbook. I have check my language formats even gave checking the 1904 date format box a go but nothing i have tried is to any benifit. Any help on this matter would be appriciated. |
#2
|
|||
|
|||
On Mon, 25 Apr 2005 02:55:02 -0700, "Magius00"
wrote: When running the code: Set rTable = refCell.CurrentRegion Set NewSheet = Workbooks("ehealthgen1").Worksheets.Add rTable.Copy NewSheet.Range("a1").PasteSpecial (xlPasteValues) Excel 2003 swaps the days and months for half of the work area and just imports the rest of the dates as text. The source is a CSV file and the first column is dates. This code works fine in office 97 but not 2003. I have just noticed similar problems with other code where copying from one worksheet to another or workbook. I have check my language formats even gave checking the 1904 date format box a go but nothing i have tried is to any benifit. Any help on this matter would be appriciated. If your regional settings (Start/Control Panel/...) are not the same as the imported data, you may see this type of behavior. If that is the case, perhaps you could paste the data in as TEXT, and then do the Data/Text to Columns function to convert it properly to your dates. --ron |
#3
|
|||
|
|||
Unfortuntly this was my first port of call. The settings do match up i even
recreated the csv on the laptop desipte it contains no formatting. Worse thing is if i copy it manualy it copys it with no problems. Only if i use any macro code does it seam to not work. This of ocurse became even more puzzling when i saw halfway down the sheet that excel had imported the rest of the records as text insteadof as a date and i can't apply formatting to this. "Ron Rosenfeld" wrote: On Mon, 25 Apr 2005 02:55:02 -0700, "Magius00" wrote: When running the code: Set rTable = refCell.CurrentRegion Set NewSheet = Workbooks("ehealthgen1").Worksheets.Add rTable.Copy NewSheet.Range("a1").PasteSpecial (xlPasteValues) Excel 2003 swaps the days and months for half of the work area and just imports the rest of the dates as text. The source is a CSV file and the first column is dates. This code works fine in office 97 but not 2003. I have just noticed similar problems with other code where copying from one worksheet to another or workbook. I have check my language formats even gave checking the 1904 date format box a go but nothing i have tried is to any benifit. Any help on this matter would be appriciated. If your regional settings (Start/Control Panel/...) are not the same as the imported data, you may see this type of behavior. If that is the case, perhaps you could paste the data in as TEXT, and then do the Data/Text to Columns function to convert it properly to your dates. --ron |
#4
|
|||
|
|||
On Mon, 25 Apr 2005 11:24:10 -0700, "Magius00"
wrote: Unfortuntly this was my first port of call. The settings do match up i even recreated the csv on the laptop desipte it contains no formatting. Worse thing is if i copy it manualy it copys it with no problems. Only if i use any macro code does it seam to not work. This of ocurse became even more puzzling when i saw halfway down the sheet that excel had imported the rest of the records as text insteadof as a date and i can't apply formatting to this. There are some instances where Excel gets confused with dates, especially if they are imported as text. If you could post a sample of the contents of the csv file; the relevant part of the importing macro; the actual results; the expected results; your regional date settings; we could probably sort it out pretty quickly. It is likely that Excel sees all of your entries as text, but is mistranslating some and not translating the others, at all. --ron |
#5
|
|||
|
|||
Applogies for the late reply have been busy. Doing some more research on this
i found an article "Creating Office solutions for Use in Multiple Countries/Regions". Basicaly i can't get the information from my work's computer to my home one and i can't get the firewall team to let me out to these forums. Howver the data in the CSV file (csv contains no formatting) is written in an english style ie the text is 04/05/2005. This translates to the 4th of may 2005 due to the fact the machine is in england so am I and I use UK style formatting and so do the customers i work with. The problem appears to be that excel 2003 is hard coded to read this text string as US format, despite whatever language settings you have on your machine), meaning when ever i get to the 13th of the month it gets confused and just imports it as text. This means i then have a sheet saying the 4th of jan to december untill i get to the 13th of april at which point it imports it as text and all is well. The solutions as far as i can see it is to force excel to read the date properly (only way i can think of doing this at the current time is to save the csv as a worksheet import the data then deleate the worksheet) . Or to get excel to imort all the dates as text. I can't make any changes to the csv itself so changing the formatting in that isn't an option. Hope this clarafies things a bit |
#6
|
|||
|
|||
On Wed, 4 May 2005 03:54:04 -0700, "Magius00"
wrote: Applogies for the late reply have been busy. Doing some more research on this i found an article "Creating Office solutions for Use in Multiple Countries/Regions". Basicaly i can't get the information from my work's computer to my home one and i can't get the firewall team to let me out to these forums. Howver the data in the CSV file (csv contains no formatting) is written in an english style ie the text is 04/05/2005. This translates to the 4th of may 2005 due to the fact the machine is in england so am I and I use UK style formatting and so do the customers i work with. The problem appears to be that excel 2003 is hard coded to read this text string as US format, despite whatever language settings you have on your machine), meaning when ever i get to the 13th of the month it gets confused and just imports it as text. This means i then have a sheet saying the 4th of jan to december untill i get to the 13th of april at which point it imports it as text and all is well. The solutions as far as i can see it is to force excel to read the date properly (only way i can think of doing this at the current time is to save the csv as a worksheet import the data then deleate the worksheet) . Or to get excel to imort all the dates as text. I can't make any changes to the csv itself so changing the formatting in that isn't an option. Hope this clarafies things a bit Your timing is pretty good as I've been out of town for a week :-). But without all the information I requested in my previous post, I can only guess at possible fixes. I do know that your assumption about how Excel being hard coded to import dates in US format is NOT correct. If I set my regional settings to English(UK) style, I can save a column of dates as a .csv file. When I subsequently open that file in Excel, they are properly interpreted as the correct dates. It may be that one fix is to have your macro open the file as a txt file, and then use the built-in capabilities to convert it properly. But there may be other solutions once you provide the requested information. Best, --ron |
#7
|
|||
|
|||
I think i need to explain what happens first, but i can't provide an original
data from work and producing a csv with english date formats in the first column isn't hard and then importing it into excel 2003 can be done by anyone. What happens is the csv file is produced and sit's on a server which is then transfered to my machine. I then play with the data to get it to mean something but have to keep the csv intact due to the fact other poeple use it on other systems. Doing this for around 200 files a month is not a nice thing to do by hand. The macro i wrote runs perfectly in office 97 then upon being "upgraded" to 2003 i rewrote all my macros and found that it was swapping my days and months as per ammerican format. The reason i say this is hard caded in is because of this artcle: http://msdn.microsoft.com/library/de...ultCR.asp?_r=1 If you read it particulary the sections Applications That Use String Literals and Applications That Use External Data You see that it does assume this is in US-en format. I'm currently trying to work on getting the csv and saving it as an xml importing the data and then deleting it. Other than this i could either do as you sujest and copy all the csv's rename them to txt files, split the text and then import it but even easyer would be if i could get it to import the data as text with no formatting. I'm sure there was a way for me to do this in excel 97 but i can't seam to get it to work in 2003. Hope this clears things up a bit and if i get some free time next week i'll knock up an example csv file |
#8
|
|||
|
|||
On Sat, 7 May 2005 02:26:01 -0700, "Magius00"
wrote: You see that it does assume this is in US-en format. I'm currently trying to work on getting the csv and saving it as an xml importing the data and then deleting it. Other than this i could either do as you sujest and copy all the csv's rename them to txt files, split the text and then import it but even easyer would be if i could get it to import the data as text with no formatting. I'm sure there was a way for me to do this in excel 97 but i can't seam to get it to work in 2003. Hope this clears things up a bit and if i get some free time next week i'll knock up an example csv file Well, the problem is not really Excel but rather VBA and how it handles some of the data. If your application is exporting the data in English(UK) format, and your regional settings are English(UK), it should be possible to import the data with messing up the dates. But your VBA code may be responsible for the issues you are having. Reading the article makes it even more clear that in order to come up with an optimum solution, it is necessary to see exactly what your .csv file contains; and how you want the data to appear in your Excel workbook. It is likely that some minor changes in your importing macro may be all that is required. In one scenario, both changing the suffix (or copying and changing the suffix) to .txt and then importing using the Data/Text to Columns wizard to properly parse the date data can be easily automated using VBA. But, for example, my file Book1a.csv contains the following text strings (dates in UK format): 6/5/2005, 7/5/2005, 8/5/2005, 9/5/2005, 10/5/2005, 11/5/2005, 12/5/2005, 13/05/2005, 14/05/2005, 15/05/2005, 16/05/2005, 17/05/2005, 18/05/2005, 19/05/2005, 20/05/2005, 21/05/2005, If I just open it in Excel, with my US settings, I get the following: 6/5/2005 7/5/2005 8/5/2005 9/5/2005 10/5/2005 11/5/2005 12/5/2005 13/05/2005 14/05/2005 15/05/2005 16/05/2005 17/05/2005 18/05/2005 19/05/2005 20/05/2005 21/05/2005 where 6/5/2005 through 12/5/2005 are US style dates, and the remainder are text strings. However, if I use the following macro: ============================ Sub foo() Workbooks.Open Filename:= _ "C:\Book1a.csv" Range("A1:A16").TextToColumns Destination:=Range("A1"), _ DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ FieldInfo:=Array(1, 4) End Sub =========================== then the dates get converted from UK to US style and are all true dates: 5/6/2005 5/7/2005 5/8/2005 5/9/2005 5/10/2005 5/11/2005 5/12/2005 5/13/2005 5/14/2005 5/15/2005 5/16/2005 5/17/2005 5/18/2005 5/19/2005 5/20/2005 5/21/2005 I suspect something similar can be done with your data. But the details depend on the details of exactly what you have, and what you are trying to accomplish. --ron |
#9
|
|||
|
|||
Thanks for your help on this so far ron i think we're both on the right track
now. here is a small sample of what i get: 01/04/2005 00:03 301 0.55 0.92 208782 345748 24 01/04/2005 00:08 301 1.32 0.91 496332 341729 47 01/04/2005 00:13 299 0.48 0.82 178567 305225 25 01/04/2005 00:18 300 0.55 0.72 204856 268616 24 01/04/2005 00:23 299 0.66 0.98 245143 367172 24 It's 5 min data the first column being a date/time the second being an interval and then network stats. This needs importing as is to get me. 01/02/2005 08:55 01-Feb-2005 08:55 08:00 3309 01/02/2005 09:55 01-Feb-2005 09:55 09:00 3596 01/02/2005 10:55 01-Feb-2005 10:55 10:00 3602 01/02/2005 11:59 01-Feb-2005 11:59 11:00 3887 01/02/2005 12:59 01-Feb-2005 12:59 12:00 3601 The macro i use is this: Sub ImportNewSheet() 'Copys the table from the CSV to the eHealthGen workbook and closes the csv Set rTable = refCell.CurrentRegion Set NewSheet = Workbooks("ehealthgen1").Worksheets.Add rTable.Copy (NewSheet.Range("a1")) End Sub The table is picked up by using a refcell and then current region and setting this as rtable. I then use the copy function to move the data into a workbook where another macro does the formatting. If i could import all of these as the original text i would be happy. Otherwise i will re-write my macro to open it as a text file and delimit the file (already do this on some other work so not a problem) Thanks again Mark |
#10
|
|||
|
|||
On Mon, 9 May 2005 01:11:05 -0700, "Magius00"
wrote: Thanks for your help on this so far ron i think we're both on the right track now. here is a small sample of what i get: 01/04/2005 00:03 301 0.55 0.92 208782 345748 24 01/04/2005 00:08 301 1.32 0.91 496332 341729 47 01/04/2005 00:13 299 0.48 0.82 178567 305225 25 01/04/2005 00:18 300 0.55 0.72 204856 268616 24 01/04/2005 00:23 299 0.66 0.98 245143 367172 24 It's 5 min data the first column being a date/time the second being an interval and then network stats. This needs importing as is to get me. 01/02/2005 08:55 01-Feb-2005 08:55 08:00 3309 01/02/2005 09:55 01-Feb-2005 09:55 09:00 3596 01/02/2005 10:55 01-Feb-2005 10:55 10:00 3602 01/02/2005 11:59 01-Feb-2005 11:59 11:00 3887 01/02/2005 12:59 01-Feb-2005 12:59 12:00 3601 The macro i use is this: Sub ImportNewSheet() 'Copys the table from the CSV to the eHealthGen workbook and closes the csv Set rTable = refCell.CurrentRegion Set NewSheet = Workbooks("ehealthgen1").Worksheets.Add rTable.Copy (NewSheet.Range("a1")) End Sub The table is picked up by using a refcell and then current region and setting this as rtable. I then use the copy function to move the data into a workbook where another macro does the formatting. If i could import all of these as the original text i would be happy. Otherwise i will re-write my macro to open it as a text file and delimit the file (already do this on some other work so not a problem) Thanks again Mark Mark, I don't understand your examples. I don't see how you can have 01/04/2005 in one worksheet and, when you copy it to another, you get 01/02/2005 unless you are copying formulas and the references get screwed up and your formatting macro does something to make it look like 2005. If I understand you correctly, you are opening the .csv file in Excel and it looks OK. When you copy it to another sheet, and format it, it looks strange. Perhaps instead of using the Copy method alone, you should try Copy (with no Destination) followed by a PasteSpecial: rTable.Copy NewSheet.PasteSpecial _ Paste:=xlPasteValues Or maybe I'm not understanding your example. --ron |
#11
|
|||
|
|||
Appologise they are just examples copyed onto a disk.
My original code WAS using paste special (see opening post) What is happening is the csv data is copyed or the values using ranges of the same size are transfered. AS you said earlyer this means that it takes upto the 12th and converts it to american format and leaves the rest as text as it doesn't know what to do with it. I am going to write a new import macro to rename the file (should be fun) to a .txt and ten import and delimit the data when i get the time. Thanks for your help on this matter but unless you know how to import it as the original text sting i think the only other option is to go back a version of office as 2003 doesn't meet user needs. The banks and other customers i work with have all trialed 2003 and not gone with it i just wish the company i work for would trial anything befor forcing it on innocent emplyees. |
#12
|
|||
|
|||
On Mon, 9 May 2005 09:20:26 -0700, "Magius00"
wrote: Appologise they are just examples copyed onto a disk. My original code WAS using paste special (see opening post) What is happening is the csv data is copyed or the values using ranges of the same size are transfered. AS you said earlyer this means that it takes upto the 12th and converts it to american format and leaves the rest as text as it doesn't know what to do with it. I am going to write a new import macro to rename the file (should be fun) to a .txt and ten import and delimit the data when i get the time. Thanks for your help on this matter but unless you know how to import it as the original text sting i think the only other option is to go back a version of office as 2003 doesn't meet user needs. The banks and other customers i work with have all trialed 2003 and not gone with it i just wish the company i work for would trial anything befor forcing it on innocent emplyees. Well fortunately (or unfortunately) I have 2002 and not 2003 so my inability to replicate your problem may be version related. Renaming to *.txt and then using the VB version of the text-to-columns wizard should surely work, though. Good luck! --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I calculate EDATE using days not months? | Excel Discussion (Misc queries) | |||
converting days to months | Excel Worksheet Functions | |||
Converting sum of time | Excel Worksheet Functions | |||
difference between two dates in years, months and days. | Excel Worksheet Functions | |||
How do I sort by date (not days, weeks, months) in Excel 2000? | Excel Discussion (Misc queries) |