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