Author: A.D. Tejpal
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.
You can find the sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=444
Link to this post: ExcelHyperLinksToAccess
.
No comments:
Post a Comment