Hale, Jim
jim.hale at fleetpride.com
Tue Apr 8 11:55:09 CDT 2003
You could create a hidden sheet with an Excel "database" (ie flat file). This worksheet could be updated from either Access or Excel as needed. Each "record" could have whatever the relevant fields you need (document name, customer, hyperlink, etc). For the user you would create a sheet with a dropdown box to select the relevant document. VBA code could then open the selected document. I have a model which loads data into an excel spreadsheet pivot table from Access using some of these techniques. You are welcome to a copy if you think it would be helpful. Jim Hale -----Original Message----- From: Jim DeMarco [mailto:Jdemarco at hshhp.org] Sent: Tuesday, April 08, 2003 11:15 AM To: accessd at databaseadvisors.com Subject: RE: [AccessD] OT: Hyperlinks by automation Henry, What about using an Excel Forms command button and VBA code to do the same thing? Not as pretty but you'd have control of the screen. HTH, Jim DeMarco Director of Product Development HealthSource/Hudson Health Plan -----Original Message----- From: Henry Simpson [mailto:hsimpson88 at hotmail.com] Sent: Tuesday, April 08, 2003 10:42 AM To: accessd at databaseadvisors.com Subject: [AccessD] OT: Hyperlinks by automation I have an Access 97 procedure that creates a series of Word 97 Doc invoices and adds a row in an Excel 97 sheet summarizing each invoice as it generates each document. Each document is saved into a specific project folder determined by the application. In the past, the code also did a .SaveAs to an invoicing folder created for the year and month so that all the documents could be retrieved from a single location. This made it easy to just drag all the files from Explorer to a single email to accounting. The alternative had been to open each project record, open the linked folder and drag one document at a time which was very tedious when there were many invoices. The monthly group folder was supposed to be temporary but because of the convenience of being able to work with multiple invoices in a single location, some users were inclined to make required revisions to invoices in this location rather than in the project folder. The end result was that we wound up with document version issues. I've been implementing an alternate solution where the access application inserts a hyperlink to each invoice document in the Excel summary sheet. The advantage is that when a user makes a revision, he is reminded by the presence of the Excel sheet to update the Excel entry and users have a single place from which all invoices can be conveniently opened and the document is the one in the actual project folder. Mail traffic is also reduced since we had anywhere from 80 to 200 documents of about 25k size that were being attached. Also, because I use a UNC link, the sheet works to open pertinent documents from anywhere on the WAN. Not having worked with hyperlinks before, I've run into an annoyance. When a user merely clicks on a hyperlink, the Word doc opens but the Excel workbook closes leaving the Excel application open. In order to keep the Excel workbook open, it is necessary for users to right click the hyperlink and select the hyperlink context sensitive menu item and then select Open in New Window from a fly out menu. All this mousing and clicking is annoying and I would prefer that Excel remain open if the user simply (left) clicks the hyperlink. Is there any way to implement this? Hen _________________________________________________________________ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com **************************************************************************** ******* "This electronic message is intended to be for the use only of the named recipient, and may contain information from Hudson Health Plan (HHP) that is confidential or privileged. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or use of the contents of this message is strictly prohibited. If you have received this message in error or are not the named recipient, please notify us immediately, either by contacting the sender at the electronic mail address noted above or calling HHP at (914) 631-1611. If you are not the intended recipient, please do not forward this email to anyone, and delete and destroy all copies of this message. Thank You". **************************************************************************** ******* _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -------------- next part -------------- An HTML attachment was scrubbed... URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030408/84ada54d/attachment-0001.html>