Paste in data from other from csv file

Resolved · Low Priority · Version 2007

Thu 19 May 2011, 14:48 Delegate Tom said...

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

Tue 24 May 2011, 12:46 Trainer Anthony said...

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

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Access tip:

Splittng a database

Split your database into two (at least).

Keep all of your code, forms, reports, etc. in one 'code' database and all of your tables in another 'data' database which is then linked to the 'code' database.

This makes modifications, updates and back-ups that much easier and allows you to work on a new version of your 'code' database without affecting existing users. It also makes it easier should you wish to convert a single user databases into a multi-user networked version.

View all Access hints and tips