Is there a way to import data from .csv to active excel sheet?

Question

I have a csv file always named the same, called SO2PO.csv. It has data that I import into an excell sheet called PO Data, in a workbook called Open Order. I need to find a way to import all the data from SO2PO.csv to Open Order.xlsm sheet PO Data.

I know it's possible, but how? Can someone point me in the right direction?

Or is there a way to make it so that I can import any .csv file that in placed into a specific folder?

Answer 1

Add this code to create a QueryTable in the PO Data sheet to your data source

Once you have created the QueryTable you can then just right click Refresh the data (or refresh on open)

Sub CSV_Import()
Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("PO Data") 'set to current worksheet name

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
     .Refresh
End With
End Sub

Answer 2

If you're going to use querytables make sure you clean up after, leftover query tables caused me a few headaches in a downstream process.

' get the file to the data sheet
Set ws = ActiveWorkbook.Sheets("Data")
With ws.QueryTables.Add(Connection:="TEXT;" & "mydata.csv", Destination:=ws.Range("A1"))
    .TextFileParseType = xlDelimited
    .TextFileCommaDelimiter = True
    .Refresh
End With

' delete the querytable if there is one
On Error GoTo nothingtodelete
    Sheets("Data").QueryTables(1).SaveData = False
    Sheets("Data").QueryTables.Item(1).Delete 
nothingtodelete: