[AccessD] OT: Hyperlinks by automation

Henry Simpson hsimpson88 at hotmail.com
Tue Apr 8 12:21:03 CDT 2003


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



More information about the AccessD mailing list