Consulting

Results 1 to 5 of 5

Thread: Data Portal Design Requires Advanced Coding

  1. #1
    VBAX Regular sdruley's Avatar
    Joined
    Sep 2011
    Location
    Conover, NC
    Posts
    6
    Location

    Smile Data Portal Design Requires Advanced Coding

    This short snippet of code was designed to create a data portal between two open instances of Excel. The purpose of doing so is two fold:
    1) to transfer the data realtime
    2) to allow for two simultaneous executions of code

    The code below develops two errors when it gets to the COPY FROM....COPY TO code line:

    1) Microsoft is waiting for another application to complete an OLE action
    2) Copy method of class failed

    Any ideas on why I am getting these errors would be appreciated. My main purpose is to be able to make data available, in one session of excel, to another session so that only the newest data is carried over and appended to the database.

    [vba]Sub Two_Instance_Data_Portal()
    Dim xlStuff As Excel.Application
    Dim xlObj As Object
    Dim ows As Worksheet
    Dim dws As Worksheet
    Dim stSourceWkBook As String
    Dim stSourceSheet As String
    Dim stSourceRange As String
    Dim stTO_WkBook As String
    Dim stTO_Sheet As String
    Dim stTO_Range As String[/vba][vba]
    ' Copy From Objects [ows = origination worksheet in instance 1]
    stSourceWkBook = "c:\TTND\IQLinkData30.xlsm"
    stSourceSheet = "LINKDATA"
    stSourceRange = "Chicago"
    Set xlObj = GetObject(stSourceWkBook)
    Set ows = xlObj.Sheets(stSourceSheet)
    ' Copy to Objects [dws = destination worksheet in instance 2]
    stTO_WkBook = "c:\TTND\Picasso31.xlsm" ' cross instance reference must be index [2]
    stTO_Sheet = "NEWMIRROR" ' cross instance reference must be index [8]
    stTO_Range = "Receiver"
    Windows("Picasso31.xlsm").Activate
    Set xlStuff = Excel.Application
    Set dws = xlStuff.Workbooks(2).Sheets(8)
    ' COPY FROM .............................COPY TO.................
    ows.Range(stSourceRange).Copy dws.Range(stTO_Range)
    Set xlStuff = Nothing
    Set xlObj = Nothing
    Set ws = Nothing
    End Sub[/vba]
    Turn data into information
    Turn information into knowledge
    Turn knowledge into direction
    Turn direction into leadership

  2. #2
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,752
    Trying here to reproduce your setup, but need some help:
    You have two instances of Excel.Application;
    1. Where is the code you've shown us located? (Instance|workbook name|module)
    2. What workbooks are open before the code runs and in which instances are they?

    I see you've cross posted to excelforum.com, would you be good enough to include a link here to that cross post and any others (just so that people answering aren't wasting their time duplicating others' work).
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Regular sdruley's Avatar
    Joined
    Sep 2011
    Location
    Conover, NC
    Posts
    6
    Location
    p45cal,

    Indebted to you for your response to this difficult subject matter...

    Workbook A is receiving data though a DDE link from Chicago Merchantile Exchange. It has its own code running to collect this data and resides in Instance 1 of Excel.
    Workbook B (in instance 2) has the above code running (hopefully) to create a real-time image of the database set up in Workbook A. Workbook B is heavily involved in number crunching.
    All workbooks in the app are open at all times.

    Workbook A specifics:

    Instance: 1
    Name: IQLinkData30.xlsm
    Sheet: LINKDATA
    Range: Chicago

    Workbook B Specifics:

    Instance: 2
    Name: Picasso31.xlsm
    Sheet: NEWMIRROR
    Range: Receiver

    Cross Post at Excel Help Forum under user sdruley. Link not available due to limited number of posts.

    The purpose behind having two instances of Excel is to better facilitate the running of two simultaneous programs, each gobbling up resources.

    My system:

    HPE-190t
    Clock 3.33 GHz
    12 Processors
    64 bit
    24 GB mem
    Last edited by sdruley; 09-18-2011 at 05:03 AM.
    Turn data into information
    Turn information into knowledge
    Turn knowledge into direction
    Turn direction into leadership

  4. #4
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,752
    You seem to go round the houses to establish dws as a sheet in the workbook where the code resides. You can use
    Set dws = ThisWorkbook.Sheets(stTO_Sheet)

    I think the copying error might occur because there may be two clipboards, one in each instance of excel, so try changing tha line to:
    dws.Range(stTO_Range).Value = ows.Range(stSourceRange).Value
    which doesn't involve the clipboard.
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    VBAX Regular sdruley's Avatar
    Joined
    Sep 2011
    Location
    Conover, NC
    Posts
    6
    Location

    Smile

    p45cal,

    Your feedback, which solved my long-standing challenge to connect two instances of Excel with a data portal, is simply brilliant. It not only works but it pushes data over the "instance" fence at the speed of light.
    I currently have this same challenge submitted to Microsoft professionals under an incident number and they haven't solved it yet for me. One thing I have learned from these type of miracle solutions is that they always seem to be shorter and more simplified in their structure.
    Thank you, thank you and thank you...

    The new code is as follows:


    [VBA]'Rem Microsoft ActiveX Data Objects 6.0 Library
    'Rem Joint effort between Kirk Beller, Stephen Druley and the experts at VBAExpress
    ' to establish a data portal from one instance of Excel to another
    Sub Two_Instance_Data_Portal()
    Dim xlObj As Object
    Dim ows As Worksheet
    Dim stSourceWkBook As String
    Dim stSourceSheet As String
    Dim stSourceRange As String
    Dim stTO_Range As Range
    ' Copy From Objects [ows = origination worksheet in instance 1]
    stSourceWkBook = "c:\TTND\IQLinkData30.xlsm"
    stSourceSheet = "LINKDATA"
    stSourceRange = "Chicago"
    Set xlObj = GetObject(stSourceWkBook)
    Set ows = xlObj.Sheets(stSourceSheet)
    ' Copy to Objects [instance 2]
    Set stTO_Range = Application.Range("Receiver")
    ' COPY TO ............................. FROM .................
    stTO_Range.Value = ows.Range(stSourceRange).Value
    End Sub[/VBA]
    Turn data into information
    Turn information into knowledge
    Turn knowledge into direction
    Turn direction into leadership

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •