Форум программистов
 

Восстановите пароль или Зарегистрируйтесь на форуме, о проблемах и с заказом рекламы пишите сюда - alarforum@yandex.ru, проверяйте папку спам!

Вернуться   Форум программистов > Microsoft Office и VBA программирование > Microsoft Office Excel
Регистрация

Восстановить пароль
Повторная активизация e-mail

Купить рекламу на форуме - 42 тыс руб за месяц

Ответ
 
Опции темы Поиск в этой теме
Старый 05.08.2009, 22:53   #1
Varen1k
 
Регистрация: 05.08.2009
Сообщений: 5
По умолчанию Импорт значений из внешних книг Excel по списку файлов

Люди, помогите.
Есть задачка.

В таблице, назову её сводной, существует список с именами файлов, которые все лежат на компе в одном месте и имеют одинаковую структуру. Так что можно сказать есть список ссылок на ячейки во внешних файлах.

Задача подтягивать в сводную таблицу данные из определенных полей каждого из файлов.

Понятно что можно каждый открывать и делать из сводной таблицы внешнюю ссылку на заданную ячейку. И так с каждым из файлов, вот только файлов много и полей в них много.

Задача осложняется что список файлов динамически меняется, так что процесс это бесконечный.

Хочется как-то автоматизировать процесс. Пробовал формулами, например "ДВССЫЛ", но работает только с открытыми книгами.

Что посоветуете, учить VBA, или как-то можно заставить её работать по внешним ссылкам штатными средствами ?
Varen1k вне форума Ответить с цитированием
Старый 06.08.2009, 05:35   #2
SAS888
Старожил
 
Аватар для SAS888
 
Регистрация: 05.12.2007
Сообщений: 4,180
По умолчанию

Получить значения из файла, не открывая его, можно только в том случае, если известны не только путь и имя файла, но также в явном виде имя листа и диапазон ячеек.
Если все это известно, приведите пример, из каких ячеек листа брать имена файлов, из каких листов (не номера, а имена), и из какого диапазона получать значения, делать с ними что-нибудь или нет, и куда вставлять полученные данные. Также, поясните, каким образом нужно указывать путь к папке с файлами-источниками.
Чем шире угол зрения, тем он тупее.
SAS888 вне форума Ответить с цитированием
Старый 06.08.2009, 20:07   #3
Varen1k
 
Регистрация: 05.08.2009
Сообщений: 5
По умолчанию

SAS888,
все из написанного известно, и пусть, пусть это будет "с:\1\" и имя листа во всех файлах одно, пусть это будет "Лист1", и значение надо брать из одной и той-же ячейки, пусть это будет A1. Пусть в сводной таблице в столбце А подряд идут имена файлов, например A1=1, A2=2 и т.д. При этом файлы соответственно 1.xlsx, 2.xlsx и т.д. все находятся в "с:\1\". При этом данные из ячейки А1 Листа "Лист1" файла-источника должны вставляться в ячейку Bn (где n собственно строка где находится название соответствующего файла) сводного файла.

На первый взгляд показалось логичным сформировать ссылку на внешнюю книгу функцией "сцепить".

То есть в ячейку B1 я ввел такую формулу
=СЦЕПИТЬ("='c:\1\[";A1;".xlsx]Лист1'!$A$1")
, но она вполне логично мне возвращает значение
='c:\1\[1.xlsx]Лист1'!$A$1.
Но мне то нужно не значение выводить а по сути формулу, которую после этого еще выполнить надо. То есть значение что мне дала функция СЦЕПИТЬ надо после этого понять как формулу и её выполнить.

Последний раз редактировалось Varen1k; 06.08.2009 в 20:17.
Varen1k вне форума Ответить с цитированием
Старый 07.08.2009, 06:34   #4
SAS888
Старожил
 
Аватар для SAS888
 
Регистрация: 05.12.2007
Сообщений: 4,180
По умолчанию

Могу предложить решение с помощью VBA. Вставьте в модуль нужного листа код:
Код:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Columns.Count > 1 Then Exit Sub
    If Target.Column <> 1 Then Exit Sub
    Dim Cell As Range
    For Each Cell In Target
        If Cell = "" Then Cell.Next.ClearContents Else Cell.Next.Formula = "='C:\1\[" & Cell & ".xlsx]Лист1'!$A$1"
    Next
End Sub
После этого, при каждом изменении значения в столбце "A", в этой же строке столбца "B" будет создана формула со ссылкой на требуемый файл. При удалении значения из ячейки столбца "A", формула в столбце "B" также удаляется. При вводе несуществующего имени файла, будет выведено окно с предложением указать путь к этому файлу. Допускается работа с диапазоном ячеек столбца "A".

ПРИМЕЧАНИЕ: Если к моменту вставки кода в программный модуль листа в столбце "A" уже имелись значения, то для этих ячеек ссылки созданы не будут. Для того, чтобы их получить, скопируйте и снова вставьте требуемые ячейки столбца "A" (т.е. требуется обновление значений).
Чем шире угол зрения, тем он тупее.
SAS888 вне форума Ответить с цитированием
Старый 07.08.2009, 08:49   #5
smith45
Пользователь
 
Регистрация: 03.08.2009
Сообщений: 10
По умолчанию

А если использовать такую функцию:

Private Function gv(p, f, s, r1, r2)
Dim arg As String
If Right(p, 1) <> "\" Then p = p & "\"
If Dir(p & f) = "" Then
gv = "файл не найден"
Exit Function
End If
arg = "'" & p & "[" & f & "]" & s & "'!" & Cells(r1, r2).Range("a1").Address(, , xlR1C1)
gv = ExecuteExcel4Macro(arg)
End Function

а ссылка к этой функции выглядит так:
Аctivesheet.Cells(x, y) = gv(path, file, list, x1, y1)

где path - путь к папке, в таком виде "c:\папка\"
file - имя файла, в таком виде "имя файла.xls"
list - имя листа - "имя листа"
x, y - строка, столбец куда разместить
x1,y1 - строка, столбец запрашиваемой информации

список файлов задаётся предварительно, потом полный путь разбивается на папку и имя файла. и вперёд =)

при желании могу выложить файлик с примером работы такого файла (делал подобное пару лет назад)

Последний раз редактировалось smith45; 07.08.2009 в 08:51.
smith45 вне форума Ответить с цитированием
Старый 07.08.2009, 08:59   #6
SAS888
Старожил
 
Аватар для SAS888
 
Регистрация: 05.12.2007
Сообщений: 4,180
По умолчанию

Цитата:
А если использовать такую функцию
Дело в том, что данную функцию можно использовать только из макроса. В ячейке рабочего листа она работать не будет. И еще. Как мне показалось, автор пытается получить не просто значение, а именно формулу с требуемой ссылкой. Это оправдано. Так, например, если в тот момент, когда открыта книга с макросом, открыть и изменить значения в книге-источнике данных, то формула сработает сразу, а макрос об этом событии знать не будет.
Чем шире угол зрения, тем он тупее.
SAS888 вне форума Ответить с цитированием
Старый 07.08.2009, 10:03   #7
smith45
Пользователь
 
Регистрация: 03.08.2009
Сообщений: 10
По умолчанию

Цитата:
Сообщение от Varen1k Посмотреть сообщение
.......
Пробовал формулами, например "ДВССЫЛ", но работает только с открытыми книгами.

Что посоветуете, учить VBA, или как-то можно заставить её работать по внешним ссылкам штатными средствами ?
а я понял что нужно собирать сводную информацию из списка файлов...

автору...
полюбому лучше учить vba. "пригодится" (с)
smith45 вне форума Ответить с цитированием
Старый 07.08.2009, 10:42   #8
SAS888
Старожил
 
Аватар для SAS888
 
Регистрация: 05.12.2007
Сообщений: 4,180
По умолчанию

Цитата:
а я понял что нужно собирать сводную информацию из списка файлов
Мы долго можем гадать, что же все-таки нужно автору, пока он сам не появится. Но если по-Вашему, и если использовать макрос XLM, то можно и проще. Например, если в столбце "A" имеется список файлов, то требуемые значения в столбце "B" можно получить, выполнив макрос:
Код:
Sub Main()
    Dim i As Long: Application.ScreenUpdating = False
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        If Cells(i, 1) <> "" Then Cells(i, 1).Next = ExecuteExcel4Macro("'C:\1\[" & Cells(i, 1) & ".xls]Лист1'!" & Range("A1").Address(, , xlR1C1))
    Next
End Sub
P.S.
Цитата:
полюбому лучше учить vba. "пригодится"
+1 !!!
Чем шире угол зрения, тем он тупее.
SAS888 вне форума Ответить с цитированием
Старый 07.08.2009, 13:05   #9
Varen1k
 
Регистрация: 05.08.2009
Сообщений: 5
По умолчанию

Smyth, SAS, спасибо вам.

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

Только остается вопрос: насколько это будет грузить комп? Т.к. файлов-источников может быть несколько сотен и из каждого берется с десяток данны.

Значит получается штатными средствами или формулами Excel нельзя сделать так, чтобы он значение, вычисляемое одной формулой, понимал как формулу а не как значение ячейки ?
Varen1k вне форума Ответить с цитированием
Старый 25.04.2012, 11:59   #10
ratibor
Пользователь
 
Регистрация: 01.12.2011
Сообщений: 42
По умолчанию

Цитата:
Сообщение от SAS888 Посмотреть сообщение
Мы долго можем гадать, что же все-таки нужно автору, пока он сам не появится. Но если по-Вашему, и если использовать макрос XLM, то можно и проще. Например, если в столбце "A" имеется список файлов, то требуемые значения в столбце "B" можно получить, выполнив макрос:
Код:
Sub Main()
    Dim i As Long: Application.ScreenUpdating = False
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        If Cells(i, 1) <> "" Then Cells(i, 1).Next = ExecuteExcel4Macro("'C:\1\[" & Cells(i, 1) & ".xls]Лист1'!" & Range("A1").Address(, , xlR1C1))
    Next
End Sub
P.S. +1 !!!
Уважаемые, подскажите как адаптировать этот код к моему примеру.
Данные лежат в папках по годам, в каждой папке "года" еще три папки с пронумерованными файлами. Вычисления реализованы через использование функции "ДВССЫЛ", естественно данные обновляются только после открытия файлов. Хотелось бы, чтобы данные обновлялись при закрытых файлах. Пример во вложении.СводФЭ_разр.rar
ratibor вне форума Ответить с цитированием
Ответ


Купить рекламу на форуме - 42 тыс руб за месяц

Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск


Похожие темы
Тема Автор Раздел Ответов Последнее сообщение
Обработка в Excel внешних событий pan Microsoft Office Excel 4 22.02.2009 17:26
Импорт внешних данных, если данные в архиве Zip Vic65 Microsoft Office Excel 5 29.07.2008 14:10
Импорт внешних данных, синхронизация по времени slba Microsoft Office Excel 3 15.07.2008 13:05
Компиляция внешних файлов NoRty Паскаль, Turbo Pascal, PascalABC.NET 2 25.02.2008 15:34
Импорт внешних данных asale Microsoft Office Excel 1 15.04.2007 00:41