Excel VBA - Importing CSV files which include URLs

[CharlesB]

Hi, I hope you can help. I am trying to import into an excel sheet a CSV file, one of the columns in the CSV is a URL, Excel is delimiting on the : of the url in addition to the commas.

Importing the file manually works, but even if I use the screen recorder to record this it does not run correctly when I re run the macro. Similarly I have tried renaming the file as .txt but this doesn't make a difference either.

The code I am using is -

    With ActiveSheet.QueryTables _
        .Add(Connection:="TEXT;" & REDIRECT2, Destination:=ActiveCell)
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 65001
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .refresh BackgroundQuery:=False
    End With

(where REDIRECT2 is a DIM string containing the file name).

The URL is in the second column, and I am using Excel 2010.

Thanks for reading this, hope you can help