Roger's Access Library Homepage
Forum Home Forum Home > Other Download Libraries > MS Access MVP Libraries > Tejpal, A.D.
  New Posts New Posts RSS Feed - ExcelHyperLinksToAccess
  FAQ FAQ  Forum Search   Events   Register Register  Login Login


Please consider donating $1 per sample downloaded, (find out why here)

ExcelHyperLinksToAccess

 Post Reply Post Reply
Author
Message
A.D. Tejpal View Drop Down
Microsoft MVP
Microsoft MVP


Joined: 30 Jun 2008
Status: Offline
Points: 192
Post Options Post Options   Thanks (0) Thanks(0)   Quote A.D. Tejpal Quote  Post ReplyReply Direct Link To This Post Topic: ExcelHyperLinksToAccess
    Posted: 23 Jan 2009 at 2:38am

ExcelHyperLinksToAccess
Sample Db - Explanatory Notes

    This sample db demonstrates import of excel data including hyperlinks if any. Direct import of specified data block from excel, using DoCmd.TransferSpreadsheet command is not able to pull the hyperlink details. To do so, it is necessary to convert the face value of hyperlink cells into full information suitable for proper interpretation in access table's hyperlink type field.

    Source excel file is placed in folder ExcelFile. This folder is located in the home folder (ExcelHyperLinksToAccess) that contains the sample db. It should be ensured that in the excel workbook, the sheet having source data is the active sheet (Open the workbook, select the sheet having source data, save and close). First row of data block should carry field names matching those in access table named T_Imported.

    On opening the main form, excel source sheet is displayed in an unbound OLE control. Through auto detection, available data block is shown enclosed in a solid border. If there is any column within this data block that carries hyperlinks, it gets highlighted in special color. Row and column numbers of first cell (i.e. top left cell) of excel data block, as well as column number of hyperlink column, get displayed in text boxes. If found necessary, the user can edit these values.

    After verifying that excel data block is displayed correctly, clicking of Import button carries out the import into table T_Imported, after converting the face value of cells in hyperlink column so as to suit the requirements of hyperlink type field in access table.

    If data is required to be imported from more than one worksheet in a given workbook, the process can be repeated by making different sheets active in turn (Open the workbook in folder ExcelFile, select the desired sheet, save and close).

    Important:
    It is observed that DoCmd.TransferSpreadsheet action, if carried out as an integral part of automation code, can lead to sticking of excel application (excel refuses to quit despite explicit quit command and setting the object variables to nothing). For this reason, implementation of  DoCmd.TransferSpreadsheet has been carried out independently, after execution of automation code modules.

References:
    (a) Microsoft Excel Object library (version 9.0 or later)
    (b) Microsoft Scripting RunTime
    (c) DAO 3.6

Version - Access 2000 File Format

Download >>:  uploads/37/ExcelHyperLinksToAccess.zip


--

Email: adtejpal@gmail.com
Back to Top
Sponsored Links


Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down