XP - Automated Excel Link Table using OnOpen Event

[mastro78]

I need to create a function that will link all the Excel files in the folder where my database is located (however, that path could change). The common spreadsheet in all these possible Excel files will be named Details (range A2:BE2) and that is the only one I want linked to my Access database out of each possible Excel file. Please help, this is a pending project I got assigned to and I'm at a loss since this exceeds my current knowledge base.

PS
If there is a way to combine all the linked Excel table data into one table that would be great too. THANK YOU!!!

 

[JerryKlmns]

CurrentProject.Path gives you ... the path! Use the Application.FileSearch to find all excel files in a folder, and search TT for Linking/Re-linking tables (DAO or ADOX).But you cannot link different excel files or workseets to one table. Plus editing an excel file is not allowed since 2002 versions, due to copyrights. That said you 'll have to import excel data to one or more access tables.

That's for getting you started. Do your search & development and post back if you hit a deadend.

Good luck.

 

[mastro78]

Function ImportToExcel()
Dim fs, f, s
Dim ExcelFileName As String
Dim PathToExcelFiles As String

Set fs = CreateObject("Scripting.FileSystemObject")

PathToExcelFiles = "D:\*.*"

ExcelFileName = Dir(PathToExcelFiles, vbDirectory)
Do While ExcelFileName <> ""

If ExcelFileName <> "." And ExcelFileName <> ".." And Right(ExcelFileName, 3) = "xls" Then
Set f = fs.GetFile("D:\" & ExcelFileName)
s = f.DateCreated
DoCmd.TransferSpreadsheet acLink, 8, ExcelFileName, "D:\" & ExcelFileName, True
End If

ExcelFileName = Dir
Loop

End Function

Now how would I go about calling this function in the On Open event of the form???