CODEWITHSUNDEEP
vbams-access
Seth E
Seth E

Reputation: 165

Preserve link to Excel when name of worksheet changes

I have a linked table, tblREDEIMPORT that is set to a specific path that's only accessible programatically through a FSO import process where it overwrites the previous day's version.

However, while the .xls linked file is always named the same thing, the name of the sheet that it's on changes every day giving me an error like 215380_REDEFILEIMPORTREPORT_230$ is not a valid name, because that was yesterdays sheet name, todays sheetname has a completely different set of numbers before and after.

Through the linked table manager, I'm unable to point the linked table to anything but that sheet. How can I change the linked table path to either always look at the first (and only) spreadsheet in the workbook, or at least change it to dynamically update the name to the worksheet it should be pointing at?

Upvotes: 1

Views: 3173

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123409

As an alternative to DoCmd.TransferSpreadsheet acLink, ... you can simply "clone" the existing TableDef object, tweak the .SourceTableName property, and swap the updated TableDef object for the existing one. This approach would have the advantage of preserving the existing file location, Excel document type, etc., saving you from the temptation of hard-coding those values into the DoCmd.TransferSpreadsheet statement.

For example, I have a linked table in Access named [LinkedTableInExcel] that points to a sheet named OldSheetName in an Excel document. I can verify that the linked table is working by using a DCount() expression in the VBA Immediate Window

?DCount("*","LinkedTableInExcel")
 2

Now if I open the document in Excel and change the sheet name to NewSheetName the linked table in Access stops working

BadSheetName.png

However, I can update the linked table as follows

Sub UpdateExcelLinkedTable()
Dim cdb As DAO.Database
Dim tbd As DAO.TableDef, tbdNew As DAO.TableDef
Dim n As Long
Const LinkedTableName = "LinkedTableInExcel"
Set cdb = CurrentDb

Set tbd = cdb.TableDefs(LinkedTableName)
Debug.Print "Current .SourceTableName is: " & tbd.SourceTableName
On Error Resume Next
n = DCount("*", LinkedTableName)
Debug.Print "The linked table is " & IIf(Err.Number = 0, "", "NOT ") & "working."
On Error GoTo 0

Set tbdNew = New DAO.TableDef
tbdNew.Name = tbd.Name
tbdNew.Connect = tbd.Connect
tbdNew.SourceTableName = "NewSheetName$"
Set tbd = Nothing
cdb.TableDefs.Delete LinkedTableName
cdb.TableDefs.Append tbdNew
Set tbdNew = Nothing

Set tbd = cdb.TableDefs(LinkedTableName)
Debug.Print "Updated .SourceTableName is: " & tbd.SourceTableName
On Error Resume Next
n = DCount("*", LinkedTableName)
Debug.Print "The linked table is " & IIf(Err.Number = 0, "", "NOT ") & "working."
On Error GoTo 0

Set tbd = Nothing
Set cdb = Nothing
End Sub

results:

Current .SourceTableName is: OldSheetName$
The linked table is NOT working.
Updated .SourceTableName is: NewSheetName$
The linked table is working.

Upvotes: 4

Related Questions