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.

On Mon, 25 Apr 2005 02:55:02 -0700, "Magius00"
<[email protected]> 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

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"
> <[email protected]> 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
>

On Mon, 25 Apr 2005 11:24:10 -0700, "Magius00"
<[email protected]> 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

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

On Wed, 4 May 2005 03:54:04 -0700, "Magius00"
<[email protected]> 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

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

On Sat, 7 May 2005 02:26:01 -0700, "Magius00"
<[email protected]> 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

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

On Mon, 9 May 2005 01:11:05 -0700, "Magius00"
<[email protected]> 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

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.

On Mon, 9 May 2005 09:20:26 -0700, "Magius00"
<[email protected]> 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