Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

paste data other csv

Forum home » Delegate support and help forum » Microsoft Access VBA Training and help » Paste in data from other from csv file

Paste in data from other from csv file

ResolvedVersion 2007

Tom has attended:
Excel VBA Intro Intermediate course

Paste in data from other from csv file

Hi,

I am currently trying to obtain the code in order for me to open a csv file. copy the data in it and paste it into a sheet in my current workbook. then also close the csv file i had opened.

The code i have at the moment is as follows:-

ilName = Application.GetOpenFilename(FileFilter:="csv files,*.csv", Title:="Select Settlement File", _
MultiSelect:=False)

Workbooks.Open filName

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

Range("A1").CurrentRegion.Copy
ActiveWindow.Close
Sheets("Settlements").Range("A1").Paste
Range("A31").Select


All seems to work ohk expect i get a pop up when closing the csv file which i do not want and a was wondering is there any other better/quiker ways to do this?

Many thanks for your help

RE: Paste in data from other from csv file

Hi Tom, thanks for your query. You could just import the csv data onto a new worksheet and copy it from there. Here's a tidied up subroutine generated from the macro recording of a CSV import (via From Text on the data tab).

Sub import_CSV()

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\Anthony\Documents\My Dropbox\Application Support\Anthony Desktop\Book1.csv" _
, Destination:=Range("$A$1"))
.Name = "Book1"
.FieldNames = True
.TextFileTabDelimiter = True
.Refresh BackgroundQuery:=False
End With
End Sub


Hope this helps,

Anthony

Tue 31 May 2011: Automatically marked as resolved.

 

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:

Create calculated fields that work out your age

You can uset eh year function to work out the year from NOW function and then subtract it with your date of birth type field

Age=Year(Now( ))-Year([DoB])

View all Access hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.09 secs.