Posts by Kyle123

Re: VBA Arguments not optional

What is FindWinner?

Re: my huge 100K line file keeps freezing

I suggest that if you have over 100k rows, excel probably isn't the best tool to work with. Do you have access?

Re: Search box in Excel to search key words in sentences in multiple worksheets

I think you should alter the structure of the workbook, it will make things much easier.

Instead of having a different worksheet for each type of question, just have one sheet with an extra field that dictates the question type.

It's then very easy to do what you want, you can use autofilter, or if you still want a separate sheet, you only need to search one sheet rather than multiple.

Re: Moving MS Excel data to a website via a web service

Yeah, is it soap? If so do you have the schema? Is it purely xml based?

Re: Grouping data into defined bins using VBA

maybe:
=INT(A1/10)*10&"-"&(INT((A1+10)/10)*10)

Though depending on your goal, a pivot table also seems like a sensible option

Re: Range.Select results in error if running macro from another workbook

You aren't explicitly setting the sheets to copy from/to. You're using activesheet, what if the wrong sheet is active?

Re: Range.Select results in error if running macro from another workbook

Don't select the range. Selecting ranges is rarely necessary and slows your code down

Re: Moving MS Excel data to a website via a web service

Generally you wouldn't need any add-ons, VBA could automate this, but the complexity of the VBA would depend on the webservice. Without seeing what the webservice expects, it's difficult to speculate though

Re: VBA Code To Grab Multiple HTML Tables From Web Page

I can only assume that you've got something somewhere named cls, a variable declared publicly or a module name or a sheet code name etc. I don't know without seeing your workbook

Re: Use 4th button in msgBox for ignore with vbYesNoCancel

You can't have four buttons on a messagebox, why not just create a userform?

Re: VBA Code To Grab Multiple HTML Tables From Web Page

I can't replicate your error. Does it occur in the attached?

Re: VBA Code To Grab Multiple HTML Tables From Web Page

Try this, it might actually work for today's as well:

Sub GetLinks()
    
    Dim htm As HTMLDocument: Set htm = New HTMLDocument
    Dim cls As Object
    Dim lRow As Long
    With CreateObject("msxml2.xmlhttp")
        .Open "GET", "http://www.sportinglife.com/racing/results/14-10-2012", False
        .send
        htm.body.innerhtml = .ResponseText
    End With
    
    For Each cls In htm.getElementsByClassName("rac-cards click ")
        With Sheets(1).Range("a1")
            Sheets(1).Hyperlinks.Add .Offset(lRow, 0), "http://www.sportinglife.com" & cls.ChildNodes(2).ChildNodes(0).pathname
            .Offset(lRow, 1).Value = Replace(Replace(cls.innerText, vbCrLf, " "), " result", "")
        End With
        lRow = lRow + 1
    Next cls
End Sub

Re: VBA Code To Grab Multiple HTML Tables From Web Page

lol no problem

As far as I can see, my code for getting the hyperlinks works on the historic url you've posted. So I'm not really sure what the question is

Re: VBA Code To Grab Multiple HTML Tables From Web Page

I reckon so, it dumps all the urls in sheets(1) starting at A1

Re: VBA Code To Grab Multiple HTML Tables From Web Page

oops, just noticed a typo:
change:

"http://www.sportinglife.com/" & opt.Value

to:

"http://www.sportinglife.com" & opt.Value

Not sure why it's not working in your workbook, it's odd all the others do, try changing all the Sheet1s to Sheets(1)

Re: VBA Code To Grab Multiple HTML Tables From Web Page

Try this

Re: VBA Code To Grab Multiple HTML Tables From Web Page

Yes. It works for me, do you have a sheet1?

Re: VBA Code To Grab Multiple HTML Tables From Web Page

I reckon this will be a little better than part1 and part2

Sub GetLinks()
Dim htm As Object: Set htm = CreateObject("htmlfile")
Dim optGrp As Object
Dim opt As Object
Dim lRow As Long
    With CreateObject("msxml2.xmlhttp")
        .Open "GET", "http://www.sportinglife.com/racing/racecards", False
        .send
        htm.body.innerhtml = .responsetext
    End With
    
    With htm.getElementById("select-racecard")
        For Each optGrp In .Children
            For Each opt In optGrp.Children
                With Sheet1.Range("a1")
                    Sheet1.Hyperlinks.Add .Offset(lRow, 0), "http://www.sportinglife.com/" & opt.Value
                    .Offset(lRow, 1).Value = opt.innerText
                End With
                lRow = lRow + 1
            Next opt
        Next optGrp
    End With
End Sub

Re: VBA Code To Grab Multiple HTML Tables From Web Page

Try changing

Set table = .getelementsbytagname("table")(0)

to:

Set table = .getElementById("racecard")

and remove all the #s from your urls

Re: VBA Code To Grab Multiple HTML Tables From Web Page

Using the url you provided?