.Paste not working everytime

[JCVBA]

I have a macro that is going to intranet site through internet
explorer. It is to pull up a page, copy all the whole page, and paste
the contents into an excel file. I have everything working up to the
point of pasting the data. My first problem is the ActiveSheet.paste
works for maybe 10 rounds, but then it errors out with run time error
'1004' Paste Method of worksheet class failed. So, i changed to code to
use pastespecial, but i then get run time error '1004' PasteSpecial
method of range class failed, which doesn't run at all. My second
problem is that when the code decides to run without any errors, what
is getting copied is not what is getting pasted. Its actually pasting
one screen behind.

CODE
Sub FIT_File_Update_Wing_Info()

Dim IeApp As InternetExplorer
Dim sUrl As String
Dim fso As New FileSystemObject
Dim fls As Files
Dim unitno As String
Dim fname As String

'Get the Fitness Work Folder contents
Set fls = fso.GetFolder("C:\Folder").Files

'Get Internet Explorer Object to open window
Set IeApp = New InternetExplorer

'Loop through the filenames in thefolder
'and get data from Portal
For Each f In fls
Debug.Print f.Name

numint = Mid(f.Name, 7, 3)
fname = f.Name
sUrl = "http://yahoo.com" 'can't put real url


Debug.Print "UnitNo: " & numint

Debug.Print "Fname: " & fname



'Display the Internet Explorer window
IeApp.Visible = True

'Open up report
IeApp.Navigate sUrl
Do
Loop Until IeApp.ReadyState = READYSTATE_COMPLETE

'Needed pause before copying data
Application.Wait (Now + TimeValue("0:00:10"))

'Select and copy report
SendKeys "%ea"
SendKeys "%ec"

'Debug.Print "Open Fname: " & fname & vbCrLf & vbCrLf

'Open the xls file and paste the portal data
Workbooks.Open Filename:="C:\Folder\" & fname
Windows(fname).Activate
Cells.Clear
Cells.Select

Range("A1").Select

'The code lines around the paste statement, supress a message box
that
'kept coming up that i needed to default to OK each time in order
for the
'paste action to go through without user interface

Application.DisplayAlerts = True
ActiveSheet.Paste '********keeps erroring
out here****************
Application.DisplayAlerts = False

'Clear Clipboard
Application.CutCopyMode = False

'Close & save the xls file
ActiveWindow.Close (True)

'Empty Variables
numint = ""
fname = ""


Next

IeApp.Quit

'Clear Objects
Set fso = Nothing
Set fls = Nothing
Set IeApp = Nothing


End Sub

 

[Guest]

Just a thought, but knowing that you're using the clipboard for the copy
makes me wonder if the problem is in that area. I have experienced heavy
memory fragmentation when copying via the clipboard and I wrote up my
experience here
(http://krgreenlee.blogspot.com/2006/01/software-workbook-to-workbook-copy.html) with my solution to the problem.

Take a look at Task Manager to see if you're experiencing an unusual number
of page faults. And if you are, maybe you can use my sample solution as a
starting point for removing the clipboard from your copy process.

Good luck,

Kim Greenlee

 

[JCVBA]

Thanks for the advice. I tried something else and it worked. You know
how you have to add a pause before copying from the internet page, well
I found out that you have to add a pause after as well. Its like its
simulating downloading the info that you copied. Once I added another
line of waiting, it was copying and pasting the internet data in the
files it was supposed to.

'Display the Internet Explorer window
IeApp.Visible = True

'Open up the wing report
IeApp.Navigate sUrl
Do
Loop Until IeApp.ReadyState = READYSTATE_COMPLETE

'Needed pause before copying data
Application.Wait (Now + TimeValue("0:00:05"))

'Select and copy report
SendKeys "%ea"
SendKeys "%ec"

'Needed pause before pasting data
Application.Wait (Now + TimeValue("0:00:05"))