Import Data - from Excel

[makulski]

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

 

[makulski]

Figured it out.

The correct incantation is mode=read

For a list of mode options, see
http://www.tech-archive.net/Archive/Data/microsoft.public.data.oledb/2005-10/msg00003.html

 

[makulski]

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 OLEDBatabase 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 OLEDBon'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

 

[ward376]

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

 

[makulski]

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