Excel и Word

[Foxx]

Здравствуйте! Ситуация: написали прогу в Excel. Сделали отчет в Word, связанный с Excel. Потом пришлось добавить парочку строк в прогу, тем самым все строки сдвинулись и есественно в Wordе будет не то что нужно. Как сделать так, чтобы даже при добавлении строк в Excel, нужные ссылки на нужные ячейки сохранились? Такое возможно?

[EducatedFool]

Такое возможно.

Невозможно другое - основываясь на представленной вами весьма скудной информации о "проге", предложить вам дельные советы или варианты решения проблемы


--------------------- добавлено позже - вдруг кому окажется полезным ------------------------

Сделал программу для заполнения документов данными из таблицы Excel (из Excel в Word)

--------------------
Надстройка предназначена для формирования (подготовки) документов по шаблонам, с заполнением созданных файлов данными из текущей книги Excel.

В качестве шаблонов могут выступать следующие типы файлов:

• документы Word (расширения DOC, DOCX, DOCM)
• шаблоны Word (расширения DOT, DOTX, DOTM)
• книги Excel (расширения XLS, XLSX, XLSM, XLSB)
• шаблоны Excel (расширения XLT, XLTX, XLTM)
• текстовые документы (расширения TXT, DAT, XML и т.д.)

В качестве исходных данных для заполнения, используется открытый в Excel файл (с таблицей, содержащей строку заголовка)

Количество шаблонов документов не ограничено

ПРОГРАММА ПОДДЕРЖИВАЕТ ФОРМИРОВАНИЕ ДОКУМЕНТОВ В ФОРМАТЕ PDF
(создаётся файл DOC, и преобразуется в PDF средствами Word 2007 - 2010)
--------------------------------
В каких случаях вам может пригодиться эта надстройка:

• если у вас в таблице Excel хранится список контрагентов (с их реквизитами), и вы хотите нажатием одной кнопки формировать договоры, акты или коммерческие предложения для каждого из них
• если в вашей таблице Excel хранятся паспортные данные сотрудников, и вам необходимо быстро сформировать по шаблону приказы или прочие документы в формате Word, заполнив созданные документы данными сотрудников
• подготовить письма, уведомления, грамоты в формате PDF или Word, заполнив созданные файлы информацией с листа Excel
• и т.д. и т.п. - надстройку можно применять для создания любых документов на основе данных из Excel

--------------------------------

Скачать надстройку можно здесь:
http://excelvba.ru/programmes/FillDocuments

[Foxx]

Там будут переменные, чисто как калькулятор, для вычислений множества значений.
Банально. Все строки в Excele до 9 включительно забиты переменными и фомулами. Последнняя строка: a9=5; b9=6; c9=s=a9+b9=11. А в Wordе отчет, последняя строка: s=a+b=5+6=11. Сохранили. Потом в Excel добавили еще 2 строки между 5 и 6, так что a9 теперь a11. Все сместилось. Нужно, чтобы в Worde остались все ссылки как должно быть (в тексте и в редакторе формул на числа), и далее можно было бы добавить еще формулы (ссылки на верхние строки в Excele, которые добавили). Вот так.

[EducatedFool]

А как Вы осуществляете вставку в Word?

Попробуйте такой вариант:
1) Выделяем диапазон ячеек, который должен оказаться в Word-е
2) Присваиваем выделенному диапазону имя (слева от строки формул есть поле для ввода имени)
3) Копируем диапазон
4) Вставляем в Word, выбирая опцию вставки "Связать с excel"

Теперь, при добавлении строк внутри диапазона именованный диапазон автоматически расширится, и в Word-е окажутся уже не 9 строк, а, к примеру, 11.
Так надо было?

[Foxx]

Немножно не то, что я хотел. Нужно было сразу залить архив. Вот здесь первый вариант Proga и Otchet. И 2ой вариант Proga 2 и Otchet 2. Увидим, что в Word совсем другие числа, все сместилось. Ссылки именно на ячейки, а не на диапазон. Так как при формировании отчета, в основном идут формулы, в которых значения будут браться из Excel. Проще для расчетов различных значений по различным исходным данным.

[Foxx]

Вот кажется так получается, проверил. Если каждую ячейку на которую будет ссылка в Worde назвать как диапазон. То при добавлении строк в Excel все остается как нужно. Теперь другой вопрос возникает, как сделать так, чтобы Excel автоматический уже задавал имена ячейкам как диапазон. Иначе будет очень долго и неразмуно вбивать на ячейку в Excele имя (диапазно левее строки формул).

[Foxx]

Файл Proga 3 и Otchet 3 работают не стабильно. Видимо, потому что ссылки оставались от прошлых. Сделал новые файл. Можно проверить добавляя строки в Excel и параллельно меняя значения. Работает ведь?
Поправка: выдает ошибка связи, и не меняются значения.

[viter.alex]

Да, забыл я про имена диапазонов.
Ошибку связи может давать, если файл с таблицей переместился. Чтобы задать новое расположение файла таблицы, нужно зайти в меню Office→Подготовить→Изменить ссылки на файлы
Выделить все ссылки и нажать "Сменить источник…". Выбрать нужный файл с таблицей для каждой ссылки. Они обновятся. При этом указанный диапазон не изменится

[Foxx]

А как сделать, чтобы не задавать диапазон для каждой ячейки вручную. Чтобы автоматом Excel сделал (ведь не важно его имя, факт что при добавлении строк в Excel таким способом все ссылки сохраняются. имена были бы как перебор букв по всем ячейкам) Ведь 250 строк по 7 столбцов переменных в Excel и каждой задавать диапазон вручную тяжко. И еще вопрос про смену источника: вот у меня около 150 ссылок, ведь там не возможно сразу сменить источник для всех ссылок (каждый раз открывается окно для каждой ссылки с выбором источника), здесь можно как нибудь сделать тоже, чтобы сразу для всех ссылок изменился источник.

[viter.alex]

Я писал такой макрос для смены множества ссылок буквально неделю назад:

Sub ChangeLinks()
  Dim oFld As Field 'Поле
  Dim OldFileName As String 'Старое имя файла
  Dim NewFileName As String 'Новое имя файла
  Dim FieldCode As String 'Код поля
  Dim ReplaceAllPath As Boolean 'Заменять весь путь к файлу или только имя
  Dim StartPath As Integer, EndPath As Integer 'Начало и конец пути к файлу в коде поля
  
  'Ввод старого имени файла
  OldFileName = InputBox("Укажите старое имя файла с расширением в ссылке, которое нужно изменить", "Изменение ссылок", OldFileName)
  If Len(OldFileName) = 0 Then Exit Sub
  
  'Выбор нового файла
  With Application.FileDialog(msoFileDialogFilePicker)
    .Title = "Выберите новый файл, с которым должен быть связан документ"
    .AllowMultiSelect = False
    .ButtonName = "Выбрать"
    .Filters.Clear
    .Filters.Add "Таблицы Excel", "*.xls; *.xlsx"
    If .Show Then NewFileName = .SelectedItems(1) Else Exit Sub
  End With
  
  'Если изменилось не только имя, но и местоположение, то можно заменить весь путь
  ReplaceAllPath = MsgBox("Заменять весь путь? Нажмите ""Нет"", чтобы заменить только имя файла", vbYesNo + vbInformation, "Изменение ссылок") = vbYes
  
  NewFileName = Replace(NewFileName, "\", "\\")
  'Перебираем все поля в документе
  For Each oFld In ActiveDocument.Fields
    If oFld.Type = wdFieldLink Then 'Если поле является полем ссылки
      FieldCode = oFld.Code.Text
      If InStr(oFld.Code.Text, "Excel.Sheet") <> 0 And InStr(FieldCode, "\\" & OldFileName) <> 0 Then 'Если поле ссылается на лист Excel и на нужный файл
        If ReplaceAllPath Then 'Если нужно заменить весь путь
          StartPath = InStr(FieldCode, ":\\") - 2
          EndPath = InStr(FieldCode, "\\" & OldFileName) + Len(OldFileName) + 2
          FieldCode = Mid(FieldCode, 1, StartPath) & NewFileName & Mid(FieldCode, EndPath)
        Else 'Если нужно заменить только имя файла
          FieldCode = Replace(FieldCode, OldFileName, Mid(NewFileName, InStrRev(NewFileName, "\") + 1))
        End If
      End If
    End If
    oFld.Code.Text = FieldCode
  Next
End Sub

Думаю, что имена диапазонов тоже можно задать программно, но это пусть спецы по Excel думают