Reproducing HTML Table

[pajo60]

Hi guys. Looking to reproduce a table in Excel, exactly as it is online if at all possible.

An example of the table can be seen here. (The table located below the Form tab - date, race conditions, wgt etc.)

Copy and Paste Special is the closest I can get. But Excel changes most of the 'WGT' column entries to dates. It also underlines all text and changes text colour colour to linky blue (I do need to keep the hover over text but could lose the links). The colour and the underlining aren't difficult to fix admittedly but I also lose the part bolded sections of the 'Race Conditions' and 'Race Outcomes' entries.

edit: I should add that I hope to reproduce each table in as short a time as is possible. Have a lot of these to do.

Any help or advice greatly appreciated. Thanks guys.

[Whizbang]

Use a Web Query.

http://exceluser.com/formulas/webqueries_1.htm

[pajo60]

Thanks for taking a look Whizbang. Really appreciate that. I'll get reading on the web queries.


Is it possible to keep the 'hover over' text from the online source?

Any advice on stopping Excel from changing some of the entries under 'WGT' to dates?

[Whizbang]

In the Web Query dialog box, click "options". Select the option to import Full HTML formatting. Unfortunately this grabs the drab grey background, but I don't see how to get around that. There is also an option for disabling date recognition.

[Edit]

Conditional formatting should take care of the grey.

[pajo60]

Thanks again Whizbang. That's very helpful.

[pajo60]

Actually one more question.

On that table there are two starred out columns at the end that only become visible when signed in to a (paid) account. Is it possible for Excel to grab this information? I do have an account.

[Whizbang]

I am not sure. If the site uses cookies to remember your login (keep you logged in) then I think it would pull that data down on a refresh. Not sure, though. I know web queries can be used to pull data from a site that requires a login, so there must be a way. I am not an expert at Web Queries though.

Try opening the website in the browser, log in, then refresh the table and see if anything happens. Otherwise more research will need to be done to figure this out. Maybe post a second question specifically about Web Queries that require log in.

[pajo60]

Tried that Whizbang but no luck unfortunately. I'll have a look see if anything like it has been answered/solved before.

Thanks.

[newdoverman]

This was done with a web query. When the site is accessed there is a button for OPTIONS on the right side where you can choose what options you want after selecting the tables for import. This was done choosing FULL HTML Formatting and Disable Date Recognition.

[pajo60]

Thanks newdoverman. I had got that far (thanks to Whizbang). Just need to try and grab the data that's hidden behind log in now.

[newdoverman]

You don't need to use the HTML to import and keep the data from changing to a date just choose the option regarding the dates at the bottom of the options dialogue.

I don't see any way around the login unless you have the option to keep logged in. Then you should be able to access the tables normally.

I have done some reading about auto login scripts but it seems that they are not reliable.

Good Luck.

[pajo60]

Just a quick (beginners!) question on using a Web Query.

What I want to do is just grab the information once. I will then create additional rows and columns of my own information. I might even modify some of the grabbed information.
I don't want a refresh to mess up whatever personal changes I have made.

So does excel ever refresh the query (such as every time I open the file)? Or does it grab the information just the once and 'lock' it in?

[Whizbang]

There is an option in the connection settings to refresh on open (or not). Also, you can just delete the connection and it becomes static.

[pajo60]

Perfect.

Cheers Whizbang.

[pajo60]

One other small thing.

The html is great as I get the 'hover over' information. But the links are a bit of a pain in the ***, as whenever I click the cell it opens the link in the browser. I will never need this facility (but want to keep the 'hover text').

Anyway I can stop it opening external links?

[newdoverman]

When you remove the hyperlink, the screen tip (that is what you see with the mouse hover) disappears also. I haven't seen a way around this.

You can copy the screen tip, delete the link and then create a comment for the cell and past the screen tip in the comment. I think that this might be able to be automated with VBA. An expert in VBA would be able to advise you on this.

[pajo60]

Ah I see, I feared that might be the case. Thanks newdoverman.

I probably need to pay and work with a professional to get this working as I'd like I think.

[newdoverman]

You're welcome.

Post a question in the VBA section. There are many people there who are amongst the very best in VBA.