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

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?

Solution 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

Solution 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:

Solution 3:

it is possible.

Without vba you would use the DATA-Tab and import from text source.

With vba you could open the csv as a new Workbook:

Public Function openSource(fileToOpen As String) As Excel.Workbook
On Error GoTo err_exit

    Dim f As Object
    Dim fs As Object

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set openSource = Nothing

    If fs.fileexists(fileToOpen) Then
        Set f = fs.GetFile(fileToOpen)

        Set openSource = ThisWorkbook.Application.Workbooks.Open( _
            FileName:=f.path, _
            UpdateLinks:=2, _
            ReadOnly:=True, _
            AddToMRu:=False, _
            Notify:=False)

        Windows(openSource.Name).Visible = False
    End If
    Exit Function
err_exit:
    'throwErrMsg "Failed to read File!", "openSource"
    If Not openSource Is Nothing Then
        openSource.Close SaveChanges:=False, RouteWorkbook:=False
    End If
End Function

Dim CSVWorkbook As New Excel.Workbook
Set CSVWorkbook = openSource(c:\SO2PO.csv)

Now you can navigate through this workbook like any other and copy rows, cols or a whole worksheet ;) hope this helps.

Another way would be to use the import from text in vba version, but i don't have an example right away.