Writing to External Data

[tomlancaster]

Hello, I am having a problem with updating another workbook using a Userform.

I have a Master Workbook which imports data from external sources. Once of these data sources is another Excel Workbook which has a list of Username’s and Times.

When I open the Master Workbook, it imports the Data from the other Workbook OK. However, I cannot open the other Workbook or make amendments to it.

The routine in my form requires the other Workbook to be updated but it is coming up as Read Only. I did modify the “Mode” section to “Share Deny None” but the Workbook cannot be opened at all. It says 'Cannot access “Staff.xls”'

I think it may be to do with the query but I cannot be sure?

Query is:

Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=\\Server\TIMEKEEPER\WORKBOOK\DATA\Staff.xls;Mode=ReadWrite;Extended Properties="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

Can anyone help me? 

[romperstomper]

How did you set up the connection to the other workbook? (Are you using 2003 or 2007?)

[tomlancaster]

How did you set up the connection to the other workbook? (Are you using 2003 or 2007?)

Hi,

I set it up using Data > Import External Data > Import Data

I am using Excel 2003

[romperstomper]

Have you tried using the New Database Query instead of Import Data?

[tomlancaster]

Have you tried using the New Database Query instead of Import Data?

I've tried connecting the the excel workbook as you suggested but it returns the error:

"This data source contains not visible tables"