copy text from webpage then paste into excel

X82 · 23 Jul 2012 at 22:21

I complicated situation this one, but I will try to explain it best I can.
At work, we needs to access the powerbars for servers to find out ampage. In short, we have to go an an ip like 174.45.71.4 for each powerbar. And we have 1000. We take the amps reading and place it in an excel spreadsheed for that IP and that day. This has to be repeated for the other 999 powerbars 5 days a week. A tiresome task.

Does anyone know a way of taking a certain peice of text from a website, in this case the amps reading and pasting it inside excel? So I can automate this task best I can. At the moment, it's tedious.

Thanks in advance.

mp3duck · 24 Jul 2012 at 08:30

what generates the website? Does it use some sort of data source, or read the data live?
If there is a data source, you should be able to connect to it via VB.

Otherwise, you could try writing some html scraping code. Not done this in Excel VB, but it's easy in C#

edscdk · 24 Jul 2012 at 08:38

not sure how you are doing it currently, if you are manually typing the details ths will be faster.. but it may already be what you are doing..

copy and paste the details into notepad

use copy and repalce to remove elements / words / characters you dont want

to get rid of tabs you need to copy one from the document and paste it into he replace box...

ifthe above helps and you cannot make it any easier you can macro all the copy and replace's

are you sure you cannot turn any logging on in the software?

A.N.Other · 24 Jul 2012 at 09:35

That's the sort of task that can be automated in Python or Perl in about 10 lines of code to output to csv. This would also allow you to simply compare data to see any anomalies, though I suppose you can do that in excel as well.

mp3duck · 24 Jul 2012 at 10:46

A.N.Other said:
That's the sort of task that can be automated in Python or Perl in about 10 lines of code to output to csv. This would also allow you to simply compare data to see any anomalies, though I suppose you can do that in excel as well.

You could always open the CSV in excel?
Like you say, it is a fairly simple scripting tasks!

I've written scripts which scrape whole forums (not this one), and move all it's data into a database

visibleman · 24 Jul 2012 at 12:09

If you've only got access to Excel, then use VB and the XMLHTTP (saves having to use the inet control etc) and just scrape the data into a new sheet.

X82 · 24 Jul 2012 at 17:30

Thanks for the replies.
I will say that when it comes to programming I know nothing.
The site I need to access requires a log in and password, which is all the same throughout the 1000 pages by the way. Don't know if that complicates things.

From a programming newbie perspective, how would I start programming this task in VB? I only have access to firefox (which the powerbars only work in, not IE) and office 2010 at work. I have no ability to install extra 3rd party programmes.

Thanks again.

mp3duck · 25 Jul 2012 at 07:52

What kind of password prompt is it? Is it a popup window, or a form on the page?
I'd look into seeing if you can enable logging of some sort in the 3rd party software.
If you can, and it contains the data you require, you can then easily read this text file, and load into excel.

What vendor is producing your powerbars?