 |  |  |  |  |  |  |  |
Excel vba 6.0 copy date cells date format
 |
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.
•
•
Join Date: Dec 2004
Posts: 2,413
Reputation: 
Solved Threads: 211
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...
•
•
•
•
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.
•
•
•
•
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.
•
•
•
•
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 ???
•
•
•
•
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?
Workbooks.OpenText Filename:="C:\Test1.csv", DataType:=xlDelimited, _
TextQualifier:=xlTextQualifierNone, FieldInfo:=Array(1, 4), Local:=True
Thankyou so much for this it has helped solve the same problem I have had for a few months.
Now it works perfectly!
Now it works perfectly!
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
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
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.
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.
|
Other Threads in the Visual Basic 4 / 5 / 6 Forum
- Previous Thread: textbox to datagrid and database
- Next Thread: Running a VB app on pc without VB installed
| Thread Tools | Search this Thread |
You need to join our community in order to build your list of favorite forums. You can visit the forum index for a listing of all forums.
Related Forum Features
Latest Visual Basic 4 / 5 / 6 Posts
- Today's Posts
- Unanswered Threads
* 6 429 2007 access activex add age application basic beginner birth bmp calculator cd cells.find click client code college component connection connectionproblemusingvb6usingoledb copy creat ctrl+f data database datareport date delete dissertations dissertationthesis dissertationtopic edit error excel excelmacro file filename form hardware header iamthwee image inboxinvb internetfiledownload keypress label listbox listview liveperson login looping machine microsoft movingranges number objectinsert open oracle password prime program prompt range-objects readfile reading record refresh remotesqlserverdatabase report save search sendbyte sites sort sql sql2008 sqlserver subroutine tags textbox time urldownloadtofile vb vb6 vb6.0 vba visual visualbasic visualbasic6 web window windows
