Link an Excel sheet to a Table in MS Access - Using VBScript

Question

Hi,

I am trying to create a Linked table in MS Access linked to Excel sheet. I want to do this through VBscripting.

My Scenario is i will have a excel sheet which will be updated very often. But my script picks up the values from the table in MSAccess which should be a replica of the Excel sheet (Linked table).

So i want to know if there is any code in VBscript wherein i can create a Linked table to ExcelSheet.

Any help is Greatly Appreciated!!

Answer 1

Yes you are correct. But my question is to "How to link an Excel with MS Access Table using VBScript" for the first time itself. Excel is not already linked.

Answer 2

Here is some sample script.

   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

From: http://wiki.lessthandot.com/index.php/Linking_Tables_via_Jet_and_ADO

Answer 3

Hi,

Thanks for the reply. I am able to undrestand and implement the 'Link Excel by Sheet Name'

Can you please help me understand the other part 'Link Excel using named range' wherein it uses .properties("Jet OLEDB:Remote Table Name") = "Data_Range" Not sure what is/should the Data_Range be here?

Thanks Again!!