paste csv file
Request a callback

We'll call during UK business hours

Name:
Number:
0207 987 3777

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

 

Please browse our web site to find out more about
advanced micorosoft access training and other Microsoft training courses.

Access tip:

Display current date & time in column of any width

The worksheet function =NOW() returns the current date & time. When entered into a column which is not wide enough to display the value NOW returns, the cell displays ###

View all Access hints and tips



Excel training
Course rating
4.7 stars - based on 18023 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