Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old April 19th, 2005, 10:56 PM
Authorized User
Points: 306, Level: 6
Points: 306, Level: 6 Points: 306, Level: 6 Points: 306, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: , , .
Posts: 54
Thanks: 2
Thanked 0 Times in 0 Posts
Default Excel VBA and Office Clipboard

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
Reply With Quote
  #2 (permalink)  
Old April 20th, 2005, 12:20 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Bournemouth, Dorset, United Kingdom.
Posts: 180
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #3 (permalink)  
Old April 20th, 2005, 11:08 PM
Authorized User
Points: 306, Level: 6
Points: 306, Level: 6 Points: 306, Level: 6 Points: 306, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: , , .
Posts: 54
Thanks: 2
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old April 4th, 2006, 12:41 PM
Registered User
 
Join Date: Apr 2006
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA vs Visual Studio for Office lovNexcel Beginning VB 6 1 November 19th, 2007 12:32 PM
Clipboard to excel sheet thorugh VB Kaustav Pro VB 6 0 March 20th, 2006 06:54 AM
uploading ms excel without office on server vauneen Classic ASP Components 0 April 13th, 2005 10:16 AM
-2147024770 VBA Office 2003 josemariagomes Access VBA 8 March 29th, 2005 08:47 AM
Excel but no Office walgie VB How-To 6 June 9th, 2003 10:56 AM



All times are GMT -4. The time now is 09:15 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.