VBA copy flipping dates

[ianto2842]

Hi, I have a pice of code that should open a .csv workbook based on the cell reference (B6 = full file with path, B7=filename), copy all of the data in range A2:AK500. It should then revert back to the main workbook and paste this in cell A1 of the tab 'Paste'. It then closes the B7 workbook. This all works fine. The problem comes when you check the data. It seems to flip the date and month around, with some pasting correctly, some flipped, some in the 44812 format, some as text. The datafile is a csv and all the data is in the 44812 format.

If I open the csv and copy the data manually and paste it then it works totally FINE, just not when the vba does it. So that tells me its an issue with the vba, not the source data.

Thoughts? Thanks!

Sub Part2CopyInData()
'

'Declaring variables
Dim FileName As String
Dim WBName As String
Dim WB As Workbook

'Getting file path and file name from the textbox
FileName = Range("B6").Value
WBName = Range("B7").Value

'Open the Excel workbook
Set WB = Workbooks.Open(FileName)

'Copy data from csv workbook
Range("A2:AK501").Copy

'Move to Converter and unhide
Workbooks("Converter.xlsm").Activate
Sheets("Paste").Visible = True

'Paste
Sheets("Paste").Select
Range("A1").Select
ActiveSheet.Paste

'Move to Daily File and close
Application.DisplayAlerts = False
Workbooks(WBName).Activate
ActiveWorkbook.Close
Application.DisplayAlerts = True

End Sub

 

[RoryA]

If the data is stored in the CSV file in integer format, not date, then what you are describing really should not happen. But when opening text files in code that have dates in, you should typically specify the Local argument:

Set WB = Workbooks.Open(FileName, Local:=True)

 

[ianto2842]

If the data is stored in the CSV file in integer format, not date, then what you are describing really should not happen. But when opening text files in code that have dates in, you should typically specify the Local argument:

Set WB = Workbooks.Open(FileName, Local:=True)

That's fantastic! Seems to have fixed the problem!
Can I ask what the "Local:=True" part actually means?

 

[RoryA]

It tells the code to use your regional settings, rather than the US ones that VBA defaults to, to interpret the data in the file.

 

[ianto2842]

Ahhh That makes sense. Thanks very much for your help! It's amazing how much of a difference a couple of extra little words can make