<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2654.45">
<TITLE>RE: [AccessD] OT: Hyperlinks by automation</TITLE>
</HEAD>
<BODY>
<P><FONT SIZE=2>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.</FONT></P>
<P><FONT SIZE=2>Jim</FONT>
</P>
<P><FONT SIZE=2>-----Original Message-----</FONT>
<BR><FONT SIZE=2>From: Henry Simpson [<A HREF="mailto:hsimpson88@hotmail.com">mailto:hsimpson88@hotmail.com</A>]</FONT>
<BR><FONT SIZE=2>Sent: Tuesday, April 08, 2003 12:21 PM</FONT>
<BR><FONT SIZE=2>To: accessd@databaseadvisors.com</FONT>
<BR><FONT SIZE=2>Subject: RE: [AccessD] OT: Hyperlinks by automation</FONT>
</P>
<BR>
<P><FONT SIZE=2>Thanks Jim:</FONT>
</P>
<P><FONT SIZE=2>I've had the basics running for about four years now except for saving the </FONT>
<BR><FONT SIZE=2>path in the Excel sheet. The procedure has always saved the invoice to a </FONT>
<BR><FONT SIZE=2>path that it knows exists (it creates it if it doesn't and confirms the path </FONT>
<BR><FONT SIZE=2>to the file after the save) and uses this path for the hyperlink which it </FONT>
<BR><FONT SIZE=2>could as well store in the same sheet as the numeric summary information in </FONT>
<BR><FONT SIZE=2>a hidden column. The text of the hyperlink is a five digit invoice number </FONT>
<BR><FONT SIZE=2>so the full path is hidden from the user. There is no need to use a </FONT>
<BR><FONT SIZE=2>separate sheet or file as I really like to keep it in a single sheet so </FONT>
<BR><FONT SIZE=2>users can copy/paste a single sheet to email a monthly summary and give the </FONT>
<BR><FONT SIZE=2>recipient access to the documents (password locked) over the WAN. Keeping </FONT>
<BR><FONT SIZE=2>it self contained also means there are fewer document interdependencies to </FONT>
<BR><FONT SIZE=2>fail.</FONT>
</P>
<P><FONT SIZE=2>The sheet already stores client, project, previous invoice totals, contract </FONT>
<BR><FONT SIZE=2>value, contract changes and all data necessary to display any information </FONT>
<BR><FONT SIZE=2>our accounting department requires. There are also cells that display the </FONT>
<BR><FONT SIZE=2>previous year's totals for the month and totals to that month by fiscal </FONT>
<BR><FONT SIZE=2>year that use a cell reference to the other sheet and by colum totals within </FONT>
<BR><FONT SIZE=2>each sheet. My primary issue with the whole application is that the </FONT>
<BR><FONT SIZE=2>hyperlink is inconvenient in that it closes the workbook. Secondary issues </FONT>
<BR><FONT SIZE=2>are that I have more options for error handling and the option to open the </FONT>
<BR><FONT SIZE=2>folder that should contain the document should it not be present by using </FONT>
<BR><FONT SIZE=2>VBA code rather than a hyperlink.</FONT>
</P>
<P><FONT SIZE=2>Hen</FONT>
</P>
<P><FONT SIZE=2>>From: "Hale, Jim" <jim.hale@fleetpride.com></FONT>
<BR><FONT SIZE=2>>Reply-To: accessd@databaseadvisors.com</FONT>
<BR><FONT SIZE=2>>To: "'accessd@databaseadvisors.com'" <accessd@databaseadvisors.com></FONT>
<BR><FONT SIZE=2>>Subject: RE: [AccessD] OT: Hyperlinks by automation</FONT>
<BR><FONT SIZE=2>>Date: Tue, 8 Apr 2003 11:55:09 -0500</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>You could create a hidden sheet with an Excel "database" (ie flat file).</FONT>
<BR><FONT SIZE=2>>This worksheet could be updated from either Access or Excel as needed. Each</FONT>
<BR><FONT SIZE=2>>"record" could have whatever the relevant fields you need (document name,</FONT>
<BR><FONT SIZE=2>>customer, hyperlink, etc). For the user you would create a sheet with a</FONT>
<BR><FONT SIZE=2>>dropdown box to select the relevant document. VBA code could then open the</FONT>
<BR><FONT SIZE=2>>selected document. I have a model which loads data into an excel </FONT>
<BR><FONT SIZE=2>>spreadsheet</FONT>
<BR><FONT SIZE=2>>pivot table from Access using some of these techniques. You are welcome to</FONT>
<BR><FONT SIZE=2>>a copy if you think it would be helpful.</FONT>
<BR><FONT SIZE=2>>Jim Hale</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>-----Original Message-----</FONT>
<BR><FONT SIZE=2>>From: Jim DeMarco [<A HREF="mailto:Jdemarco@hshhp.org">mailto:Jdemarco@hshhp.org</A>]</FONT>
<BR><FONT SIZE=2>>Sent: Tuesday, April 08, 2003 11:15 AM</FONT>
<BR><FONT SIZE=2>>To: accessd@databaseadvisors.com</FONT>
<BR><FONT SIZE=2>>Subject: RE: [AccessD] OT: Hyperlinks by automation</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>Henry,</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>What about using an Excel Forms command button and VBA code to do the same</FONT>
<BR><FONT SIZE=2>>thing? Not as pretty but you'd have control of the screen.</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>HTH,</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>Jim DeMarco</FONT>
<BR><FONT SIZE=2>>Director of Product Development</FONT>
<BR><FONT SIZE=2>>HealthSource/Hudson Health Plan</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>-----Original Message-----</FONT>
<BR><FONT SIZE=2>>From: Henry Simpson [<A HREF="mailto:hsimpson88@hotmail.com">mailto:hsimpson88@hotmail.com</A>]</FONT>
<BR><FONT SIZE=2>>Sent: Tuesday, April 08, 2003 10:42 AM</FONT>
<BR><FONT SIZE=2>>To: accessd@databaseadvisors.com</FONT>
<BR><FONT SIZE=2>>Subject: [AccessD] OT: Hyperlinks by automation</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>I have an Access 97 procedure that creates a series of Word 97 Doc invoices</FONT>
<BR><FONT SIZE=2>>and adds a row in an Excel 97 sheet summarizing each invoice as it </FONT>
<BR><FONT SIZE=2>>generates</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>each document. Each document is saved into a specific project folder</FONT>
<BR><FONT SIZE=2>>determined by the application. In the past, the code also did a .SaveAs to</FONT>
<BR><FONT SIZE=2>>an invoicing folder created for the year and month so that all the </FONT>
<BR><FONT SIZE=2>>documents</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>could be retrieved from a single location. This made it easy to just drag</FONT>
<BR><FONT SIZE=2>>all the files from Explorer to a single email to accounting. The</FONT>
<BR><FONT SIZE=2>>alternative had been to open each project record, open the linked folder </FONT>
<BR><FONT SIZE=2>>and</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>drag one document at a time which was very tedious when there were many</FONT>
<BR><FONT SIZE=2>>invoices. The monthly group folder was supposed to be temporary but </FONT>
<BR><FONT SIZE=2>>because</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>of the convenience of being able to work with multiple invoices in a single</FONT>
<BR><FONT SIZE=2>>location, some users were inclined to make required revisions to invoices </FONT>
<BR><FONT SIZE=2>>in</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>this location rather than in the project folder. The end result was that </FONT>
<BR><FONT SIZE=2>>we</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>wound up with document version issues.</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>I've been implementing an alternate solution where the access application</FONT>
<BR><FONT SIZE=2>>inserts a hyperlink to each invoice document in the Excel summary sheet.</FONT>
<BR><FONT SIZE=2>>The advantage is that when a user makes a revision, he is reminded by the</FONT>
<BR><FONT SIZE=2>>presence of the Excel sheet to update the Excel entry and users have a</FONT>
<BR><FONT SIZE=2>>single place from which all invoices can be conveniently opened and the</FONT>
<BR><FONT SIZE=2>>document is the one in the actual project folder. Mail traffic is also</FONT>
<BR><FONT SIZE=2>>reduced since we had anywhere from 80 to 200 documents of about 25k size</FONT>
<BR><FONT SIZE=2>>that were being attached. Also, because I use a UNC link, the sheet works</FONT>
<BR><FONT SIZE=2>>to open pertinent documents from anywhere on the WAN.</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>Not having worked with hyperlinks before, I've run into an annoyance. When</FONT>
<BR><FONT SIZE=2>>a user merely clicks on a hyperlink, the Word doc opens but the Excel</FONT>
<BR><FONT SIZE=2>>workbook closes leaving the Excel application open. In order to keep the</FONT>
<BR><FONT SIZE=2>>Excel workbook open, it is necessary for users to right click the hyperlink</FONT>
<BR><FONT SIZE=2>>and select the hyperlink context sensitive menu item and then select Open </FONT>
<BR><FONT SIZE=2>>in</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>New Window from a fly out menu. All this mousing and clicking is annoying</FONT>
<BR><FONT SIZE=2>>and I would prefer that Excel remain open if the user simply (left) clicks</FONT>
<BR><FONT SIZE=2>>the hyperlink. Is there any way to implement this?</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>Hen</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>_________________________________________________________________</FONT>
<BR><FONT SIZE=2>>MSN 8 with e-mail virus protection service: 2 months FREE*</FONT>
<BR><FONT SIZE=2>><A HREF="http://join.msn.com/?page=features/virus" TARGET="_blank">http://join.msn.com/?page=features/virus</A></FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>_______________________________________________</FONT>
<BR><FONT SIZE=2>>AccessD mailing list</FONT>
<BR><FONT SIZE=2>>AccessD@databaseadvisors.com</FONT>
<BR><FONT SIZE=2>><A HREF="http://databaseadvisors.com/mailman/listinfo/accessd" TARGET="_blank">http://databaseadvisors.com/mailman/listinfo/accessd</A></FONT>
<BR><FONT SIZE=2>>Website: <A HREF="http://www.databaseadvisors.com" TARGET="_blank">http://www.databaseadvisors.com</A></FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>****************************************************************************</FONT>
<BR><FONT SIZE=2>>*******</FONT>
<BR><FONT SIZE=2>>"This electronic message is intended to be for the use only of the named</FONT>
<BR><FONT SIZE=2>>recipient, and may contain information from Hudson Health Plan (HHP) that </FONT>
<BR><FONT SIZE=2>>is</FONT>
<BR><FONT SIZE=2>>confidential or privileged. If you are not the intended recipient, you are</FONT>
<BR><FONT SIZE=2>>hereby notified that any disclosure, copying, distribution or use of the</FONT>
<BR><FONT SIZE=2>>contents of this message is strictly prohibited. If you have received this</FONT>
<BR><FONT SIZE=2>>message in error or are not the named recipient, please notify us</FONT>
<BR><FONT SIZE=2>>immediately, either by contacting the sender at the electronic mail address</FONT>
<BR><FONT SIZE=2>>noted above or calling HHP at (914) 631-1611. If you are not the intended</FONT>
<BR><FONT SIZE=2>>recipient, please do not forward this email to anyone, and delete and</FONT>
<BR><FONT SIZE=2>>destroy all copies of this message. Thank You".</FONT>
<BR><FONT SIZE=2>>****************************************************************************</FONT>
<BR><FONT SIZE=2>>*******</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>_______________________________________________</FONT>
<BR><FONT SIZE=2>>AccessD mailing list</FONT>
<BR><FONT SIZE=2>>AccessD@databaseadvisors.com</FONT>
<BR><FONT SIZE=2>><A HREF="http://databaseadvisors.com/mailman/listinfo/accessd" TARGET="_blank">http://databaseadvisors.com/mailman/listinfo/accessd</A></FONT>
<BR><FONT SIZE=2>>Website: <A HREF="http://www.databaseadvisors.com" TARGET="_blank">http://www.databaseadvisors.com</A></FONT>
<BR><FONT SIZE=2>>_______________________________________________</FONT>
<BR><FONT SIZE=2>>AccessD mailing list</FONT>
<BR><FONT SIZE=2>>AccessD@databaseadvisors.com</FONT>
<BR><FONT SIZE=2>><A HREF="http://databaseadvisors.com/mailman/listinfo/accessd" TARGET="_blank">http://databaseadvisors.com/mailman/listinfo/accessd</A></FONT>
<BR><FONT SIZE=2>>Website: <A HREF="http://www.databaseadvisors.com" TARGET="_blank">http://www.databaseadvisors.com</A></FONT>
</P>
<BR>
<P><FONT SIZE=2>_________________________________________________________________</FONT>
<BR><FONT SIZE=2>MSN 8 with e-mail virus protection service: 2 months FREE* </FONT>
<BR><FONT SIZE=2><A HREF="http://join.msn.com/?page=features/virus" TARGET="_blank">http://join.msn.com/?page=features/virus</A></FONT>
</P>
<P><FONT SIZE=2>_______________________________________________</FONT>
<BR><FONT SIZE=2>AccessD mailing list</FONT>
<BR><FONT SIZE=2>AccessD@databaseadvisors.com</FONT>
<BR><FONT SIZE=2><A HREF="http://databaseadvisors.com/mailman/listinfo/accessd" TARGET="_blank">http://databaseadvisors.com/mailman/listinfo/accessd</A></FONT>
<BR><FONT SIZE=2>Website: <A HREF="http://www.databaseadvisors.com" TARGET="_blank">http://www.databaseadvisors.com</A></FONT>
</P>
</BODY>
</HTML>