MS访问自动链接的Excel US preadsheets [英] MS Access Auto Link Excel Spreadsheets

问题描述

我有一个目录结构，其中我管理系统的要求与该系统具有其自己的目录的每一个组件。每个组件的要求被存储在一个excel工作簿具有多个工作表（＃工作表是静态的）。我目前使用的访问作为一个中心位置，以查看这些页上的信息并对其执行查询。我讨厌每一个新的组件文档添加到目录时手动链接新的Excel文件。有没有一种方法，当每次我开始接触，它会搜索存储的Excel文件的目录树，并自动将它们链接来访问，如果他们没有联系并更新我保存的查询，包括新的文件。我在想，我可以节省一个表中的子目录名称和所有的文件名中的子目录中的另一个表引用另一个表，这样它搜索文件系统，它比较名称表。这是可能的，如果因此可能有人点我在正确的方向。

I have a directory structure where I am managing the requirements of a system with each component of that system having its own directory. the requirements of each component are stored in a excel workbook that has multiple worksheets(# of worksheets are static). I am currently using access as a central location to view the information in these sheets and perform queries on them. I hate having to manually link new excel files every time a new component documentation is added to the directory. Is there a way that when everytime I start access it will search the directory tree of stored excel files and automatically link them to access if they're not linked and update my save queries to include the new files. I was thinking that I could save the sub directory names in a table and all the filenames in those sub directory in another table that references the other table, so as it searches the filesystem it compares names to the table. Is this possible if so could someone point me in the right direction.

推荐答案

您可以使用目录或FileSystemObject的递归从目录树中获取文件。访问存储的文件中的TableDef或你的连接属性中的链接信息可以得到它：

You can use Dir or the FileSystemObject recursively to get files from a directory tree. Access stores the link information of files in the connect property of the TableDef or you can get it from:

SELECT msysobjects.Database
FROM msysobjects
WHERE (((msysobjects.Database) Is Not Null));

您可以得到像这样的工作表：

You can get worksheets like so:

   ''Requires reference to the Microsoft Excel x.x Object Library

   Dim strFileName As String
   Dim objXL As New Excel.Application 
   Dim wkb As Excel.Workbook
   Dim wks As Object

   ''objXL.Visible = True

   strFileName = "C:\Docs\LTD.xls"
   Set wkb = objXL.Workbooks.Open(strFileName)

   For Each wks In wkb.Worksheets
      DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, _
            wks.Name, strFileName, True, wks.Name & "$"
   Next

   ''Tidy up
   wkb.Close
   Set wkb = Nothing
   objXL.Quit
   Set objXL = Nothing

或使用ADOX.Catalogue： HTTP：//forum.lessthandot ？.COM / viewtopic.php F = 95＆amp; T公司= 3712

这篇关于MS访问自动链接的Excel US preadsheets的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！