Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Start Excel in Text Import Wizard for data import | Setting up and Configuration of Excel | |||
Import external data, data includes Excel function | Excel Worksheet Functions | |||
Import Data / Edit Data / Export Data in Excel | Excel Programming | |||
PLEASE!! How can I un-grey the 'Data-Import External Data-Import Data' option ??? | Excel Programming | |||
how to Import data in excel office2003 with data more 65000 rows | Excel Worksheet Functions |