Screen Scraped Web page data

[LGMan]

Hi,
I've been sent what looks like a screen scrape of a web page.
some of the numbers are hypertext which try to log me into the web page. I can remove the hyperlink on a cell by cell basis.
However is there a way to remove them at worksheet level.

Also the figures have a trailing space. I've tried Trim but that does not remove the space.

Any suggestions greatly appreciated. Am using Excel 2003

 

[kjv1611]

Well, I think the easiest method would be to use VBA to fix it. Best I can guess anyhow. If you want to go that route, post your question over in forum707.

For the hyperlinks, I'm trying to remember, but I believe there's a hyperlinks group/object of the worksheet object. And for trimming the space out of a hyperlink, you'd basically just need to set the hyperlink equal to the trimmed value of the cell's hyperlink. Then you'd just loop through all the necessary cells to update all of them in one fell swoop.

If you do want to go that route, and create a new thread, be sure to post a link in THIS thread referencing that thread. The easiest way to do this (if you don't already know) is to select the section on the other threads page that says [ignore]thread68-1576111[/ignore] at the top of the page. When you do, it'll look like this:
thread68-1576111. Of course, the difference will be it'll point to whatever thread you reference.

--

"If to err is human, then I must be some kind of human!" -Me

 

[LGMan]

Thanks, however am not keen on the VBA route as I'm just covering someones leave, and that person does not use VBA and would not be able to maintain any code if it were to error.

 

[kjv1611]

I don't believe there's any built-in automated way of cleaning up all the hyperlink values on a page. I'm fairly certain you'll have to do it manually if VBA is not an option.

Of course, you can always do the VBA method (behind a button and/or keyboard shortcut), and then document the manual method as well. That way, if the VBA method ever breaks, the user can just follow the manual documentation until/unless they can fix any errors that popped up in VBA.

And the code for doing such should be simple enough, by the way, that if you used the code, it shouldn't really break - at least not anytime soon. I don't believe the basics changed enough in Excel for VBA for something like this to break when moving to Excel 2007. I have seen some break for Access 2007 of my own, but it was just on one database - none of the others saw any such effect.

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

I can remove the hyperlink on a cell by cell basis.

Turn on your macro recorder and record doing that for ONE hyperlink.

Post back with your recorded code in forum707 for help customizing your recorded code.

Ist either THAT or manual changes. Pick yer poison!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LGMan]

thanks all, have just figured out the trim which will also sort out the hyperlink.
the simple =Trim(c2) doesn't work, so I used =Trim(substitute(c3,char(160),char(32)))
Data can now be used within simple formula