MS Access Auto Link Excel Spreadsheets

Question

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.

Answer 1

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

Or using an ADOX.Catalogue: http://forum.lessthandot.com/viewtopic.php?f=95&t=3712