As you can see from my screen shot, I’m using the following sub routine to link various worksheets from ExcelFile.xlsx into my database. As shown below, I’ve linked only four worksheets. However, my actually worksheet has many more tabs that I need to link. Also, the tab names are not constant. In other words, I could have three worksheets named like: 1111, 2222, 3333. Then at a later time for the exact same file, I could have four worksheets with multiple other names like: AAAA, BBBB, CCCC, DDDD.
Right now, it’s a manual process for me to update this code each time a sheet name changes or is removed. I’d like to modify this code to be more dynamic by accomplishing the following:
1. First delete all database table objects that have a string of 4 characters in their name (e.g. 1111, 2233, AABC)
2. Link all worksheet tabs that have a string of 4 characters in their name from ExelFile.xlsx
I'm using MS office 2016. Any Expert suggestion on this is greatly appreciated! =)
Private Sub LinkSpreadSheets()
Dim Fpath As String, _
XLname1 As String, _
tb1 As String, _
tb2 As String, _
tb3 As String, _
tb4 As String
Fpath = Environ("USERPROFILE") & "\Documents\databases"
XLname1 = "\ExcelFile.xlsx"
tb1 = "1001"
tb2 = "2001"
tb3 = "3001"
tb4 = "F008"
.DeleteObject acTable, "1001"
.DeleteObject acTable, "2001"
.DeleteObject acTable, "3001"
.DeleteObject acTable, "F008"
.TransferSpreadsheet acLink, , tb1, Fpath & XLname1, True, "1001$"
.TransferSpreadsheet acLink, , tb2, Fpath & XLname1, True, "2001$"
.TransferSpreadsheet acLink, , tb2, Fpath & XLname1, True, "3001$"
.TransferSpreadsheet acLink, , tb2, Fpath & XLname1, True, "F008$"