Hale, Jim
jim.hale at fleetpride.com
Tue Apr 8 13:18:55 CDT 2003
Several years ago I used hyperlinks in a financial summary workbook to help users navigate from report to report. I found that the links behaved differently on different machines. I was never able to pin down why and eventually moved to a different solution. Since then I have never used hyperlinks as part of a solution- they seem more trouble then they are worth. Jim -----Original Message----- From: Henry Simpson [mailto:hsimpson88 at hotmail.com] Sent: Tuesday, April 08, 2003 12:21 PM To: accessd at databaseadvisors.com Subject: RE: [AccessD] OT: Hyperlinks by automation Thanks Jim: I've had the basics running for about four years now except for saving the path in the Excel sheet. The procedure has always saved the invoice to a path that it knows exists (it creates it if it doesn't and confirms the path to the file after the save) and uses this path for the hyperlink which it could as well store in the same sheet as the numeric summary information in a hidden column. The text of the hyperlink is a five digit invoice number so the full path is hidden from the user. There is no need to use a separate sheet or file as I really like to keep it in a single sheet so users can copy/paste a single sheet to email a monthly summary and give the recipient access to the documents (password locked) over the WAN. Keeping it self contained also means there are fewer document interdependencies to fail. The sheet already stores client, project, previous invoice totals, contract value, contract changes and all data necessary to display any information our accounting department requires. There are also cells that display the previous year's totals for the month and totals to that month by fiscal year that use a cell reference to the other sheet and by colum totals within each sheet. My primary issue with the whole application is that the hyperlink is inconvenient in that it closes the workbook. Secondary issues are that I have more options for error handling and the option to open the folder that should contain the document should it not be present by using VBA code rather than a hyperlink. Hen >From: "Hale, Jim" <jim.hale at fleetpride.com> >Reply-To: accessd at databaseadvisors.com >To: "'accessd at databaseadvisors.com'" <accessd at databaseadvisors.com> >Subject: RE: [AccessD] OT: Hyperlinks by automation >Date: Tue, 8 Apr 2003 11:55:09 -0500 > >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 >_______________________________________________ >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com _________________________________________________________________ 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 -------------- next part -------------- An HTML attachment was scrubbed... URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030408/ed6e67fd/attachment-0001.html>