Using ADODB to access opened xls file

excel vba adodb
mj82 picture mj82 · Jan 24, 2012 · Viewed 11.5k times · Source

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.
I'm using Excel 2007 and Windows XP, all updated with newest patches.

I'm very often using following code to get data from another workbook: 

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

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.
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: 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. 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):
enter image description here

I started to believe it's a bug that cannot be solved - hope I'm wrong :)

Answer

Bruno Leite picture Bruno Leite · Jan 25, 2012

Your Excel version is 2007 or later?

if is use Microsoft.ACE.OLEDB.12.0 at provider and your problem is solved.

[]´s

Related questions

Accessing SQL Database in Excel-VBA

I am copying an VBA code snippet from MSDN that shows me how to grab results from a SQL query into excel sheet (Excel 2007): Sub GetDataFromADO() 'Declare variables' Set objMyConn = New ADODB.Connection Set objMyCmd = New ADODB.Command Set objMyRecordset = …

Read More
Excel VBA - connect to sql with a trusted connection (no uid/pwd)

Is there a way to have Excel connect to Sql Server using the current user's network windows authentication (trusted connection/integrated security)? I don't want to rely on a sql login or have a uid/password in my code.

Read More
VBA New Database Connection

How to change the code below to prevent what you see in the screenshot. I am running a macro with the following code Dim conn As ADODB.Connection Dim rec1 As ADODB.Recordset Dim thisSql As String Set conn = New …

Read More