Кавараный Excel!!! Глючит макрос сбора данных.

[mephist]

ОК, спасибо большое

[mephist]

Можно уточняющий вопрос:
Можно ли копировать диапазоны???
Пример(который не работает):

a = Range("asLiners4(li), 3:asLiners4(li), 11").Address: Cells("asLiners4(li), 3:asLiners4(li), 11") = GetValue(p, f, s, a)

[valerij]

Сообщение от mephist

Можно ли копировать диапазоны???

Я смотрю как у тебя и применяю к себе, копировать диапазоны то же надо, так, на ск. правильно, ?, но все ок!

For nn = 3 To 35
        ThisWorkbook.Worksheets(1).Cells(nn, 2) = .Worksheets(3).Cells(nn, 2)
Next

[mephist]

Написал макрос методом SAS888. Все супер, все работает,только невероятно долго. Файл собирается 15 минут. Когда он загружался с открытием книг, он работал 10 секунд на тот же обьем данных.
Помогите пожалуйста разобраться в чем дело!!!
Если честно я так и не понимаю

Сообщение от SAS888

Excel будет в "легком замешательстве"

Ведь в коде, написанном мною в самом начале, нет никаких указаний на конкретные файлы. Только указания на имена файлов из текущей папки.
Так почему же при втором запуске он отказывается работать с другими файлами, имеющими такие же имена???Если Excel где-то создает связи с какими-то файлами после первого запуска макроса,то нельзя ли их очитстить???
Получившийся код:

Sub Ìàêðîñ2()
    Dim Sh As Worksheet, li As Long, asFileNames, asColumns1, asLiners4, asColumns8, asColumns10a, asColumns10b
    Dim p As String, f As String, s As String, a As String
    Application.Calculation = xlCalculationManual: Application.ScreenUpdating = False: On Error Resume Next
    asFileNames = Array("hq.xlsx", "sz.xlsx", "sd.xlsx", "mk.xlsx", "mp.xlsx", "mc.xlsx", "ug.xlsx", "mn.xlsx", "mh.xlsx")
    asColumns1 = Array(6, 5, 7, 8, 9, 10, 11, 12, 13)
    asLiners4 = Array(13, 11, 12, 16, 18, 14, 19, 15, 17)
    asColumns8 = Array(7, 12, 17, 22, 27, 32, 37, 42, 47)
    asColumns10a = Array(1, 6, 10, 18, 22, 14, 26, 30, 34)
    asColumns10b = Array(5, 4, 6, 7, 8, 9, 10, 11, 12)
    For Each Sh In Sheets
        Sh.Unprotect
    Next
    p = ThisWorkbook.path & Application.PathSeparator
    For li = LBound(asFileNames) To UBound(asFileNames)
        f = asFileNames(li): ThisWorkbook.Sheets("OC").Activate: s = "OC"
        a = Cells(9, asColumns1(li)).Address: Cells(9, asColumns1(li)) = GetValue(p, f, s, a)
(Далее несколько десятков строк аналогичных последней)
    Next li
    Application.Calculation = xlCalculationAutomatic
End Sub

Function GetValue(path, file, sheet, ref)
    Dim arg As String
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & Range(ref).Range("A1").Address(, , xlR1C1)
    GetValue = ExecuteExcel4Macro(arg)
End Function

[mephist]

Код имеющийся у меня до сегодняшнего дня.

Сообщение от mephist

Sub blank_fill_8()
    Application.Calculation = xlCalculationManual: On Error Resume Next
    Dim Sh As Worksheet
    For Each Sh In Sheets
        Sh.Unprotect
    Next
    Application.ScreenUpdating = False
    Filename = Replace(ThisWorkbook.FullName, ThisWorkbook.Name, "sz.xlsx")
    With Workbooks.Open(Filename, , True)
        .Worksheets(1).Unprotect
        .Worksheets(1).Range("E9").Copy ThisWorkbook.Worksheets(1).[E9]
        .Worksheets(1).Range("E12:E13").Copy ThisWorkbook.Worksheets(1).[E12]
(десятки аналогичных строк)
       .Close False
    End With
    Application.ScreenUpdating = False
    Filename = Replace(ThisWorkbook.FullName, ThisWorkbook.Name, "hq.xlsx")
    With Workbooks.Open(Filename, , True)
    .Worksheets(1).Range("F9").Copy ThisWorkbook.Worksheets(1).[F9]
    ...
    End With
    Application.Calculation = xlCalculationAutomatic
End Sub

Работает моментально, но зато работает только с теми фалами из текущей папки которые были при первом запуске. Соответсвенно, цели тоже не достигает.

[mephist]

А как можно перейти к ссылкам на ячейки? Очень нужно.

[SAS888]

Работает моментально, но зато работает только с теми фалами из текущей папки которые были при первом запуске. Соответсвенно, цели тоже не достигает.

По-моему, мы это уже обсуждали. Давайте разберемся.
Если непосредственно в ячейках листа главного файла формулами задать связи с другими файлами, то все работает. Так? Теперь, перемещаем этот главный файл в другую папку, в которой есть все имена файлов, на которые указывают ссылки. Так? Теперь, при открытии главного файла, Excel спросит, обновлять связи, или нет. Нажав кнопку "Обновить", все связи станут указывать на файлы в этой папке. Если же какого-то файла нет, Excel предложит указать к нему путь (это я и назвал "легким замешательством". Но этого, как я понимаю, не потребуется).
Почему Вас не устраивает такой вариант?

[mephist]

Хм. Если бы Эксель предлагал обновить связи все было бы замечательно. Но все совсем не так.
Если запустить макрос с кодом открывающим книги, размещенным в начале темы (последовательность событий): главный файл в папке с исходниками - запускаем макрос - все данные собираются за десять секунд - переносим файл в другую папку(со всеми файлами исходниками) - запускаем макрос - открывается(именно открывается) файл прописанный в макросе первым - Excel зависает!!! Аналогичная ситуация при смене файла исходника (но с тем же именем) только зависание происходит при отрытии замененного файла.
Меняю в этом коде

.Worksheets(1).Range("E9").Copy ThisWorkbook.Worksheets(1).[E9]

на стороку

ThisWorkbook.Worksheets(1).Range("E9").Formula = "=" & .Worksheets(1).Range("E9").Address(, , , True)

Все также.
Меняю на строку

.Sheets("ОС").Range("E12:E13").Copy: ThisWorkbook.Sheets("ОС").Range(E12).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

все идет по тому же сценарию.
Если создать новый файл, новый макрос(с тем же кодом) с новыми файлами-исходниками (но с теми же именами), то теперь Excel зависает при первом же запуске.
Мне уже просто самому интересно: ГДЕ Я ТУПЛЮ??? Почему этот код раньше работал, а теперь нет? Теперь у меня этот код совсем не работает. Никак.

Код написанный по предложению SAS888
реально работает не смотря ни на что (то что нужно). Но он во-первых занимает 15 минут. Во-вторых я не могу заполнить диапазоны , то есть Е15:К48. В-третьих я не знаю как перейти к ссылкам.

В конечном счете цель макроса: в главном файле собираются ссылки (минимум значения),кое - где нужны копии диапазонов (желательно с форматированием,хотя могу перебиться), которые можно заполнять из любой текущей папки сколько угодно раз. Мне бы теперь хотя бы добиться части этой цели.

Я ОЧЕНЬ ХОЧУ РАЗОБРАТЬСЯ И ПОНЯТЬ, ЧТО К ЧЕМУ! Кому не лень, пишите свои мысли.

[SAS888]

Вы меня не правильно поняли. Макрос здесь совсем не нужен. Попробуйте вставить, например, в ячейку "E9" главного файла формулу:

='D:\[sz.xlsx]Лист1'!$E$9

Естественно, путь, имя файла и имя листа подставьте те, которые требуются. После этого, при открытии главного файла (пусть, например, файл "sz.xlsx" не открыт), Excel спросит, нужно ли обновлять связь. Если ответить "Не обновлять", то в ячейке "E9" останется то значение, которое имелось при открытии. Если же ответить "Обновить", то Excel возьмет данные из файла по указанному пути.
Сохраним главный файл и переместим его в папку, в которой также имеется файл "sz.xlsx", но с другими данными. Открываем главный файл, отвечаем "Обновить", и Excel возьмет данные из файла в этой папке (естественно, если такой файл в ней есть).
Т.е. достаточно один раз прописать в требуемой ячейке главного файла связь с ячейкой другого файла, и, каждый раз, при открытии файла и ответе "Обновить" (кстати, в настройках Excel можно указать, спрашивать об этом или нет, обновлять по умолчанию, либо не обновлять по умолчанию), данные будут браться из файла, расположенного в той же папке, где находится главный файл.

[mephist]

Согласен. Я это уже осмыслил еще когда мы это обсуждали в теме:http://www.programmersforum.ru/showthread.php?t=57241 Ссылка на файл из текущей папки! Сбор инфы в один файл!
Но для меня это самое последнее дело. Файлы действительно очень сложные. Прописывать так ссылки в каждой ячейке это во-первых самоубийство,а во-вторых одноразово. Помимо моих наставленных ссылок, будет еще миллион других ссылок. Часть этих чужих ссылок понадобиться в следующем месяце, часть - нет и будет мешаться. Работать с файлом будут другие люди и они могут просто напутать ссылки, или могут не справиться с обновлением связей.
Одним словом нужна неизменная форма прописанная в макросе. Которую никто не сможет испортить, и которая стабильно будет заполнять мой файлик данными из любых директорий, любого компа.
Мне кажется это должно быть актуально для многих.
Какая-то хитрость есть в установлении связей в Excel. Сейчас попробую набросать пример.