Microsoft Access: Visual Basic & Writing to a Linked XLS File

Microsoft Access: Visual Basic & Writing to a Linked XLS File
Page content

Powerful Data Linking: Access and Excel

With Microsoft Access, Visual Basic writes to linked XLS (Excel) files. Data linking between Access and Excel synchronizes data so that an exact copy is always available in both documents. This differs from importing in that the same data is used in both places at once, rather than a copy of the data. The end result is that you can view the data in Access tables in one presentation, and the other way of viewing the same data is in an Excel worksheet. As you refresh the information in the worksheet, your amendments are shown in the linked table in Access.

Linking creates a new table in Access linked to the worksheet source columns. Linked tables are special tables in Access, as normal tables cannot be linked to from within Excel. The linked table organizes the information from the source worksheet or named range, but doesn’t physically store the information in the Access file. There can be multiple linked tables, and changes made in Excel are automatically mirrored in the linked table. To link an Excel worksheet within Access the stages are as follows:

In Access, open the dialogue box to browse for files to link, change the “Files of Type” list choice to Microsoft Office Excel Files, and select the database to link to. The Link Spreadsheet Wizard will then commence its operation.

The Linking Process

In order to link data correctly, it will be necessary to identify the exact data to link to. This will be in the Excel file containing the source data, contained in a worksheet. You can only link to one worksheet or named range in a single operation. For multiple worksheets or named ranges, the operation will need to be repeated. The next step is to identify the destination database and the table that it will be linked to. This will be in an Access file on the computer drive. To create a new link without using an existing database a new Access database file will need to be created. If, however, a linked table already exists with the same name in a database, Access will overwrite the linked table with the new information.

Linking Rules

To link data, columns must be in a tabular format. In the case of merged columns, the leftmost column contains the data and other fields are empty. One other feature of linking is that names of cells in Excel can be converted to field names in the Access database during the linking process. The best way to view linked data in Access is to use the Datasheet view as this corresponds exactly to the source file.

Permissions

Commence the link operation by ensuring that the target Access database is not Read Only and permissions are available to amend it.

A new database will need to be created if it does not exist. The database menu has an option, “Get External Data.” Choose this and then click on “link tables.”

A link dialogue box will then be presented, listing a “Files of type” box. From there choose Microsoft Excel. This leads you to the next dialogue, the “Look in” field, which allows you to choose the folder and file you want to link to.

At this point, the Link Spreadsheet Wizard dialog boxes will appear, and it is simply a case of following the wizard instructions, choosing a table to link to and picking the fields required. Once this process is complete the link can be tested by changing data on one side of the link (Access or Excel) and verifying that the data has changed in the other document.

References

Microsoft Linking Help : https://office.microsoft.com/en-us/access-help/import-export-and-link-data-between-access-and-excel-HP001095095.aspx#_Toc269881996