Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Connection of Excel 07 pivot table to Access Query makes DB read o | Excel Discussion (Misc queries) | |||
Open new instance of Excel with each Excel file opened | Setting up and Configuration of Excel | |||
Excel opens as read only from IE | Excel Discussion (Misc queries) | |||
Excel file opened as read-only, if saved by another user | Excel Discussion (Misc queries) | |||
Set up Excel to start new instance for each new file opened. | Excel Discussion (Misc queries) |