excel file locked for editing after using 'Import Data' script 1

[robertsquestion]

Hi,

I've used the macro-recorder in Excel 2010 to create a script that imports the data from a 'data-workbook' (data.xls) into a 'master-workbook' (master.xlsm). So I just used the standard button 'Import Data' and recorded the VBA from that button.

Now the script itself is working fine, the data gets copied to the masterworkbook. But the problem is that after I've used the script, the data-workbook is 'locked for editing'. I guess that there's still a connection to the data-workbook after I use the script. After I close the master-workbook and re-open, the data-workbook is not locked anymore.

Does anyone know how I have to adjust the VBA so that the data-workbook will not be locked after I've used the sript?

See below for the script that I'm using. In fact I'm importing multiple files, I've simplified the script a bit.

I hope someone can help me out, thanks in advance for your help!

Sub Import()
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:\Data.xls;Mode=Share Deny Write;Extended Properti" _
, _
"es=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLEDB:Engine Type=35;Je" _
, _
"t 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;Jet OLEDB:Support Complex Data=False" _
), Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdTable
.CommandText = Array("Sheet1$")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = "C:\Data.xls"
.ListObject.DisplayName = "Table_Data"
.Refresh BackgroundQuery:=False
End With
Range("Table_Data[[#Headers],[a]]").Select
End Sub

Robert
The Netherlands

 

[combo]

Try to change mode (Mode=Share Deny None).

combo

 

[robertsquestion]

Hi combo,

Thanks for your reply. I've changed:
Mode=Share Deny Write
in
Mode=Share Deny None

But if I run the script now and open the 'data-workbook' after that, I get the message:
"Data.xls cannot be accessed. The file may be corrupted, located on a server that is not responding, or read-only"

Do you have any other suggestions?

Thanks,
Robert