paste csv file

Forum home » Delegate support and help forum » Microsoft Access VBA Training and help » Paste in CSV file

Paste in CSV file

resolvedResolved · Medium Priority · Version 2007

Tue 24 May 2011, 11:36 replyReply Delegate Tom said...

Tom has attended:
Excel VBA Intro Intermediate course

Paste in CSV file


I have code which opens up a CSV file, copies it and pastes it into my Excel file. Within the CSV file there are dates in the format 01/06/2011 which dislays as Jun-11. However when i look at what has been pasted in it shows up as 11/01/2011. This is not correct. the code i have is as follows:-

Option Explicit

Dim filName As String

Sub Paste_In_Settlement_File()

ChDir "C:\Users\tlittle\Documents\Brent Settlement Analysis\May11 Front Month 2 Mins"

filName = Application.GetOpenFilename(FileFilter:="csv files,*.csv", Title:="Select Settlement File", _

Workbooks.OpenText Filename:=filName, DataType:=xlDelimited, comma:=True, Local:=True

'If Err.Number = 1004 Then
'MsgBox ("Please Choose Settlement File")
'Exit Sub
'End If

Application.DisplayAlerts = False
Application.DisplayAlerts = True
Sheets("Atlas File").Select
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _

End Sub

I have put i break in the code to see if it open and copies in the correct date format which is it does. Any help you can provide is greatly appreciated.

Many thanks

For upcoming training course dates see: Pricing & availability

Thu 26 May 2011, 10:57 replyReply Trainer Stephen said...

RE: Paste in CSV file

Hi Tom

Thanks for the question

Sorry for the delay in responding. This will require a little research and I will endeavour to get you a full response by the end of today or tomorrow morning




Access tip:

Random sampling

Data is th key to a good database, therefore checking is essential.

A good way of checking data is by random sampling. You can do this in a query, by typing the follow:
1) In the Field box create a RandonID field eg. Randon Id: Rnd(fieldname])

2) sort the field
3)Right click and chage the properties for To value to be the number of randon records you want to see.

4) Change the set to Show row for Randon ID to be False, add all the other fields you want to see and the run the query.

View all Access hints and tips

Training courses
London & UK

Live dates & prices »

On-site training

Get a quote »


Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.