Posts by Kyle123

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

saying?

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

Try this:

Function GetTableSportingLife(url As String) As Variant    
    Dim htm As HTMLDocument, table As Object
    Dim data() As String, x As Long, y As Long
    Set htm = New HTMLDocument
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", url, False
        .send
        htm.body.innerhtml = .responsetext
    End With
    With htm
        Set table = .getelementsbytagname("table")(1)
        ReDim data(1 To table.Rows.Length + 6, 1 To 10)
        For x = 0 To table.Rows.Length - 1
            For y = 0 To table.Rows(x).Cells.Length - 1
                data(x + 7, y + 1) = table.Rows(x).Cells(y).innertext
            Next y
        Next x
        
        
        data(1, 1) = .getElementsByClassName("header-nav")(0).NextSibling.innertext
        data(2, 1) = .getElementsByClassName("content-header")(0).Children(0).innertext
        Set table = .getElementsByClassName("list")(0)
        For x = 1 To table.Children.Length
            data(x + 2, 1) = table.Children(x - 1).innertext
        Next x
        GetTableSportingLife = data
         
    End With
End Function

You'll need to add a reference to Microsoft HTML Object Library, I can't get it to late bind properly

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

no, not really Again, the data isn't in a table, so you need to be specific. I need a list of everything you want from that page, so far I've got:
Race Meeting (location) - 14.10 Pontefract
Race Going - Good to Soft

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

I'll answer here since MrE seems to be on deaths door.

The data you are after is not in a table, so specifically which headings are you wanting to return?

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

Can you put a link on this to your thread in MrExcel please?

Re: Getting Web Data in its proper format

near enough

Re: Getting Web Data in its proper format

snb, that would have been my suggestion, but the op wanted one cell per line - which is what you get when you copy and paste from a browser

Re: Macros code protection

I can't believe that you're actually just copy and pasting your responses into each forum without acknowledging any of the people that have made suggestions/referencing the advice you've been given

Re: Foolproof Protection of VBA codes

Any chance you could share how you did it Roy? I'm still intrigued

Re: Getting Web Data in its proper format

Norie means that rather than using Internet Explorer, you can use the XMLHTTP object to query the page directly.

Maybe something like this:

Sub GetData()
Dim g
With CreateObject("MSXML2.XMLHTTP")
    .Open "GET", "http://www.sec.gov/Archives/edgar/data/1368111/000095012306009002/0000950123-06-009002.txt", False
    .send
    g = Split(Replace(.responsetext, vbTab, Space(8)), vbLf)
End With




On Error Resume Next
    Sheets(1).Cells(1, 1).Resize(UBound(g), 1).Value = Application.Transpose(g)
End Sub

Re: Help: create an array from a range

Probably something like:

Dim g
g = Cells(1,1).currentRegion.value

Re: Foolproof Protection of VBA codes

I did, but I don't know how it was locked

Re: VBA Excel populate CommandBox with values from SQL

or maybe:

with rs
    .Open "SELECT L.intLaptopID, L.strLaptopName from tblLaptops L, tblUser U, tblUserLaptop LU WHERE L.intLaptopID = LU.intLaptopID AND LU.intUserID = U.intUserID AND U.strUsername = '" & Me.txtReturnName.Value & "' " 
	cbLoanLaptop.Column = .GetRows() 
    .Close 
End With

Re: Foolproof Protection of VBA codes

No, neither could I, I'm not sure how it works

Re: Foolproof Protection of VBA codes

I like the approach Roy has, but ultimately all these methods are moot since it takes seconds to view the code in any of them without needing any code.

Roy's at least makes people scratch their head a bit

Re: Foolproof Protection of VBA codes

Here is the method I'd seen before:
1. Save the Excel workbook (.xls) file into an add-in (File > SaveAs.....)
2. After you have the saved add-in (.xla), close the Excel workbook (.xls)
3. Double click on the add-in to open it
4. Press Alt+F11 to access the add-in's vba project
5. Lock the vba-project with a password
6. Double-click on the "ThisWorkbook" code module
7. Press F4 to open the Properties window
8. Change the "IsAddin" status to FALSE
9. Return to Excel by Pressing Alt+Q (or close the vbe window)
10. Go to Tools > Share Workbook
11. When the dialogue appears, check the box for: (Allow changes by.....)
12. Press OK to close the Dialogue
13. When prompted to save, Press OK
14. Press Ok to accept that "macros cannot be accessed"
15. You should feel giddy at this point because you just realized what you have
been missing right under your nose
16. Verify that the [SHARED] appears in the application title bar
17. Now save the workbook again as an Add-in (File > SaveAs...) overwriting the
previous one
18. Close this Excel workbook without saving the changes (you don't need it)
19. Test out your newly saved add-in (open it, access the vbe, try to expand
the project window, you should get the new message "Project is Unviewable"

Re: Foolproof Protection of VBA codes

yes, me too, it exhibits the same behaviour that I mentioned before - I'll have a look for that method, I can't remember the steps off the top of my head - but it's not the same method so I'm curious.

Re: Foolproof Protection of VBA codes

Me or Roy?

Re: Foolproof Protection of VBA codes

Thanks Roy, what's the logic behind it? I haven't seen that method before.

Re: Foolproof Protection of VBA codes

Ah right, ok, I could still open the project in the same way though

How's it done?