Блог

Как заставить книгу Excel использовать саму себя в качестве источника ссылок на лист?

Я пишу макрос VBA для автоматизации процесса добавления листа Excel в рабочую книгу. Рабочий лист (лист1) принимает только определенные (но очень многие) ответы из нескольких листов (ответ1, ответ2, ответ3) вопросника. В результате этого лист1 содержит множество ссылок на ячейки, которые никуда не ведут до тех пор, пока не будет запущен макрос.

Например, a1 в sheet1 «=’response1′!b6». Это возвращает #REF! ошибка перед запуском макроса (что нормально). После запуска макроса лист1 теперь находится внутри правильной книги, и «=’response1′!b6» теперь является допустимой ссылкой на ячейку.

За исключением того, что Excel не осознает этого до тех пор, пока я вручную не щелкну ячейку на листе 1, нажмите f2, затем нажмите enter. Когда я делаю это, ячейка заполняется правильно. Проблема в большом количестве ячеек.

1. Возможно ли создать макрос VBA, который будет имитировать этот процесс выбора полей формул и нажатия «Enter». Просматривая людей с похожими проблемами, большинство из них исправили проблему с помощью некоторой комбинации клавиш f9, повторного включения автоматического вычисления или ActiveSheet.Вычислить или вариант. Ни одно из этих действий не сработало, похоже, это проблема со ссылками, даже если ссылки указывают на допустимые местоположения.

2. В противном случае, возможно ли использовать VBA для выполнения того же процесса, что и: Данные> Редактировать ссылки> Обновлять значения, но в этом случае нам нужно было бы указать текущую открытую книгу в качестве ее собственного источника. Есть ли какой-либо способ сделать это?

3. Когда я вручную выбрал текущую книгу в качестве источника в разделе «Редактировать ссылки> Обновить значения», Excel странным образом повторяет имя листа в ссылках на ячейки, например, так: «='[response1] response1!B31», который затем не обновляется при изменении ячейки b31, так что это не решение.

Вот код, который запускается при нажатии кнопки:

 Private Sub CommandButton1_Click()
'copy worksheet into responses
Dim CopyFromWbk As Workbook
Dim CopyToWbk As Workbook
Dim CopyToWbk As Workbook

 Set CopyFromWbk = Workbooks("Addition.xlsm")
 Set ShToCopy = CopyFromWbk.Worksheets("Sheet1")
 Set CopyToWbk = Workbooks("QuestionnaireResponses.xlsm")
 ShToCopy.Copy After:=CopyToWbk.Sheets(CopyToWbk.Sheets.Count)

Workbooks("QuestionnaireResponses.xlsm").Activate

'Put code to update links in here
ThisWorkbook.UpdateLink Name:="myfilepathgoeshere.QuestionnaireResponses.xlsm", Type:=xlExcelLinks
'End update links
  

Спасибо за любую помощь, этот — головная боль.

Отличная идея от @Kyle. Для тех, у кого возникли проблемы с принудительным обновлением ссылок на ячейки, TextToColumns работает.

Однако TextToColumns выдает ошибку, если диапазон источника пуст, поэтому, если есть какая-либо вероятность, что это так, используйте инструкцию if без привязки к действию, чтобы пропустить эти экземпляры.

Мой успешный код выглядит следующим образом:

 Dim i As Integer

For i = 1 To 1004 
'Scans through row 2 from col A onwards
'If cell is empty, does nothing. 
'If cell is not empty, performs TextToColumns where source range = target range.
If IsEmpty(Workbooks("QuestionnaireResponses.xlsm").Worksheets_
("response1").Cells(2, i)) Then 'Does nothing if the cell is empty.
Else
Workbooks("QuestionnaireResponses.xlsm").Worksheets("response1").Cells(2, i).Select
Selection.TextToColumns Cells(2, i) 'Performs TextToColumns
End If

Next
  

Все мои данные находятся в одной длинной строке. Чтобы применить вышесказанное ко всей электронной таблице, просто вставьте все между, включая For i = 1 и Next в другой For цикл с заменой другой буквы i .