Databases
StackOverflow2013
Postsdbo
POUsing ADODB to access opened xls file
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUsing ADODB to access opened xls file
    primarykey
    data
    text
    <p>Although I've been working with VBA for Excel for quite a long time, I've one problem I cannot solve by myself. I've described it below, hope to get some help or advice.<br> I'm using Excel 2007 and Windows XP, all updated with newest patches. </p> <p>I'm very often using following code to get data from another workbook: </p> <pre><code>Set conn = New ADODB.Connection conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=g:\source.xls;Extended Properties=Excel 8.0;" Sql = "SELECT Field1, Field2 FROM [Sheet1$]" Set rst = New ADODB.Recordset rst.Open Sql, conn, adOpenForwardOnly Worksheets("Results").Range("A2").CopyFromRecordset rst rst.Close Set rst = Nothing conn.Close Set conn = Nothing </code></pre> <p>As simply as can be - just connect to file and get some data from it. It's working perfect as long, as the source file that is located on a common network drive (g:\source.xls) is not opened on another computer.<br> When some user on another computer has opened the file and I try to execute the following code, I notice one thing that I'd like to get rid off: <strong>the source Excel file is opened (in a read-only mode) on my computer and it's not closed after the connection to that file has been closed</strong>. What's worse, even if I close this source file manually, it leaves some garbage in my file, like it was never closed: see the picture after couple of code execution (the source files has been closed before):<br> <a href="https://i.stack.imgur.com/8cOPq.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/8cOPq.png" alt="enter image description here"></a></p> <p>I started to believe it's a bug that cannot be solved - hope I'm wrong :)</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USZygD
    1. USmj82
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POUsing ADODB to access opened xls file
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POUsing ADODB to access opened xls file
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POUsing ADODB to access opened xls file
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload