UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Auto Update Linked Excel Like 2000, Access 2010    
 
   
stevep
post Mar 20 2020, 04:50 PM
Post#1



Posts: 110
Joined: 9-November 14



I am trying to remember the name of this old feature. I do not know the jargon or terms.

If I remember correctly, back in Office 2000 or so, you could automatically update linked Excel files. What I mean is that if you linked to an excel file, and opened it like a table, you could change a cell and the value in the Excel file would be updated.

I think the guy who invented that successfully sued Microsoft so they omitted it from future versions of Office. But I was thinking the original developer might have tried to market his tool as an addin or something. Or someone else might have created an equivalent process.

Is there such a thing?








Go to the top of the page
 
theDBguy
post Mar 20 2020, 05:08 PM
Post#2


UA Moderator
Posts: 77,730
Joined: 19-June 07
From: SunnySandyEggo


Hi. I never heard of an add-in like that but recently saw a post where using IMEX=0 instead of 2 in the linked Excel file makes it somewhat editable. You could give it a try.

PS. I don't remember all the details at the moment, so the above may be slightly incorrect.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
stevep
post Mar 25 2020, 08:28 PM
Post#3



Posts: 110
Joined: 9-November 14



I cannot see where I could edit this setting. The wizard gives me no options and I am unaware of other methods to link Excel files. I tried editting the MSysObjects table but it wouldnt let me.
Go to the top of the page
 
theDBguy
post Mar 26 2020, 07:56 AM
Post#4


UA Moderator
Posts: 77,730
Joined: 19-June 07
From: SunnySandyEggo


Probably not through the wizard but through a relink code.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
JeffK
post Mar 26 2020, 08:16 AM
Post#5


UtterAccess VIP
Posts: 1,597
Joined: 19-December 02
From: Lansing, MI


Found this in my code library. I haven't used it in a while and it is no longer called in prod apps, but I did a quick test and it worked for me. Linked Excel table was editable after running it. I ran it in 2013 in an mdb format file linking to xls (which was not editable upon linking with the wizard). I also have a note that this produces unexpected additional behavior, notably that first row is treated as field names even when HDR is set to no (choosing no in the wizard). If you want to try it out, paste the sub in a standard module and execute it in the immediate window with your table name for strTableName.


Public Sub MakeSpreadsheetEditable(strTableName As String)

Dim db As DAO.Database
Dim t As DAO.TableDef
Dim ts As DAO.TableDefs
Dim strConnect As String

Set db = CurrentDb
Set ts = db.TableDefs
Set t = ts.Item(strTableName)

strConnect = t.Connect
strConnect = Replace(strConnect, "IMEX=2", "IMEX=0", 1, 1, vbTextCompare)
t.Connect = strConnect

t.RefreshLink
ts.Refresh

Set t = Nothing
Set ts = Nothing
Set db = Nothing

End Sub
Go to the top of the page
 
theDBguy
post Mar 26 2020, 10:44 AM
Post#6


UA Moderator
Posts: 77,730
Joined: 19-June 07
From: SunnySandyEggo


QUOTE (JeffK)
Found this in my code library. I haven't used it in a while and it is no longer called in prod apps, but I did a quick test and it worked for me. Linked Excel table was editable after running it. I ran it in 2013 in an mdb format file linking to xls (which was not editable upon linking with the wizard). I also have a note that this produces unexpected additional behavior, notably that first row is treated as field names even when HDR is set to no (choosing no in the wizard). If you want to try it out, paste the sub in a standard module and execute it in the immediate window with your table name for strTableName.


Public Sub MakeSpreadsheetEditable(strTableName As String)

Dim db As DAO.Database
Dim t As DAO.TableDef
Dim ts As DAO.TableDefs
Dim strConnect As String

Set db = CurrentDb
Set ts = db.TableDefs
Set t = ts.Item(strTableName)

strConnect = t.Connect
strConnect = Replace(strConnect, "IMEX=2", "IMEX=0", 1, 1, vbTextCompare)
t.Connect = strConnect

t.RefreshLink
ts.Refresh

Set t = Nothing
Set ts = Nothing
Set db = Nothing

End Sub

Hi Jeff. Thanks for the assist. Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
JeffK
post Mar 26 2020, 12:24 PM
Post#7


UtterAccess VIP
Posts: 1,597
Joined: 19-December 02
From: Lansing, MI


No problem. I vaguely remembered it and found it with a "IMEX" search after your post. Thinking about it more, this is an old attempt at importing Excel data into specific spots in Access tables, thinking we'd let users edit on an Access form first. Turns out it worked out best to keep all the editing in Excel and use external automation of the Excel application to import the results.
Go to the top of the page
 
stevep
post Mar 28 2020, 12:19 PM
Post#8



Posts: 110
Joined: 9-November 14



Works perfectly for my needs. Thanks all.
Go to the top of the page
 
theDBguy
post Mar 28 2020, 12:42 PM
Post#9


UA Moderator
Posts: 77,730
Joined: 19-June 07
From: SunnySandyEggo


Hi. Glad we could assist. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
AlbertKallal
post Mar 29 2020, 11:56 AM
Post#10


UtterAccess VIP
Posts: 2,982
Joined: 12-April 07
From: Edmonton, Alberta Canada


Yes - up to about 2000, or 2003, you could simply link to a Excel table from Access, and THEN you could edit that data as any other linked table.

However, someone claimed a patent on this ability. So Microsoft removed the feature. It was REALLY nice - miss the feature.

So, now you have to use code, and linked tables to Excel are READ only. At one time, Access could edit linked tables to Excel.

R
Albert
Go to the top of the page
 
FrankRuperto
post Mar 29 2020, 12:42 PM
Post#11



Posts: 825
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


Carlos Amado patented that feature in 1994 and the 17 years ran out in 2011. I'm surprised MS hasn't re-added that feature.

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
youngb
post Apr 2 2020, 03:00 AM
Post#12



Posts: 605
Joined: 2-September 03
From: Galway, Ireland


Hi Guys,

I find that fascinating, always had wondered why that ability to edit excel spreadsheets was gone after Office 2003, as I would have used it a lot, and still do, they only limitation is that you can't delete records, you can add new ones and edit, it really does have very practical uses when you need to update a lot of 'Convoluted spreadsheets.
Also I have change the IMEX Value by just specifying a new Connection String.

Regards
Brian
Go to the top of the page
 
youngb
post Apr 2 2020, 03:03 AM
Post#13



Posts: 605
Joined: 2-September 03
From: Galway, Ireland


Hi,

On a similar but slightly different topic I have a different question about adjusting the Connection String for Excel so I will start a new topic.

Brian
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    8th April 2020 - 06:36 AM