Сохранить ссылку на Excel при изменении имени рабочего листа

У меня есть связанная таблица tblREDEIMPORT, для которой задан определенный путь, который доступен только программно через процесс импорта FSO, где он перезаписывает версию предыдущего дня.

Однако, хотя связанный файл .xls всегда называется одним и тем же, имя листа, на котором он находится, меняется каждый день, что приводит к ошибке, например 215380_REDEFILEIMPORTREPORT_230$, не является допустимым именем, потому что это было вчерашнее имя листа, сегодняшнее имя листа имеет полностью разный набор цифр до и после.

Через диспетчер связанных таблиц я не могу указать связанную таблицу ни на что, кроме этого листа. Как я могу изменить путь к связанной таблице, чтобы всегда смотреть на первую (и единственную) электронную таблицу в книге или, по крайней мере, изменить ее, чтобы динамически обновлять имя рабочего листа, на который он должен указывать?


vba ms-access
person Seth E    schedule 24.10.2013    source источник
comment
Вы можете воссоздать связанные таблицы с помощью VBA, что позволит динамически изменять такие вещи, как имена рабочих листов. Что-то вроде этого (образец из инструмента, который я сделал много лет назад) DoCmd.TransferDatabase acLink, Microsoft Access, fileloc, , tablename, DB_OCIE   -  person Alan Waage    schedule 24.10.2013
comment
Я не знал, что acLink нужен, это должно помочь, я попробую. Спасибо!   -  person Seth E    schedule 24.10.2013

Ответы (1)


arrow_upward
4
arrow_downward

В качестве альтернативы DoCmd.TransferSpreadsheet acLink, ... вы можете просто «клонировать» существующий объект TableDef, настроить свойство .SourceTableName и заменить обновленный объект TableDef существующим. Преимущество этого подхода заключается в сохранении существующего местоположения файла, типа документа Excel и т. д., избавляя вас от соблазна жестко закодировать эти значения в операторе DoCmd.TransferSpreadsheet.

Например, у меня есть связанная таблица в Access с именем [LinkedTableInExcel], которая указывает на лист с именем OldSheetName в документе Excel. Я могу убедиться, что связанная таблица работает, используя выражение DCount() в окне Immediate VBA.

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

Теперь, если я открою документ в Excel и изменю имя листа на NewSheetName, связанная таблица в Access перестанет работать

BadSheetName.png

Однако я могу обновить связанную таблицу следующим образом

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.
person Gord Thompson    schedule 27.10.2013
comment
Пришлось немного поработать над кодом. Я сохранил tbd.SourceTableName в переменной, чтобы можно было повторно использовать ее при объявлении tbdNew.SourceTableName. Я может не все понял до конца. Все, что сказал --- ОЧЕНЬ ПОЛЕЗНО и СПАСИБО. - person Smandoli; 15.01.2014