VBA Insert .csv not working

Question

I am trying to import the .csv from this URL:

http://www.redfin.com/stingray/do/gis-search?market=socal&region_id=11203&region_type=6&excl_ss=true&hoa=&max_listing_approx_size=&max_num_beds=&max_parcel_size=&max_price=300000&max_year_built=&min_listing_approx_size=&min_parcel_size=&min_price=50000&min_year_built=&num_baths=1.0&num_beds=1&open_house=&pkg=-&rd=&sf=1%2C2%2C3&sold_within_days=&status=1&time_on_market_range=30-&uipt=2&v=8&num_homes=500&sp=t&al=3&render=csv

The below code works when I use a different .csv URL, like this one: http://ichart.finance.yahoo.com/table.csv?s=AAPL&d=2&e=13&f=2013&g=d&a=8&b=7&c=1984&ignore=.csv

But the code does not work with the longer URL. Is the URL too long? If so, is there a workaround?

Here is my current code:

Sub importCSV()
    Dim URL As String
    URL = "TEXT;http://www.redfin.com/stingray/do/gis-search?market=socal&region_id=11203&region_type=6&excl_ss=true&hoa=&max_listing_approx_size=&max_num_beds=&max_parcel_size=&max_price=300000&max_year_built=&min_listing_approx_size=&min_parcel_size=&min_price=50000&min_year_built=&num_baths=1.0&num_beds=1&open_house=&pkg=-&rd=&sf=1%2C2%2C3&sold_within_days=&status=1&time_on_market_range=30-&uipt=2&v=8&num_homes=500&sp=t&al=3&render=csv"
    Application.DisplayAlerts = False
    With ActiveSheet.QueryTables.Add(Connection:=URL, _
        Destination:=Range("A1"))
        .Name = "test"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Application.DisplayAlerts = True
End Sub

Matt

you can test if the URL is too short by using a URL shortener (goo.gl) try this shortened version if your long url: redfin.com. I've seen Excel freak out about some items that are longer than 255 characters (like a Range string input, for example). I wouldn't think they would limit this since URLs can be far longer than 255 characters — Joseph
– Joseph, Commented Mar 13, 2013 at 19:29

Octopus · Accepted Answer · 2013-03-13 19:26:45Z

3

Try this:

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;[your url goes here]", Destination:=Range("$A$1"))
.Name = "home"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlEntirePage
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
End With

This works well for Excel 2010.

answered Mar 13, 2013 at 19:26

Octopus

1549 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Octopus Over a year ago

I've tried your code and made these changes, using the long URL you've sent. It works fine for me.

user2113095 Over a year ago

This works great! Thank you. Is there a way to comma delimit, text-to-columns? It doesn't break the import into columns.

Our Man in Bananas Over a year ago

you can start the Macro recorder, and record the Text To Columns Excel's Data menu to get the code you need

Octopus Over a year ago

Hi @user2113095! There is a way, yes, and I believe this is matter for another posting of you, what do you think about it? :-) You can also do as Philip says, however you'll lose the opportunity of trying it the hard way and learning a lot.

Collectives™ on Stack Overflow

VBA Insert .csv not working

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related