Microsoft KB Archive/173748: Difference between revisions
(importing KB archive) |
m (Text replacement - "&" to "&") |
||
(One intermediate revision by the same user not shown) | |||
Line 77: | Line 77: | ||
' strNewAccTable - the name of your new linked table. | ' strNewAccTable - the name of your new linked table. | ||
' strXLFileName - the path and name of your Excel file. This | ' strXLFileName - the path and name of your Excel file. This | ||
' should be in the form | ' should be in the form "C:\MyDir\MyFile.xls." | ||
' strImportSheet - the name of the sheet you want to link. | ' strImportSheet - the name of the sheet you want to link. | ||
' All these variable are strings, and should be supplied to the | ' All these variable are strings, and should be supplied to the | ||
Line 97: | Line 97: | ||
' line and comment out the Excel 8.0 line when working with | ' line and comment out the Excel 8.0 line when working with | ||
' Excel 5.0/95. | ' Excel 5.0/95. | ||
' td.Connect = | ' td.Connect = "Excel 5.0;DATABASE=" & strXLFileName & ";" | ||
td.Connect = | td.Connect = "Excel 8.0;DATABASE=" & strXLFileName & ";" | ||
td.SourceTableName = strImportSheet & | td.SourceTableName = strImportSheet & "$" | ||
' Append the new TableDef to the TableDefs collection. | ' Append the new TableDef to the TableDefs collection. | ||
Line 108: | Line 108: | ||
XLError: | XLError: | ||
MsgBox Err.Number & | MsgBox Err.Number & " " & Err.Description | ||
Resume Exit_XLLink | Resume Exit_XLLink | ||
Line 118: | Line 118: | ||
<div class="indent"> | <div class="indent"> | ||
<p>XLLink | <p>XLLink "New Link", "C:\My Documents\LinkTest.xls", "Sheet1"</p> | ||
</div></li></ol> | </div></li></ol> | ||
Line 128: | Line 128: | ||
== REFERENCES == | == REFERENCES == | ||
For more information about connection strings, search the Help Index for | For more information about connection strings, search the Help Index for "Connect Property," or ask the Microsoft Access 97 Office Assistant. For more information about TableDefs, search the Help Index for "DAO collections, TableDefs," or ask the Microsoft Access 97 Office Assistant.<br /> | ||
<br /> | <br /> | ||
For additional information about getting help with Visual Basic for Applications, please see the following article in the Microsoft Knowledge Base: | For additional information about getting help with Visual Basic for Applications, please see the following article in the Microsoft Knowledge Base: |
Latest revision as of 12:29, 21 July 2020
Article ID: 173748
Article Last Modified on 1/20/2007
APPLIES TO
- Microsoft Access 95 Standard Edition
- Microsoft Access 97 Standard Edition
This article was previously published under Q173748
Advanced: Requires expert coding, interoperability, and multiuser skills.
SUMMARY
You can use data access objects (DAO) to programmatically link a Microsoft Excel spreadsheet using Microsoft Visual Basic for Applications code.
MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. The following example uses DAO in a Visual Basic procedure to link a Microsoft Excel spreadsheet.
- Start Microsoft Excel.
On Sheet1 of a new workbook, type the following in cells A1:C3:
A1: First B1: Last C1: Middle. A2: Adam B2: Smith C2: A. A3: Bob B3: Jones C3: B.
- Save the workbook as LinkTest.xls in the folder My Documents.
- Close the workbook and quit Microsoft Excel.
- Start Microsoft Access and create a new database.
Create a new module and type the following procedure in it:
Sub XLLink(strNewAccTable as string, strXLFileName as String, _ strImportSheet as String) ' Variables: ' strNewAccTable - the name of your new linked table. ' strXLFileName - the path and name of your Excel file. This ' should be in the form "C:\MyDir\MyFile.xls." ' strImportSheet - the name of the sheet you want to link. ' All these variable are strings, and should be supplied to the ' subroutine enclosed in quotation marks. On Error GoTo XLError Dim db As DATABASE Dim td As TableDef Set db = CurrentDb ' Create a new TableDef using the passed name. Set td = db.CreateTableDef(strNewAccTable) ' Set the ConnectString property to the Excel file to link. ' In Microsoft Access 7.0, the ConnectString needs to reflect the ' version of Excel. Remove the apostrophe from the Excel 5.0 ' line and comment out the Excel 8.0 line when working with ' Excel 5.0/95. ' td.Connect = "Excel 5.0;DATABASE=" & strXLFileName & ";" td.Connect = "Excel 8.0;DATABASE=" & strXLFileName & ";" td.SourceTableName = strImportSheet & "$" ' Append the new TableDef to the TableDefs collection. db.TableDefs.Append td Exit_XLLink: Exit Sub XLError: MsgBox Err.Number & " " & Err.Description Resume Exit_XLLink End Sub
- On the Debug menu, click Compile Loaded Modules.
- On the Tools menu, click Debug Window.
- In the Debug window, type:
XLLink "New Link", "C:\My Documents\LinkTest.xls", "Sheet1"
REFERENCES
For more information about connection strings, search the Help Index for "Connect Property," or ask the Microsoft Access 97 Office Assistant. For more information about TableDefs, search the Help Index for "DAO collections, TableDefs," or ask the Microsoft Access 97 Office Assistant.
For additional information about getting help with Visual Basic for Applications, please see the following article in the Microsoft Knowledge Base:
163435 VBA: Programming Resources for Visual Basic for Applications
Additional query words: wordcon inf vba
Keywords: kbhowto kbprogramming KB173748