Phantomchase
05-16-2008, 07:01 AM
Hi guys
I hope you can help me with a odd problem.
The following code is running smooth in Excel 2003, but in Excel 2007 it keeps hanging when I reach the first Activesheet.paste.
The time for the system to paste the copied data takes me around 5 minutes.
The other odd thing is that when I run it the first time in the morning it runs quickly, but when I run it a second time. The system hangs.
So, looking at the code, is there anything I?m missing here? What can I do to make this more efficient or better?:dunno
I can?t use webquery on the page I?m accessing, so that?s why I keep working with the Copy & Paste method.
I have been scanning the web & forums for clues and answers but with no luck so far:(
I really hope you guys can help me out, I?m getting really frustrated.
Sub update()
Application.ScreenUpdating = False
Sheets("R?data").Visible = True
Set myData = New DataObject
Dim myIE As Object
Set myIE = New InternetExplorer
Dim myURL As String
myURL = "webpage" Can?t post links at the moment.
myIE.navigate myURL
Do While myIE.Busy Or myIE.readyState <> READYSTATE_COMPLETE
DoEvents
Loop
myData.SetText myIE.Document.body.innerHTML
myData.PutInClipboard
myIE.Quit
Windows("test1.xlsm").Activate
Sheets("R?data").Select
Range("A1").Select
ActiveSheet.Paste
Columns("A:X").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Range("A1").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End sub
Thanks in advance.
/Phantomchase
I hope you can help me with a odd problem.
The following code is running smooth in Excel 2003, but in Excel 2007 it keeps hanging when I reach the first Activesheet.paste.
The time for the system to paste the copied data takes me around 5 minutes.
The other odd thing is that when I run it the first time in the morning it runs quickly, but when I run it a second time. The system hangs.
So, looking at the code, is there anything I?m missing here? What can I do to make this more efficient or better?:dunno
I can?t use webquery on the page I?m accessing, so that?s why I keep working with the Copy & Paste method.
I have been scanning the web & forums for clues and answers but with no luck so far:(
I really hope you guys can help me out, I?m getting really frustrated.
Sub update()
Application.ScreenUpdating = False
Sheets("R?data").Visible = True
Set myData = New DataObject
Dim myIE As Object
Set myIE = New InternetExplorer
Dim myURL As String
myURL = "webpage" Can?t post links at the moment.
myIE.navigate myURL
Do While myIE.Busy Or myIE.readyState <> READYSTATE_COMPLETE
DoEvents
Loop
myData.SetText myIE.Document.body.innerHTML
myData.PutInClipboard
myIE.Quit
Windows("test1.xlsm").Activate
Sheets("R?data").Select
Range("A1").Select
ActiveSheet.Paste
Columns("A:X").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Range("A1").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End sub
Thanks in advance.
/Phantomchase