CSV web import giving errors when using .TextFileCommaDelimiter = True

[justgotloud]

Everything was sort of working great when automatically importing a csv file to excel...except that it was putting all data into column A. So I added the line .TextFileCommaDelimiter = True to my vba macro, re-ran and now I get the error 'application -defined or object -defined error' When I click debug, it takes me to the newly added line.

Sub csvimp()

Dim lngConn As Long

url1 = Sheets("urls").Range("B2")

Sheets("csvimp").Cells.ClearContents

  Sheets("csvimp").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;" & url1 _
        , Destination:=Range("A1"))
        .Name = _
        "data"
        .FieldNames = True
        .RowNumbers = False
        .TextFileCommaDelimiter = True
        .FillAdjacentFormulas = False
        .PreserveFormatting = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

With ThisWorkbook
    For lngConn = .Connections.Count To 1 Step -1
        .Connections(lngConn).Delete
    Next lngConn
End With

End Sub

Any ideas what I am doing wrong?

 

[John_w]

Welcome to MrExcel forums.

The code is using a web query (indicated by Connection:="URL;" and the 'Web' properties) and TextFileCommaDelimiter incompatible because it is used for importing text data, whether the data is in a local file or a file accessed via a URL.

Try this macro:

Sub csvimp2()

    Dim url1 As String
   
    url1 = Worksheets("urls").Range("B2")

    With Worksheets("csvimp")
        .Select
        .Cells.ClearContents
        With .QueryTables.Add(Connection:="TEXT;" & url1, Destination:=.Range("A1"))
            .Name = "data"
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileCommaDelimiter = True
            .Refresh BackgroundQuery:=False
        End With
        .QueryTables(1).Delete
    End With
   
End Sub

If that doesn't work, or the data is wrong (it assumes row 1 of the file is column headers and you want to import from row 1) record a macro with you running the Text Import Wizard, specifying the URL as the file name. The generated code can then be edited to read the URL from a specific cell.

 

[justgotloud]

thanks. this line is giving me issues
With .QueryTables.Add(Connection:="TEXT;" & url1, Destination:=.Range("A1"))

 

[John_w]

What is the value of url1?