I have an IE / Excel worksheet process that I am trying to automate via VBA.

Manually, the process looks like this:

 1) Run a web report to get results list.
 2) Since there is no way to convert to MS-Excel,
    with the webpage in focus press CTRL-A, CTRL-C.

 3) Select cell A1 in a blank worksheet.

 4) Paste the contents of the Office clipboard into the Excel sheet.

 5) Manually clean up the report since it does not come through properly formatted.

What I would like to do is some or all of the following:

a) Via message, tell the user to select his web site.
b) With the web page in focus, press CTRL-A (select all) and CTRL-C to copy the contents.
c) With cell A1 selected in the blank work sheet, press CTRL-V (paste)
d) Spend the next day or two cleaning up and formatting the report.

Does anyone know how to use EXCEL VBA to initiate a Office Clipboard copy in an external document (this case, a web page); then paste the data into the blank spread sheet without losing formatting?
Any help would be appreciated.:D




--- Tom

__________________
--- Tom

What it seems to me is that you should be using word to do what you are asking. In word you will be able to open the html page as text, I presume the page is a static page? or if the page is an asp page with dynamic data the format of the html would still be constant, you would then be able to identify the TAGs in the html that identify the table start and end, then through a process of trial an error strip out all the unwanted tags and unwated text leaving only the standard report content. I know this works because I have done it myself with great results.

Give it a go.

This will not work if the web pages are inconsistant.

cheers

Matt

Thanks for your response!

The main reason why my customer asked for this is that the web application does not present the info in a fashion that he can use it directly AND the web developer "locked down" everything (can NOT right-click to see source code, bring up menus, etc.)

I was going to just copy the source code and parse out the stuff he wanted, but I was greated by a nasty little pop-up saying that I couldn't do it.

Today, I was able to CTRL-A (select All) and CTRL-C (copy) to get the report to the Office clipboard. Then I manually pasted it to an Excel spreadsheet. I ran my VBA routine to do the following: removed all the "shapes" that the web page put in; searched to find out what row my data started on; deleted all rows not associated with my data; deleted some columns that were not needed; converted some "text numbers" to real numbers.

The part I would like to automate is:

 - Switch to the IE window
 - Automatical copy to Office clipboard
 - Switch back to Excel
 - Paste from the Office clipboard to the Excel sheet.

Any thoughts?




--- Tom

hi..

I am facing the same problem.. i am not able to copy from web page either... could u please tell me how to do it?

Thanks