Ad:

Visual Basic 4 / 5 / 6 Discussion Thread
Unsolved
Views: 41772

Page 1
2
Next

You are currently viewing page 1 of this multi-page discussion thread

Oct 7th, 2006

Excel vba 6.0 copy date cells date format

Expand Post »

I have written a number of macros which work successfully, but 1 is giving me some trouble. At the start of the macro, I open another excel worksheet and copy data from sheet using [Range("A1", "G30").Copy], then paste using [Range("A2").Select], [ActiveCell.PasteSpecial] on current workbook.

This pastes the data correct except for 1 cell, date and time cell.
This is in format dd/mm/yyyy hh:mm. But when pasted to sheet, the date is converted to American date. mm/dd/yyy hh:mm.

It doesn't mater what the cell format is when pasteSpecial is performed (within macro).

If the same action are done manually, the date is not changed !!!!

Any help would be much appreciated.

sirving

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

7 posts
since Oct 2006

Permalink

Oct 7th, 2006

Re: Excel vba 6.0 copy date cells date format

Are you sure there is no macro, formula, or function that is forcing the cell to behave this way in the template? Another solution, is to force the change by using "format" on the cell AFTER the paste operation...

Comatose

Team Colleague

Reputation Points: 361

Solved Threads: 214

Taboo Programmer

Offline

2,413 posts
since Dec 2004

Permalink

Oct 8th, 2006

Re: Excel vba 6.0 copy date cells date format

Click to Expand / Collapse Quote originally posted by Comatose ...

Are you sure there is no macro, formula, or function that is forcing the cell to behave this way in the template? Another solution, is to force the change by using "format" on the cell AFTER the paste operation...

This is extract of code used:

[If FileExists("C:\EM AXH in Period issue.csv") Then
Workbooks.Open Filename:="C:\EM AXH in Period issue.csv"
Cells.Select
Selection.Copy

Windows("template automatic EM last 24hrs iss14.xls").Activate
Sheets("access hub").Select
Range("A1").Select
ActiveCell.PasteSpecial]

Text being copied from EM AXH in PEriod issue.csv and pasted to template automatic EM last 24hrs iss14.xls, sheet access hub.

sirving

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

7 posts
since Oct 2006

Permalink

Oct 8th, 2006

Re: Excel vba 6.0 copy date cells date format

Click to Expand / Collapse Quote originally posted by sirving ...

This is extract of code used:

[If FileExists("C:\EM AXH in Period issue.csv") Then
Workbooks.Open Filename:="C:\EM AXH in Period issue.csv"
Cells.Select
Selection.Copy

Windows("template automatic EM last 24hrs iss14.xls").Activate
Sheets("access hub").Select
Range("A1").Select
ActiveCell.PasteSpecial]

Text being copied from EM AXH in PEriod issue.csv and pasted to template automatic EM last 24hrs iss14.xls, sheet access hub.

After furtherr investigation, I have found that if i open workbook C:\EM AXH in Period issue.csv manually, the the date format is correct (cell format is set to custom dd/mm/yyyy hh:mm), but if i open the same workbook using [Workbooks.Open Filename:="C:\EM AXH in Period issue.csv"] then the date in the cells is shown as mm/dd/yyyy hh:mm although the format of cell is still set to dd/mm/yyyy. Why is the cells being converted to american date format when the file is opened using a vba macro ???

sirving

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

7 posts
since Oct 2006

Permalink

Oct 28th, 2006

Re: Excel vba 6.0 copy date cells date format

Click to Expand / Collapse Quote originally posted by sirving ...

After furtherr investigation, I have found that if i open workbook C:\EM AXH in Period issue.csv manually, the the date format is correct (cell format is set to custom dd/mm/yyyy hh:mm), but if i open the same workbook using [Workbooks.Open Filename:="C:\EM AXH in Period issue.csv"] then the date in the cells is shown as mm/dd/yyyy hh:mm although the format of cell is still set to dd/mm/yyyy. Why is the cells being converted to american date format when the file is opened using a vba macro ???

I am having the exact same problem! Did you figure it out, or does anybody know how to get around this problem?

mrdarcy

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Oct 2006

Permalink

Dec 6th, 2006

Re: Excel vba 6.0 copy date cells date format

Click to Expand / Collapse Quote originally posted by mrdarcy ...

I am having the exact same problem! Did you figure it out, or does anybody know how to get around this problem?

Ok... here's the solution, add the <local:=true> parameter to your VBA macro, e.g.

Workbooks.OpenText Filename:="C:\Test1.csv", DataType:=xlDelimited, _
TextQualifier:=xlTextQualifierNone, FieldInfo:=Array(1, 4), Local:=True

SirWren

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Dec 2006

Permalink

May 10th, 2007

Re: Excel vba 6.0 copy date cells date format

Thankyou so much for this it has helped solve the same problem I have had for a few months.

Now it works perfectly!

MHamlyn

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since May 2007

Permalink

May 13th, 2007

Re: Excel vba 6.0 copy date cells date format

Thanks for the info.

The <local:=true> parameter option doesn't appear as an option
for me using Microsoft excel200 vba version 6.

I am still having the same problem

sirving

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

7 posts
since Oct 2006

Permalink

May 13th, 2007

Re: Excel vba 6.0 copy date cells date format

What dates are being changed ?

Is is dates at beginning of month e.g 7th May or does this happen with dates later in month e.g 17th May also ?

I have had a similar problem in VB5 where some dates change and others don't for which I don't yet have a solution.

DenisOxon

Reputation Points: 22

Solved Threads: 19

Posting Whiz

Offline

345 posts
since Jan 2007

Permalink

May 13th, 2007

Re: Excel vba 6.0 copy date cells date format

First two dates are correct (25/4/07) 2nd 2 dates are incorrect (5/2/07, 5/5/07 & 5/11/07)

Think the 5th is just a coincidence on this example.

sirving

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

7 posts
since Oct 2006

Page 1
2
Next

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in Visual Basic 4 / 5 / 6 Forum Timeline: [VB6] How to delete symbolic links?

Next Thread in Visual Basic 4 / 5 / 6 Forum Timeline: dbms mini project

DaniWeb Message

Cancel Changes

User Name
Password