[решено] Внешние связи Excel 2007

[Vison]

Возникла следующая проблема:
Существует основной фаил "1" Excel (в виде календаря) и ежедневно формирующийся фаил "2" (в виде цифровой таблицы), задача сделать связь, обновляющую данные в календаре из ежедневных фаилов "2", соответствующих конкретному дню месяца. Сложность заключается в том что при обновлении связей, например при закрытии фаила "1" или открытии фаила "2", данные по предыдущим дням обнуляются.

Вопрос: Есть ли способ задать условие, при выполнении которого будет обновляться конкретная группа ячеек ? или может другой хитрый способ

Существующая связь построена с использованием:
=СЦЕПИТЬ(ссылка на фаил "2")
=ЕСЛИОШИБКА(ДВССЫЛ(А1);0)

[okshef]

Vison, приведите часть 2-х файлов.

А вообще почитайте: Как получить данные из закрытой книги? (http://www.excel-vba.ru/chto-umeet-excel/kak-poluchit-dannye-iz-zakrytoj-knigi/)

[Vison]

Вот кусок файла "1" при одном открытом файле "2", как видно на скрине данные из сегодняшнего файла "обнулены". пока у меня мысли работают в направлении переместить данные из КВ1 в КВ3 при значении КВ1 не равном нулю, но в тоже время так что бы при обнулении КВ1 данные в КВ3 сохранялись, в таком случае не придется постоянно удерживать связь с по сути однодневными файлами

[okshef]

В любом случае вам нужно либо вручную, либо макросом разрывать связь и сохранять только значения, а не формулы.

[Vison]

Вобщем в конечном итоге, на скорую руку, получилось сделать следующим макросом:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.ScreenUpdating = False
Sheets("Календарь").Select
If Range("V27") = 0 Then GoTo Lastline
Columns("V:Z").Select
Selection.Copy
Columns("AJ:AN").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Lastline:
Application.ScreenUpdating = True
End Sub

[okshef]

Зачем выделять?
Columns("V:Z").Copy
Columns("AJ:AN").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

[Vison]

не знаю, я только вчера узнал что такое VBA, делал при помощи "Запись макроса" потом корректировал

спасибо за поправку : )

[okshef]

я только вчера »
не похоже :). Макрорекодер не записывает команду
Application.ScreenUpdating
Еще одно замечание. Для сокращения кода параметры, которые и так установлены по-умолчанию, вписывать не обязательно. Я вот о чем:
.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
можно заменить на
.PasteSpecial Paste:=xlPasteValuesAndNumberFormats

[Vison]

не похоже . Макрорекодер не записывает команду »

да это я нагуглил, ну и школьная программа qBasic : )))

вобщем вот чего в итоге получилось:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.ScreenUpdating = False
Sheets("Календарь").Select
Dim X1, X2, Y1, Y2

X1 = "V"
X2 = "Z"
X11 = "AJ"
X21 = "AN"
Y1 = 27
Y2 = 33

110: If Range(X1 & Y1) = "x" Then GoTo 120
Range(X1 & Y1 & ":" & X2 & Y2).Copy
Range(X11 & Y1 & ":" & X21 & Y2).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
120: If Y1 >= 523 Then GoTo 130
Y1 = Y1 + 16
Y2 = Y2 + 16
GoTo 110
130: Range("A1").Select
Application.ScreenUpdating = True
End Sub

[okshef]

Vison, я, конечно, не корифей VBA, но не понимаю, зачем использовать переменные вместо констант. Как вам такой код:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.ScreenUpdating = False
With Sheets("Календарь")
For irow = 27 To 523 Step 16
If .Range("V" & irow) <> "x" Then
.Range("V" & irow).Resize(7, 5).Copy
.Range("AJ" & irow).PasteSpecial (xlPasteValuesAndNumberFormats)
End If
Next
.Range("A1").Select
End With
Application.ScreenUpdating = True
End Sub
P.S. В следующий раз для выделения кода используйте тэг "Код" - значок # на панели.

[Vison]

Да, так наверное эффективнее, завтра попробую на практике
по поводу .Range("A1").Select я это сделал для снятия выделения со скрытых ячеек (ну в смысле при срабатывании макроса ячейки остаются выделенными), может это не нужная функция ?

к стати столкнулся со следующей проблемой: когда применил подобную схему к каждому дню календаря - то получилось достаточно много вычислений. думаю решить это с помощью функции "=ВПР", соответственно придется вносить изменения в структуру файла "2", пока что сделал так что остальные ссылки массива производят вычисления только если первая имеет положительный результат