Hi Peter,
I take it Application.Run + arg's is not an option
It would be possible to let the other wb do the job, but then it makes more
sense to lump the 2 wb's into one.
I think that might be the best thing. It would simplify a lot of things.
RBS
"Peter T" <peter_t@discussions wrote in message
...
Hi Bart,
As you say probably not worth the trouble, even if it all works in
development every possibility of a reference or compile problem later.
I take it Application.Run + arg's is not an option and get the wb with the
ADO to do all the work.
It also would make it more likely that I will have the worlds
largest commercial .xla :)
Go for it <g
Regards,
Peter T
"RB Smissaert" wrote in message
...
Hi again,
Still interesting, but I don't think now it is worth the
trouble just to avoid the one off setting of the connection.
An option is to avoid the 2 workbooks (2 .xla's) and lump them both
together. The idea of having them separate is that one of them can be
used
on it's own, but in practice that rarely if ever happens. So that
probably
will
make sense. It also would make it more likely that I will have the worlds
largest
commercial .xla :)
RBS
"Peter T" <peter_t@discussions wrote in message
...
Hi Bart,
Yes you're right, it wouldn't compile until the ref has been added -
not
a
good idea!
In theory though might be possible to place in a dedicated non-compiled
module without Option Explicit. Apart from not doing a debug compile
with
this module included also means never running any code in the module
before
saving & distributing. Otherwise is it viable to distribute with the
ref
already added.
Regards,
Peter T
"RB Smissaert" wrote in message
...
Hi Peter,
Very interesting idea, but not sure this bit can work:
proBook1.ADOConn.Open strConn
It definitely doesn't compile as above.
Will experiment some more.
RBS
"Peter T" <peter_t@discussions wrote in message
...
Hi again,
Maybe the ref to the other workbook could be added only when needed
and
deleted immediately after. This would avoid the potential problem of
trying
to close a referenced work while the wb holding the ref is still
open.
Obviously would need a quick check to ensure the other wb is open
before
adding the ref.
Regards,
Peter T
"RB Smissaert" wrote in message
...
Hi Peter,
Interesting idea and I will try that.
In general I have found that setting references to other workbooks
can
cause
a lot of trouble and is best
avoided. It really is not a big issue as the connection will have
to
be
established only once in each workbook.
Still, I will see.
RBS
"Peter T" <peter_t@discussions wrote in message
...
Hi Bart,
How about something like this -
Sub AddRef()
Dim ref As Object
' "Book1.xls" file name, already open
' "proBook1" project name of Book1.xls
On Error Resume Next
Set ref = ThisWorkbook.VBProject.References("proBook1")
If ref Is Nothing Then
ThisWorkbook.VBProject.References.AddFromFile "Book1.xls"
End If
End Sub
Sub test()
If proBook1.ADOConn Is Nothing Then
proBook1.CreateADOConnection
End If
strConn = "DSN=System 6000;" & _
"UID=" & UserName & ";" & _
"PWD=" & Password
proBook1.ADOConn.Open strConn
End Sub
in Book1.xls your example below Sub CreateADOConnection() with
the
public
ADOConn
Regards,
Peter T
"RB Smissaert" wrote in message
...
Is it possible to use an ADO connection that has been
established
in
one
workbook
in another workbook?
Simplified the connection has been set like this:
Public ADOConn As ADODB.Connection
Sub CreateADOConnection()
If ADOConn Is Nothing Then
Set ADOConn = New ADODB.Connection
End If
End Sub
And in another Sub:
strConn = "DSN=System 6000;" & _
"UID=" & Username & ";" & _
"PWD=" & Password
ADOConn.Open strConn
Thanks for any advice.
RBS