paste csv file
Request a callback

We'll call during UK business hours

Name:
Number:
0207 987 3777
STL

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

Hi,

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", _
MultiSelect:=False)

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

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

Range("A1").CurrentRegion.Copy
Application.DisplayAlerts = False
ActiveWindow.Close
Application.DisplayAlerts = True
Sheets("Atlas File").Select
Range("A1").Select
Selection.CurrentRegion.ClearContents
Range("A1").Select
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
DisplayAsIcon:=False
Range("A1").Select
Sheets("Analysis").Select

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

Regards

Stephen


 

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 classes
London & UK

Live dates & prices »

On-site training

60-second quote »

E-Learning

From £99 · Call us »

Mailing list

Sign up for latest offers »

 

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.



Excel training
Course rating
4.7 stars - based on 24911 reviews
Microsoft Certified Partner Institute of Leadership and Management - Certified Courses Learning and Performance Institute - Accredited Training Provider Security Seal verified by visa, mastercard securecard

Connect with us: Google+ · Facebook · Twitter · LinkedIn · Pinterest

2nd Floor, CA House, 1 Northey Street
Limehouse Basin, London, E14 8BT
United Kingdom

info@microsofttraining.net

Resources