Opening a csv file to avoid corruption WITHOUT creating a query

[nigelandrewfoster]

Hello

A csv file I need to open contains quite a number of foreign characters. By default, opening it in Excel corrupts them.

I have been using this to open it (successfully, without corruption):

Sub Open_CSV_Correctly()

    Dim wb As Workbook
    Dim strFile As String


    strFile = "E:\MyFile.csv"


    Set wb = Workbooks.Add
    
    With wb.Worksheets(1)
        With .QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=.Range("A1"))
             .TextFileParseType = xlDelimited
             .TextFilePlatform = 65001
             .TextFileCommaDelimiter = True
             .Refresh
        End With
    End With
    
End Sub

However, it creates a query link, which I don't actually need.

Please can you tell me how I might achieve the same result, but without creating the link?

Thanks for your time

Nigel

 

[BiocideJ]

If your only issue is the artifact query table and connection, you can simply delete those after the import is completed.

Sub Open_CSV_Correctly()


    Dim wb As Workbook
    Dim strFile As String
    Dim c As WorkbookConnection
    Dim qt As QueryTable




    strFile = "\\freedomh.com\globaltpa-fs\Profile\A&G\jrowe\My Documents\MyFile.csv"




    Set wb = Workbooks.Add
    
    With wb.Worksheets(1)
        With .QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=.Range("A1"))
             .TextFileParseType = xlDelimited
             .TextFilePlatform = 65001
             .TextFileCommaDelimiter = True
             .Refresh
        End With
    End With


    For Each c In wb.Connections
        c.Delete
    Next c
    For Each qt In wb.ActiveSheet.QueryTables
        qt.Delete
    Next qt
    
End Sub

 

[nigelandrewfoster]

Sure, that will do nicely. Am completely new to query tables and manipulating them in VBA.

Very helpful, thank you very much.