将Excel表格链接到MS Access中的表格 - 使用VBScript

Question

我想在链接到Excel表格的MS Access中创建一个链接表格。我想通过VB脚本来做到这一点。

我的情景是我将有一个Excel表格，将经常更新。但是我的脚本从MSAccess中的表中获取值，该值应该是Excel工作表（链接表）的副本。

所以我想知道是否有VBscript中的任何代码，其中我可以创建一个链接表到Excel工作表。

Answer 1

以下是一些示例脚本。

Dim cn ''As ADODB.Connection 
    Dim ct ''As ADOX.Catalog 
    Dim tbl ''As ADOX.Table 

    Dim strLinkXL ''As String 
    Dim strMDB ''As String 

    strLinkXL = "C:\Docs\LTD.xls" 
    strMDB = "C:\Docs\LTD.mdb" 

    ''Create Link... 
    Set cn = CreateObject("ADODB.Connection") 
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
      "Data Source=" & strMDB & ";" & _ 
      "Persist Security Info=False" 

    Set ct = CreateObject("ADOX.Catalog") 
    Set ct.ActiveConnection = cn 

    Set tbl = CreateObject("ADOX.Table") 
    Set tbl.ParentCatalog = ct 


    ''Link Excel using named range 
    Set tbl = CreateObject("ADOX.Table") 
    Set tbl.ParentCatalog = ct 

    With tbl 
    .Name = "LinkTableXLRange" 
    .properties("Jet OLEDB:Link Provider String") = "Excel 8.0;DATABASE=" _ 
     & strLinkXL & ";HDR=Yes" 
    ''The named range 
    .properties("Jet OLEDB:Remote Table Name") = "Data_Range" 
    .properties("Jet OLEDB:Create Link") = True 
    End With 

    ''Append the table to the tables collection 
    ct.Tables.Append tbl 
    Set tbl = Nothing 

    ''Link Excel by sheet name 
    Set tbl = CreateObject("ADOX.Table") 
    Set tbl.ParentCatalog = ct 

    With tbl 
    .Name = "LinkTableXLSheet" 
    .properties("Jet OLEDB:Link Provider String") = "Excel 8.0;DATABASE=" _ 
      & strLinkXL & ";HDR=Yes" 
    ''Note the use of $, it is necessary 
    .properties("Jet OLEDB:Remote Table Name") = "Sheet2$" 
    .properties("Jet OLEDB:Create Link") = True 
    End With 

    ''Append the table to the tables collection 
    ct.Tables.Append tbl 
    Set tbl = Nothing 

来源：http://wiki.lessthandot.com/index.php/Linking_Tables_via_Jet_and_ADO