Excel ADO connection opens read-only instance of opened Excel
 

ok here is my code from example on microsoft website
( http://support.microsoft.com/default.aspx?scid=kb;EN-
US;Q321686 )

With gxlCN
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & xlPath & ";" & _
"Extended Properties=""Excel 8.0; HDR=Yes;"" "
.Open
End With

PROBLEM SCENARIO:
2 separate Excel applications are open
1 create connection in one Excel instance -
Connection's creates fine, but the second instance of
Excel now has read-only-instance of first excel app. If I
finish running the code and close both Excel apps, Task
Manager shows instance of Excel running in the memory.

I terminate my connection object like this
If gxlCN Is Nothing = False Then
If gxlCN.State = adStateOpen Then gxlCN.Close
Set gxlCN = Nothing
End If

Please help me open Excel ADO connection without this read-
only copy.

Thank you

Serge

Excel ADO connection opens read-only instance of opened Excel
 

Hi Serge,

Reading data from an Excel workbook via ADO should not create a new instance
of Excel. Is there anything else in your code that starts a new Excel
application? Maybe I misunderstood your post....

--
Regards,

Jake Marx
MS MVP - Excel

Serge L wrote:
ok here is my code from example on microsoft website
( http://support.microsoft.com/default.aspx?scid=kb;EN-
US;Q321686 )

With gxlCN
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & xlPath & ";" & _
"Extended Properties=""Excel 8.0; HDR=Yes;"" "
.Open
End With

PROBLEM SCENARIO:
2 separate Excel applications are open
1 create connection in one Excel instance -
Connection's creates fine, but the second instance of
Excel now has read-only-instance of first excel app. If I
finish running the code and close both Excel apps, Task
Manager shows instance of Excel running in the memory.

I terminate my connection object like this
If gxlCN Is Nothing = False Then
If gxlCN.State = adStateOpen Then gxlCN.Close
Set gxlCN = Nothing
End If

Please help me open Excel ADO connection without this read-
only copy.

Thank you

Serge

Excel ADO connection opens read-only instance of opened Excel
 

Jake, if you run this function in Excel while another Excel is open it will
create Read-Only instance. As you can see there is nothing here that starts
a new instance of Excel. I am using MDAC 2.8

Private Function Create_CN() As Boolean
Dim xlPath As String
On Error GoTo Err_Handler
Create_CN = False

'-- open XL connection
Set gxlCN = New ADODB.Connection
xlPath = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

'Stop
With gxlCN
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.ConnectionString = "Data Source=" & xlPath & ";" & _
"Extended Properties=Excel 8.0;"
.CursorLocation = adUseClient
.Open
End With

Create_CN = True
Exit Function

Err_Handler:
Create_CN = False
MsgBox Err.Description, vbCritical, "Error while creating connection..."
'Call Close_CN
End Function

"Jake Marx" wrote in message
...
Hi Serge,

Reading data from an Excel workbook via ADO should not create a new
instance
of Excel. Is there anything else in your code that starts a new Excel
application? Maybe I misunderstood your post....

--
Regards,

Jake Marx
MS MVP - Excel

Serge L wrote:
ok here is my code from example on microsoft website
( http://support.microsoft.com/default.aspx?scid=kb;EN-
US;Q321686 )

With gxlCN
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & xlPath & ";" & _
"Extended Properties=""Excel 8.0; HDR=Yes;"" "
.Open
End With

PROBLEM SCENARIO:
2 separate Excel applications are open
1 create connection in one Excel instance -
Connection's creates fine, but the second instance of
Excel now has read-only-instance of first excel app. If I
finish running the code and close both Excel apps, Task
Manager shows instance of Excel running in the memory.

I terminate my connection object like this
If gxlCN Is Nothing = False Then
If gxlCN.State = adStateOpen Then gxlCN.Close
Set gxlCN = Nothing
End If

Please help me open Excel ADO connection without this read-
only copy.

Thank you

Serge

Excel ADO connection opens read-only instance of opened Excel
 

Hi Serge,

Jake, if you run this function in Excel while another Excel is open
it will create Read-Only instance. As you can see there is nothing
here that starts a new instance of Excel. I am using MDAC 2.8

I guess I'm not following what the problem is. If you open a workbook in
one instance of Excel, then open the same workbook in another instance, the
second instance will get a read-only copy of it. That is, unless you are
using a shared workbook.

Is your problem that you want to be able to update the workbook with ADO and
you can't? If so, that's because you already have it open for writing, so
you can't make changes via ADO (or a second instance).

What are you trying to do? Maybe there's a better way....

--
Regards,

Jake Marx
MS MVP - Excel


Serge wrote:
Jake, if you run this function in Excel while another Excel is open
it will create Read-Only instance. As you can see there is nothing
here that starts a new instance of Excel. I am using MDAC 2.8

Private Function Create_CN() As Boolean
Dim xlPath As String
On Error GoTo Err_Handler
Create_CN = False

'-- open XL connection
Set gxlCN = New ADODB.Connection
xlPath = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

'Stop
With gxlCN
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.ConnectionString = "Data Source=" & xlPath & ";" & _
"Extended Properties=Excel 8.0;"
.CursorLocation = adUseClient
.Open
End With

Create_CN = True
Exit Function

Err_Handler:
Create_CN = False
MsgBox Err.Description, vbCritical, "Error while creating
connection..." 'Call Close_CN
End Function

"Jake Marx" wrote in message
...
Hi Serge,

Reading data from an Excel workbook via ADO should not create a new
instance of Excel. Is there anything else in your code that starts
a new Excel application? Maybe I misunderstood your post....

--
Regards,

Jake Marx
MS MVP - Excel

Serge L wrote:
ok here is my code from example on microsoft website
( http://support.microsoft.com/default.aspx?scid=kb;EN-
US;Q321686 )

With gxlCN
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & xlPath & ";" & _
"Extended Properties=""Excel 8.0; HDR=Yes;"" "
.Open
End With

PROBLEM SCENARIO:
2 separate Excel applications are open
1 create connection in one Excel instance -
Connection's creates fine, but the second instance of
Excel now has read-only-instance of first excel app. If I
finish running the code and close both Excel apps, Task
Manager shows instance of Excel running in the memory.

I terminate my connection object like this
If gxlCN Is Nothing = False Then
If gxlCN.State = adStateOpen Then gxlCN.Close
Set gxlCN = Nothing
End If

Please help me open Excel ADO connection without this read-
only copy.

Thank you

Serge

Excel ADO connection opens read-only instance of opened Excel
 

Ok, I'm sorry for now being clear. I'll try again, thanks for your patience
:)

If you open any blank workbook in one Excel and then open workbook that
contains the ADO connection code in the second instance of Excel and run
that code
what happens in my case is the first instance (the one with blank workbook)
of Excel now contains 2 workbooks! One is blank (from the start) + a
Read-only copy of the workbook that contained the code (i.e. second
Excel).That is not supposed to happen.

How's that?

Thanks

Serge


"Jake Marx" wrote in message
...
Hi Serge,

Jake, if you run this function in Excel while another Excel is open
it will create Read-Only instance. As you can see there is nothing
here that starts a new instance of Excel. I am using MDAC 2.8

I guess I'm not following what the problem is. If you open a workbook in
one instance of Excel, then open the same workbook in another instance,
the
second instance will get a read-only copy of it. That is, unless you are
using a shared workbook.

Is your problem that you want to be able to update the workbook with ADO
and
you can't? If so, that's because you already have it open for writing, so
you can't make changes via ADO (or a second instance).

What are you trying to do? Maybe there's a better way....

--
Regards,

Jake Marx
MS MVP - Excel


Serge wrote:
Jake, if you run this function in Excel while another Excel is open
it will create Read-Only instance. As you can see there is nothing
here that starts a new instance of Excel. I am using MDAC 2.8

Private Function Create_CN() As Boolean
Dim xlPath As String
On Error GoTo Err_Handler
Create_CN = False

'-- open XL connection
Set gxlCN = New ADODB.Connection
xlPath = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

'Stop
With gxlCN
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.ConnectionString = "Data Source=" & xlPath & ";" & _
"Extended Properties=Excel 8.0;"
.CursorLocation = adUseClient
.Open
End With

Create_CN = True
Exit Function

Err_Handler:
Create_CN = False
MsgBox Err.Description, vbCritical, "Error while creating
connection..." 'Call Close_CN
End Function

"Jake Marx" wrote in message
...
Hi Serge,

Reading data from an Excel workbook via ADO should not create a new
instance of Excel. Is there anything else in your code that starts
a new Excel application? Maybe I misunderstood your post....

--
Regards,

Jake Marx
MS MVP - Excel

Serge L wrote:
ok here is my code from example on microsoft website
( http://support.microsoft.com/default.aspx?scid=kb;EN-
US;Q321686 )

With gxlCN
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & xlPath & ";" & _
"Extended Properties=""Excel 8.0; HDR=Yes;"" "
.Open
End With

PROBLEM SCENARIO:
2 separate Excel applications are open
1 create connection in one Excel instance -
Connection's creates fine, but the second instance of
Excel now has read-only-instance of first excel app. If I
finish running the code and close both Excel apps, Task
Manager shows instance of Excel running in the memory.

I terminate my connection object like this
If gxlCN Is Nothing = False Then
If gxlCN.State = adStateOpen Then gxlCN.Close
Set gxlCN = Nothing
End If

Please help me open Excel ADO connection without this read-
only copy.

Thank you

Serge

Excel ADO connection opens read-only instance of opened Excel
 

Serge,

In the meantime, you should only execute this code on a closed workbook. It
seems to create various problems when run against an open workbook. So
either move your code to another workbook or use a standalone VB executable
to connect to your data source workbook.

--
Regards,

Jake Marx
MS MVP - Excel


Serge wrote:
Ok, I'm sorry for now being clear. I'll try again, thanks for your
patience :)

If you open any blank workbook in one Excel and then open workbook
that contains the ADO connection code in the second instance of Excel
and run that code
what happens in my case is the first instance (the one with blank
workbook) of Excel now contains 2 workbooks! One is blank (from the
start) + a Read-only copy of the workbook that contained the code
(i.e. second Excel).That is not supposed to happen.

How's that?

Thanks

Serge


"Jake Marx" wrote in message
...
Hi Serge,

Jake, if you run this function in Excel while another Excel is open
it will create Read-Only instance. As you can see there is nothing
here that starts a new instance of Excel. I am using MDAC 2.8

I guess I'm not following what the problem is. If you open a
workbook in one instance of Excel, then open the same workbook in
another instance, the second instance will get a read-only copy of
it. That is, unless you are using a shared workbook.

Is your problem that you want to be able to update the workbook with
ADO and you can't? If so, that's because you already have it open
for writing, so you can't make changes via ADO (or a second
instance).

What are you trying to do? Maybe there's a better way....

--
Regards,

Jake Marx
MS MVP - Excel


Serge wrote:
Jake, if you run this function in Excel while another Excel is open
it will create Read-Only instance. As you can see there is nothing
here that starts a new instance of Excel. I am using MDAC 2.8

Private Function Create_CN() As Boolean
Dim xlPath As String
On Error GoTo Err_Handler
Create_CN = False

'-- open XL connection
Set gxlCN = New ADODB.Connection
xlPath = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

'Stop
With gxlCN
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.ConnectionString = "Data Source=" & xlPath & ";" & _
"Extended Properties=Excel 8.0;"
.CursorLocation = adUseClient
.Open
End With

Create_CN = True
Exit Function

Err_Handler:
Create_CN = False
MsgBox Err.Description, vbCritical, "Error while creating
connection..." 'Call Close_CN
End Function

"Jake Marx" wrote in message
...
Hi Serge,

Reading data from an Excel workbook via ADO should not create a new
instance of Excel. Is there anything else in your code that starts
a new Excel application? Maybe I misunderstood your post....

--
Regards,

Jake Marx
MS MVP - Excel

Serge L wrote:
ok here is my code from example on microsoft website
( http://support.microsoft.com/default.aspx?scid=kb;EN-
US;Q321686 )

With gxlCN
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & xlPath & ";" & _
"Extended Properties=""Excel 8.0; HDR=Yes;"" "
.Open
End With

PROBLEM SCENARIO:
2 separate Excel applications are open
1 create connection in one Excel instance -
Connection's creates fine, but the second instance of
Excel now has read-only-instance of first excel app. If I
finish running the code and close both Excel apps, Task
Manager shows instance of Excel running in the memory.

I terminate my connection object like this
If gxlCN Is Nothing = False Then
If gxlCN.State = adStateOpen Then gxlCN.Close
Set gxlCN = Nothing
End If

Please help me open Excel ADO connection without this read-
only copy.

Thank you

Serge

Excel ADO connection opens read-only instance of opened Excel
 

Jake,

That's alright. The worksheet contains DDE links and I save values to SQL
database during the day. It is not a good option for me to make user close
live data workbook to save the values to DB. I will just use named ranges
for my purpose. I wanted to use ADO in Excel just because I could. My ADO
connection object to SQL DB does not create any problems in VBA. For the
future I will keep this problem in mind.

I would still be interested to find out what causes this and if there is a
fix.

Thanks for looking into this.

Serge


"Jake Marx" wrote in message
...
Serge,

In the meantime, you should only execute this code on a closed workbook.
It
seems to create various problems when run against an open workbook. So
either move your code to another workbook or use a standalone VB
executable
to connect to your data source workbook.

--
Regards,

Jake Marx
MS MVP - Excel


Serge wrote:
Ok, I'm sorry for now being clear. I'll try again, thanks for your
patience :)

If you open any blank workbook in one Excel and then open workbook
that contains the ADO connection code in the second instance of Excel
and run that code
what happens in my case is the first instance (the one with blank
workbook) of Excel now contains 2 workbooks! One is blank (from the
start) + a Read-only copy of the workbook that contained the code
(i.e. second Excel).That is not supposed to happen.

How's that?

Thanks

Serge


"Jake Marx" wrote in message
...
Hi Serge,

Jake, if you run this function in Excel while another Excel is open
it will create Read-Only instance. As you can see there is nothing
here that starts a new instance of Excel. I am using MDAC 2.8

I guess I'm not following what the problem is. If you open a
workbook in one instance of Excel, then open the same workbook in
another instance, the second instance will get a read-only copy of
it. That is, unless you are using a shared workbook.

Is your problem that you want to be able to update the workbook with
ADO and you can't? If so, that's because you already have it open
for writing, so you can't make changes via ADO (or a second
instance).

What are you trying to do? Maybe there's a better way....

--
Regards,

Jake Marx
MS MVP - Excel


Serge wrote:
Jake, if you run this function in Excel while another Excel is open
it will create Read-Only instance. As you can see there is nothing
here that starts a new instance of Excel. I am using MDAC 2.8

Private Function Create_CN() As Boolean
Dim xlPath As String
On Error GoTo Err_Handler
Create_CN = False

'-- open XL connection
Set gxlCN = New ADODB.Connection
xlPath = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

'Stop
With gxlCN
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.ConnectionString = "Data Source=" & xlPath & ";" & _
"Extended Properties=Excel 8.0;"
.CursorLocation = adUseClient
.Open
End With

Create_CN = True
Exit Function

Err_Handler:
Create_CN = False
MsgBox Err.Description, vbCritical, "Error while creating
connection..." 'Call Close_CN
End Function

"Jake Marx" wrote in message
...
Hi Serge,

Reading data from an Excel workbook via ADO should not create a new
instance of Excel. Is there anything else in your code that starts
a new Excel application? Maybe I misunderstood your post....

--
Regards,

Jake Marx
MS MVP - Excel

Serge L wrote:
ok here is my code from example on microsoft website
( http://support.microsoft.com/default.aspx?scid=kb;EN-
US;Q321686 )

With gxlCN
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & xlPath & ";" & _
"Extended Properties=""Excel 8.0; HDR=Yes;"" "
.Open
End With

PROBLEM SCENARIO:
2 separate Excel applications are open
1 create connection in one Excel instance -
Connection's creates fine, but the second instance of
Excel now has read-only-instance of first excel app. If I
finish running the code and close both Excel apps, Task
Manager shows instance of Excel running in the memory.

I terminate my connection object like this
If gxlCN Is Nothing = False Then
If gxlCN.State = adStateOpen Then gxlCN.Close
Set gxlCN = Nothing
End If

Please help me open Excel ADO connection without this read-
only copy.

Thank you

Serge