Import Data - from Excel
 

I've started experimenting with the rather cool technique of using Data
Import to get data from another spreadsheet. This is better than linking to
the other spreadsheet, because the other spreadsheet doesn't need to be open,
and I can easily grab large chunks of data.

Setting up the link for the first time I use the "Data : Get External
Data"menu. This sets up the Connection string, Command type, and Command
text. I found that I can change the Command type from Table to SQL, then I
can actually use SQL commands to filter and choose fields, do calcs, etc. I
can also use code to externalize the spreadsheet to use, destination range,
etc.

This is really useful, except I've run into one problem.
Once I've run the Data Import, the source workbook is now locked. ie. if I
open it, I get a "Locked by another user (me)" message and it won't unlock
until I close the destination workbook.

There is a piece of the connection string that looks like this:
Mode=Share Deny Write;
This might be the culprit, but I can't find a list of other options anywhere
in the help.

Help?
Thanks

Import Data - from Excel
 

Figured it out.

The correct incantation is mode=read

For a list of mode options, see:
http://www.tech-archive.net/Archive/.../msg00003.html

Wrongo - Read access takes more than read
 

Turns out that didn't work after all.
Even though my connection string says "Read", after I do the Data Import,
the workbook is locked by another user (me).

Here is the full connection string in the query definition. I didn't write
all of this gobbledygook, this is the connection string that results from
just pointing at the data source using the "Data : Import External data" menu.

Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data
Source=\\mylan\MyFile.xls;Mode=Read;Extended Properties="Jet OLEDB: Don 't
Copy Locale on Compact=False;HDR=YES;";Jet OLEDB:System database="";Jet
OLEDB:Registry Path="";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking
Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk
Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System
Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale
on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet
OLEDB:SFP=False

Does anybody know how the connection string should/could be altered to not
lock up the source file?
Or is there any way to tell my destination workbook that I'm done using the
source file and let it go?

Very frustrating.
Thanks

"makulski" wrote:

Figured it out.

The correct incantation is mode=read

Wrongo - Read access takes more than read
 

Rather than using Data Import from the External data menu, you could
try using New Database Query from the same menu and choose Excel
Files* as the data source.This method uses ODBC/SQL to extract data.

Is there a reason you need to open the source file while the query is
connected? This can lead to other issues like memory leaks.

Cliff Edwards

And the answer is ... MaintainConnection
 

I seem to have things working now.
The MaintainConnection property of QueryTable lets my people go.

Here is the code that ultimately did the trick:


With Selection.QueryTable
.Connection = MyConnectString1 & Range("Folder") & Range("FileName")
& MyConnectString2
.CommandType = xlCmdSql
.CommandText = Array(SqlStatement)
.MaintainConnection = False
.Refresh BackgroundQuery:=False
End With