Pasting clipboard to Excel sheet

[Ray S.]

I have to copy and paste text data from a web-based report into an Excel
spreadsheet. I then clean up the spreadsheet data by several macros and
transfer it to MS Access for further processing there. What I'd like to do is
automate and simiplify the process as much as possible. I can't avoid
selecting the web report data and copying it into the clipboard with Ctrl-C,
but I'd like to automate the opening of Excel and pasting the information
from the clipboard...and even the saving of the Excel file to a predetermined
folder location. Does anyone have any ideas on this? Can I do it from an
Access module, or does it have to be done from Excel? I'm more familiar with
Access than I am with Excel.

 

[Knut]

Check out excel help for "get external data" or "web-query" or sth like
that, I dont have excel at hand right now so I dont remember the exac
terms...

 

[Ray S.]

That's a real good thought, but I cannot use the get external data query. I
first have to sign into the web site and there is an active script that runs
after I log in. I can only select an item from a drop down box then click a
button. A page pops up with the data without a page url. No matter what
options I choose in the get external data query, I get nothing. I can select
the data and use Ctrl-C to copy it to the clipboard. I then wanted some code
that will dump the clipboard contents to a spreadsheet.

 

[Tom Ogilvy]

Range("A1").Select
ActiveSheet.Paste

 

[Ray S.]

Great. That works fine. Now, how do I save the active sheet to a specified
location and filename?

 

[Tom Ogilvy]

ActiveWorkbook.SaveAs "C:\MyFolder\MyFiles.xls"

if you just want a copy of the sheet as a separate workbook

Activesheet.copy
ActiveWorkbook.SaveAs "C:\MyFolder\MyFiles.xls"

 

[Ray S.]

Thanks, is there a method to clear the clipboard? I'm having the user click
on an MS Access form hyperlink to the empty excel workbook that has the code
in it. I need to make sure the clipboard is clear of any data before the
workbook is opened, otherwise it will be filled with uninteded data. If the
clipboard is empty and the user clicks on the hyperlink, a message tells him
he has not copied anything to the clipboard and exits back to the form. If he
has copied the text into the clipboard, then the text is pasted into the
excel workbook into sheet1 and the excel file is saved to a specified
location and filename. I want to again make sure that the clipboard is now
emptied of it's contents. If it is not, then another click on the hyperlink
gets messy. Any idea on a method to clear the clipboard?

 

[Tom Ogilvy]

in excel

application.cutcopymode = false

will clear the clipboard of a range that has been copied.

I don't know if that fits in with what you are doing.