Date recognition issue

Adar123

Board Regular
Joined
Apr 1, 2018
Messages
83
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Hi all,

I am running a VBA code which instructs to copy data from .csv file to .xls file.

Without any transformation to the dates, the year in the original data is suddenly recognized as the day of the month once copied (refer to the table below). I tried clearing formatting of the .xls file to ensure it does not affect the data copied into it. Also instructed to change the date format to the following:
VBA Code:
Range("B2:B" & lastRow).NumberFormat = "@"
Range("B2:B" & lastRow).NumberFormat = "MMMYY"

Neither approach makes a difference. Any suggestions?

Book13
AB
1Original in CSVAfter Copy in XLS
2Sep-2020-Sep
3Oct-2020-Oct
4Nov-2020-Nov
5Dec-2020-Dec
6Jan-2121-Jan
7Feb-2121-Feb
8Mar-2121-Mar
9Apr-2121-Apr
10May-2121-May
11Jun-2121-Jun
12Jul-2121-Jul
13Aug-2121-Aug
14Sep-2121-Sep
15Oct-2121-Oct
16Nov-2121-Nov
17Dec-2121-Dec
18Jan-2222-Jan
19Feb-22
Sheet1
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
I am copying my simplified section of VBA code in case you see anything there

VBA Code:
Sub copydata ()

Dim lastRow As Long
Dim myApp As Excel.Application
Dim wkBk As Workbook
Dim wkSht As Object

Dim dTod As String, dYes As String

'Adjust for Monday
If Format(Now(), "DDD") = "Mon" Then
    dTod = Format(Date - 2, "yyyy-mm-dd")
    dYes = Format(Date - 3, "yyyy-mm-dd")
Else

    dTod = Format(Date, "yyyy-mm-dd")
    dYes = Format(Date - 1, "yyyy-mm-dd")
End If

Dim localZipFile As Variant, destFolder As Variant  'Both must be Variant with late binding of Shell object
Dim Sh As Object

Dim strFileName As String
Dim strFileExists As String

'Check the file name ending
strFileName = "\\path\DataY_" & dYes & "_" & dTod & "_000501UTC.zip"
strFileExists = Dir(strFileName)
 
If strFileExists = "" Then     '.zip file to be unzipped
    localZipFile = "\\path\DataY_" & dYes & "_" & dTod & "_000502UTC.zip"
Else
    localZipFile = "\\path\DataY_" & dYes & "_" & dTod & "_000501UTC.zip"
End If
    
destFolder = "\\path\"    'destination folder of .zip file's unzipped contents
    
'Unzip all files in the .zip file
        
Set Sh = CreateObject("Shell.Application")
With Sh
        
.Namespace(destFolder).CopyHere .Namespace(localZipFile).Items
End With

'copy data from closed source workbook
Set myApp = CreateObject("Excel.Application")

'Check the file name ending
 
If strFileExists = "" Then
    Set wkBk = myApp.Workbooks.Open("\\path\DataY_" & dYes & "_" & dTod & "_000502UTC.csv")
Else
    Set wkBk = myApp.Workbooks.Open("\\path\DataY_" & dYes & "_" & dTod & "_000501UTC.csv")
End If
                 
lastRow = wkBk.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
wkBk.Sheets(1).Range("C2:H" & lastRow).Copy

myApp.DisplayAlerts = False
wkBk.Close
myApp.Quit
Set wkBk = Nothing
Set myApp = Nothing
Set wkBk = ActiveWorkbook
Set wkSht = wkBk.Sheets("NewData")
wkSht.Activate
Range("A1").Select
wkSht.Paste

lastRow = Range("A" & Rows.Count).End(xlUp).Row
'change date format

Range("C2:C" & lastRow).NumberFormat = "MMMYY" 'This line makes to difference to the result

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top