GetObject problem

[[email protected]]

Hello:
XP Pro
Office Excel 2003
Excel\Excel....spreadsheets

I wrote this code to update(export) 3 cells data to an external spreadsheet. Both spreadsheets are running. I wrote the code with both spreadsheets on one computer just to make sure I had it working. Which it does(like a charm). But when I try to run this over a network there's where it gets into trouble.

Let me explain my limited understanding of how GetObject works:

GetObject() is a two step process

1. It first looks for a running instance that was identified by the strPath. If it finds that running instance then it sets a reference to it.

2. If the first step fails to see the instance as running then it attempts to retrieve's the file and open it on the computer that made the call.

How I have explained steps 1 and 2 is exactly the behavior that I am witnessing which is certainly not what I'm looking for. Step 1 is failing and step 2 does work. Opening an other instance of an already running spreadsheet is unacceptable. I need it to work in the same manner as it did when both spreadsheets were on one computer.

To correct this let me share with you where my reading has taken me:

1.Possible NetWork issue:
I don't think so....It does retrieve the file that GetObject called. But I do wonder if I should set this up on a mapped network drive rather just thru the network.

2. Register as a running instance on the ROT(Running Object Table)
My understanding is the spreadsheet has to lose focus before it is registered. As far as knowing how to determine whether it has been registered I wouldn't have clue. In my situation this just defines the order in which things get launched in the morning. There are other applications launched so to insure Excel loses focus.

3.Launch Microsoft Excel, giving it focus:
Taken from: http://support.microsoft.com/default...b;en-us;238610
Shell "C:\Program Files\Microsoft Office\Office\Excel.EXE", _
vbMinimizedFocus 'other options for starting with
'focus: vbMaximizedFocus and vbNormalFocus

' Move focus back to this form. (This ensures the Office
' application registers itself in the ROT, allowing
' GetObject to find it.)
Me.SetFocus

4.Using early binding and late binding in Automation
Taken from: http://support.microsoft.com/kb/245115
It doesn't seem to matter whether I use Dim objXLApp As Object Or Dim objXLApp As Excel.Application it doesn't resolve the issues describe above


Sub ExportData()
Dim objXLApp As Object, strPath As String

strPath = "c:\********************.xls"
Set objXLApp = GetObject(strPath)
With objXLApp
.Worksheets("Sheet1").Cells(3, 3).Value = SourceWorkBook.Worksheets("Sheet1").Cells(3, 3).Value
.Worksheets("Sheet1").Cells(4, 2).Value = SourceWorkBook.Worksheets("Sheet1").Cells(4, 2).Value
.Worksheets("Sheet1").Cells(4, 5).Value = SourceWorkBook.Worksheets("Sheet1").Cells(4, 5).Value
End With
Set objXLApp = Nothing
End Sub

Some of this stuff is beyond my level of expertise...But that doesn't matter I still need to get this working.
Thanks for any pointers
Dale

[westconn1]

to attach to an existing instance just use
set objxl = getobject("excel.application")
if you then want to open an existing workbook use
set owb = workbooks.open(directory)

if the workbook is already open then getobject(directory) should attach to the open instance

[[email protected]]

westconn1:
In an attempt to explain better I edited my first post....still looking at the same problem

To respond to you.....As explained above if I put both spreadsheets on one computer the code provided works great.....When I put the spreadsheets on seperate computers there's where it gets in trouble. This needs to work in a network enviroment.

Would you be so kind to read my edited post. I think it explains better
Thanks
Dale

[westconn1]

i have reread the original post, but am not sure that i understand what you want to do

i have tested getobject to attach to a workbook residing on a networked computer, that is open on my computer and it works fine, so i must assume that is not what you are trying to do

Both spreadsheets are running.

on the same computer?

i do not know of any way you can attach to instance of excel that is running on a networked computer, (if that is what you want to do), i don't believe it is possible
wrong!!
having said that, on testing it all works just fine, i can have connected to an instance of the open workbook that is open the computer that contains the workbook, it is not of course the same instance of excel that is running on my computer and i can not view the workbook on my computer, but that will not prevent copying of values from one to the other, so i am not sure what the problem is, check all the permissions and unc path

[[email protected]]

westconn1:
Interesting....That tells me it can be done. That's good news....That also tells me there must be something wrong with my network config. At least that narrows down where I need to focus. I'll post back if and when I find something.

Thanks a ton
Dale

[westconn1]

note the workbook object instance appeared to be readonly, so it might be neccessary to make sure it is always opened in share mode