paste csv file

AI Training: For Busy Decision Makers & Professionals Book now

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

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

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

 

Training courses

 

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.


 

Access tip:

Hide Access Tables

One method of stopping your Access tables appearing in Database window, is to rename the table(s) with the prefix usys.

This converts the table into a system object, which cannot be viewed in the Database window.

You can show system objects in the Database window by the following:

Step 1: On the Tools menu, click Options.
Step 2: Click the View tab.
Under Show, select the System Objects check box.
Click OK.

View all Access hints and tips


Server loaded in 0.12 secs.