Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 279
Default Copying/cloning an object

I do a lot of scraping information into Excel 2003 from the Internet via
Internet Explorer (IE).

[I find that I can safely open an instance of IE and make 100
interactions. If I set the limit as 200 interactions, IE sometimes fails
and Excel stops until I manually acknowledge the failure.]

I would like to do overlapped IO. Grab the first piece of data from IE,
copy it, send for the second piece of data and find it available when I
come to process it.
However, I find I have a misunderstanding of the Set Statement. It does
not take a copy of an object; it increases the reference count to the
object.
How do I copy an object?

My outline code is

Public ie As SHDocVw.InternetExplorer ' Needs
Tools/References/Microsoft Internet Controls

TxURLretry disambiguator & ActiveCell ' Sets ie
Set ieCopy = ie ' This does not copy ie
TxURLNavigate disambiguator & ActiveCell(2, 1) ' Sets ie
' That call changes ie and ieCopy.

I could possibly open 2 instances of IE, but the code for that is likely
to be significantly more complicated.

I have several requirements, where overlapping is likely to be useful.
One consists of ~1400 interactions, where each ~2 second interaction is
followed by ~4 seconds of processing.
Another consists of ?20K 2 second interactions. Overlapping the IO could
lead to a potential saving of ~6 hours.

I've done a bit of Googling on this.
Wikipedia has "There is no built-in method for deep copies of Objects in
VBA".
A Visual Basic DoCmd.CopyObject Method might be usable to those with the
skill.

I think I may have to adopt a 2 instance method.

I already tried running 2 instances of Excel, each on half the data.
I had to do some post-processing, and was not convinced the effort was
justified.
--
Walter Briscoe
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Copying/cloning an object

Here's how I reuse object refs...

Public oIE As SHDocVw.InternetExplorer

Set oIE = New SHDocVw.InternetExplorer '//create instance
'//use the instance for some processes
Set oIE = Nothing '//destroy the instance

...where each time the object is needed a new instance gets
created/used/destroyed. This usually requires some process to make
repeated calls to the process using the object, and so you may need to
restructure code accordingly.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 279
Default Copying/cloning an object

In message of Sun, 3 Jul 2016 17:54:16 in
microsoft.public.excel.programming, GS writes

Thanks for responding.

Here's how I reuse object refs...

Public oIE As SHDocVw.InternetExplorer

Set oIE = New SHDocVw.InternetExplorer '//create instance
'//use the instance for some processes
Set oIE = Nothing '//destroy the instance

..where each time the object is needed a new instance gets
created/used/destroyed. This usually requires some process to make
repeated calls to the process using the object, and so you may need to
restructure code accordingly.


How do you cause oIE to be filled?

I have
Public ie As SHDocVw.InternetExplorer
I usually instantiate ie by
public sub foo()
If ie Is Nothing Then
Set ie = CreateObject("InternetExplorer.Application.1")
....
I have written some fairly hairy double buffering code.
Static IEn(1) As SHDocVw.InternetExplorer ' Needs
Tools/References/Microsoft Internet Controls

for i = 0 to 1
set ie = nothing
foo ' intantiates ie
set IEn(i) = ie
set ie = nothing
next i

That seems to work (goes significantly faster), but is fairly complex.
--
Walter Briscoe
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Copying/cloning an object

Hi Walter,
The work done by the object instance happens in its own separate
procedure from the procedure using the object. So your instantiation
procedure is a reusable utility that your business logic code uses to
process its data, or retrieve data for the business logic code to
process. IOW, it's a 2-way street in terms of how data flows. So...

Dim oIE As Object
Sub GetDataFormIE(<datacontainer,<other args if any)
Set oIE = CreateObject("InternetExplorer.Application")
'//load datacontainer as required
Set oIE = Nothing
End Sub

Optionally:
Dim vIE
Sub GetDataFormIE(<datacontainer,<other args if any)
Set vIE = CreateObject("InternetExplorer.Application")
'//load datacontainer as required
Set vIE = Empty
End Sub

...where your caller procedure will process the contents of
datacontainer. If there's no data then IE didn't give it up; -so check
how you load datacontainer above...

Sub DoThis()
Dim vData
Call GetDataFormIE(vData)
'//process vData
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I code the cloning of an object in vba? sarndt Excel Programming 1 March 17th 10 08:05 PM
Cloning an object which contains a collection deltaquattro Excel Programming 0 March 17th 10 05:11 PM
cloning sheets maisonvi Excel Discussion (Misc queries) 1 June 28th 08 09:58 AM
file cloning shnim1 Excel Discussion (Misc queries) 1 December 8th 05 04:00 PM
Best method of cloning the contents of a worksheet. Marie1uk Excel Worksheet Functions 2 July 6th 05 05:20 PM


All times are GMT +1. The time now is 07:58 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"