工作表名称更改时保留指向Excel的链接 [英] Preserve link to Excel when name of worksheet changes

问题描述

我有一个链接表tblREDEIMPORT，该表设置为特定路径，该路径只能通过FSO导入过程以编程方式访问，在该过程中它将覆盖前一天的版本.

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.

但是，尽管.xls链接文件始终具有相同的名称，但是它所在的工作表的名称每天都在变化，这给我一个类似215380_REDEFILEIMPORTREPORT_230$的错误，这不是有效的名称，因为那是昨天的工作表名称，今天的工作表名称前后都有一组完全不同的数字.

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?

推荐答案

作为DoCmd.TransferSpreadsheet acLink, ...的替代方案，您可以简单地克隆"现有的TableDef对象，调整.SourceTableName属性，并交换更新的对象为现有对象.这种方法的优点是可以保留现有文件位置，Excel文档类型等，从而避免了将这些值硬编码到DoCmd.TransferSpreadsheet语句中的诱惑.

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.

例如，我在Access中有一个名为[LinkedTableInExcel]的链接表，该链接表指向Excel文档中名为OldSheetName的工作表.我可以在VBA即时窗口中使用DCount()表达式来验证链接表是否正常工作

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 

现在，如果我在Excel中打开文档并将工作表名称更改为NewSheetName，Access中的链接表将停止工作

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

但是，我可以如下更新链接表

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

结果:

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

这篇关于工作表名称更改时保留指向Excel的链接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！