Open CSV File and Copy the Data on Other sheet

[unknownymous]

Hi Guys,

Here's a snap of my codes but it automatically paste the CSV file data on the data sheet. I was wondering if you could help me find the codes for GetOpenFile whereas the path file directory will open. After I paste the address, the macro will open the CSV file and paste everything on the Data Sheet.xlsx then CSV file will close.


Sub Subject()


Dim strFile As String
Dim strCSV As String

strFile = ActiveWorkbook.Path
strCSV = "Subject.csv"
Workbooks.Open strFile & "" & strCSV


Range("A1").CurrentRegion.Copy Destination:=Workbooks("Data Sheet.xlsx").Sheets("Data Sheet").Range("A1")


Workbooks(strCSV).Close


Any help will be much appreciated.

Thank you!

 

[ranman256]

you dont have to copy/paste
record a macro, and use DATA, FROM TEXT, stop recording, then review the macro.
this will load the file directly in 1 step using the command:

With ActiveSheet.QueryTables.Add(Connection:= "TEXT;" & strFile & "" & strCSV & ", Destination:=Range("$A$1"))
....

 

[unknownymous]

I have below codes but the data from the CSV won't copy on my active workbook. Can you help me?

Dim ws As Worksheet, strlFile as String

Set ws = ActiveWorkbook.Sheets("CB Data")


strFile = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Please select text file...")
With ws.QueryTables.Add(Connection:="TEXT;" & strFile, _
Destination:=ws.Range("A1"))
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
End With