Linking to excel datasource using VBA (1 Viewer)

[nortonm]

Hi All - I am currently needing to code a button that will create a linked table to an excel spreadsheet that sits in the OneDrive for each person who uses the database. The database handles tutorial bookings for academics in our school - they enter availability slots in the DB, then export as an excel file to OneDrive, whereupon the students put their name against a slot; the academic then pulls data into the DB from the spreadsheet, and handles calendar and email confirmation from within the DB.

I've tried a number of different ways, and i'm clearly misunderstanding the code's purpose. The one below gets me nearest - it does actually connect, but I have to close the database and reopen it before it shows in 'Tables'. Is it possible to refresh the view and show the table without closing and reopening the DB? If you have a more logical way of mapping a new connection that's also good for me. My code just happened to work, it wasn't 'played for'.

' Set variables
Dim db As DAO.Database
Dim tbl As DAO.TableDef
' Set database
Set db = CurrentDb
' Set table
Set tbl = db.CreateTableDef("rptTutorialBooking")
' Connect to excel sheet
tbl.Connect = "Excel 12.0;DATABASE=C:\Users\" & Environ("UserName") & "\OneDrive - My University\TutorialBookings\rptBookTutorial.xlsx"
' Set source sheet
tbl.SourceTableName = "qry_rptBookTutorial_export$"
' Append table
db.TableDefs.Append tbl

 

[pbaldy]

Try adding this at the end:

Application.RefreshDatabaseWindow

I normally just link with:

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "BankStatement", strPath, True

 

[nortonm]

Ah, brilliant - thanks very much!

 

[pbaldy]

Happy to help!