Re: VBA Code To Grab Multiple HTML Tables From Web Page
saying?
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
Display More
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
Display More
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:
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?