Link to home
Start Free TrialLog in
Avatar of Robert Hester
Robert Hester

asked on

How to automate a procedure after a copy command so paste is appended in Excel?

I have a situation where some one has to copy about 3000 partial web pages one by one and append them in Excel starting off from a blank worksheet. My database system will after know how to recognize the begining and end of each copy. This will not be an issue.
The problem is that after a copy, the person will need to shfit to the Excel window, position the pointer on the first empty cell  and then paste. Then, move back to the web page and move on to following page to copy from and then repeat the procedure over and over again. This procedure, due to the volume, may cause humam errors such as duplicating or skippimg.
What I would like is a way that when copy is done, that the procedure of appending on the boittom of the worksheet would be done automatically. until a specific command closes down this code.
I would appreciate receiving a full working sample. Feel free to give names to the Excel objects (filename, etc). Consider that just text (no images) will be copied and will always be with a fixed number of columns, but with variable number of rows.

Thanks in advance,
Robert
SOLUTION
Avatar of kgerb
kgerb
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I forgot to mention.  If you assign this macro to a shortcut key your worker could just copy the stuff from the web page, switch to Excel, and then hit the short cut key.  The stuff will be automatically be pasted to the correct location.  Just a thought.

Kyle
Avatar of Robert Hester
Robert Hester

ASKER

Getting close...

I´d prefer he didn´t have to make his way to the Excel window.
If he could copy, then use ashortcut key from there that would take the procedure foward without moving him away from the web page, that would be the ideal solution. All he would nead to worry about would be to move to the next page. If he goes and comes back, chances are that he might lose track where he stands.....
hmmm....I'm not sure how this will work.  I'm not a programming expert but I think this may be a sticking point.  Then again, I get in over my head quickly as soon as I leave the Excel VBA realm.  Maybe someone else can chime in who knows more about controlling other programs with VB.  I'm concerned that there will be no easy way to keep Excels shortcuts active after Excel loses focus.  I don't know though, maybe someone else will have a more definitive answer.

Kyle
Here's something which is very close to what you want.  It's nearly as simple to use and doesn't involve programming outside of Excel VBA.  Instructions are:
1. Press the start button ... this makes it "live"
2. ALT+TAB to Internet Explorer (or whatever)
3. Select and CTRL+C to copy what you want
4. ALT+TAB to Excel and watch it paste in the data
5. Repeat from step 2. as often as you like
6. Press the stop button when you're finished.

Let me know if this does the trick. Book5.xlsm
jan24,

Your solution is almost close to the best one could imagine and I´ll be extremely happy to reward you if you could perhaps just do one more thing.

Sometimes one wants to simply look and check at the last page copied on the spreadsheet. Barely for checking purposes. But then another paste is done. Time is then lost to erase it.

Could you please creat a third button called SPECIAL PASTE that could be used if your frst solution is not triggered. In other words, We´d have 2 alternative solutions: one as it is now, and the other would require the user to press this new button everytime he'd want to paste after the last empty cell in column A.

Thanks a lot
kgerb and jan24,

I´m sorry, but what I just asked for is basically the solution provided by kgerb.
So, to be fair with him, I will grant him also with a small share....

But I would appreciate having this solution added to jan24s one...as a third button which is installed together wiyth the other two.

Thank you,
Robert
Sure - no problem.  I'm on a train right now but I'll crack on with it this evening.
Roberth509,
Please, by all means, give it to jan24.  His solution was much superior to mine.  He deserves the points and the grade.  I was just happy to see someone who has a clue help you.  I'm just a hack engineer who knows a little VBA.  The solution is his.

Kyle
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I am very grateful...great guys...
You're welcome:)
Ditto!