sdruley
09-17-2011, 01:56 PM
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.
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
' 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
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.
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
' 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