Henry Simpson
hsimpson88 at hotmail.com
Tue Apr 8 11:58:40 CDT 2003
Thanks Jim: Your suggestion is the manner in which I would ordinarily have implemented this solution though I would probably just add a menu item when the workbook loads. Buttons really aren't that pretty on workbooks even though you can set them not to print. The code behind the workbook would be a simple shell execute declare and I would add a hidden column that stored the path string for each invoice rather than store it in the hyperlink. Then code in the menu event to pass the path from the currently selected row to the shell execute procedure and it's done. Using a coded procedure also allows the procedure to check for the continued existence of the file, and failing that, the folder and provide a more meaningful message to the user than 'Cannot open the specified file.' as well as provide the option to pass the project folder to the shell execute (which opens the project folder for exploring) should that exist. Never having used hyperlinks before, I thought it would be interesting to explore the possibilities and this seemed a logical and intuitive place, but the complete lack of error handling and control of error messages makes me wonder why anyone would use hyperlinks. If there really is no way to simply click on the hyperlink and keep the Excel workbook open, I will likely switch back to my old way of doing things. Hen >From: "Jim DeMarco" <Jdemarco at hshhp.org> >Reply-To: accessd at databaseadvisors.com >To: <accessd at databaseadvisors.com> >Subject: RE: [AccessD] OT: Hyperlinks by automation >Date: Tue, 8 Apr 2003 12:14:38 -0400 > >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 _________________________________________________________________ Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail