<!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>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.</FONT></P>
<P><FONT SIZE=2>Jim Hale</FONT>
</P>
<P><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>
</P>
<BR>
<P><FONT SIZE=2>Henry,</FONT>
</P>
<P><FONT SIZE=2>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.</FONT></P>
<P><FONT SIZE=2>HTH,</FONT>
</P>
<P><FONT SIZE=2>Jim DeMarco</FONT>
<BR><FONT SIZE=2>Director of Product Development</FONT>
<BR><FONT SIZE=2>HealthSource/Hudson Health Plan</FONT>
</P>
<BR>
<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 10:42 AM</FONT>
<BR><FONT SIZE=2>To: accessd@databaseadvisors.com</FONT>
<BR><FONT SIZE=2>Subject: [AccessD] OT: Hyperlinks by automation</FONT>
</P>
<BR>
<P><FONT SIZE=2>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 </FONT></P>
<P><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 an invoicing folder created for the year and month so that all the documents </FONT></P>
<P><FONT SIZE=2>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 </FONT></P>
<P><FONT SIZE=2>alternative had been to open each project record, open the linked folder and </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 because </FONT>
<BR><FONT SIZE=2>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 </FONT></P>
<P><FONT SIZE=2>this location rather than in the project folder. The end result was that we </FONT>
<BR><FONT SIZE=2>wound up with document version issues.</FONT>
</P>
<P><FONT SIZE=2>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 </FONT></P>
<P><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 to open pertinent documents from anywhere on the WAN.</FONT></P>
<P><FONT SIZE=2>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 </FONT></P>
<P><FONT SIZE=2>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 </FONT></P>
<P><FONT SIZE=2>and select the hyperlink context sensitive menu item and then select Open in </FONT>
<BR><FONT SIZE=2>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?</FONT></P>
<P><FONT SIZE=2>Hen</FONT>
</P>
<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>
<BR>
<P><FONT SIZE=2>***********************************************************************************</FONT>
<BR><FONT SIZE=2>"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".</FONT></P>
<P><FONT SIZE=2>***********************************************************************************</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>