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

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

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

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

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

Ответ
 
Опции темы Поиск в этой теме
Старый 10.07.2014, 14:41   #1
MaxxVer
Форумчанин
 
Регистрация: 17.03.2009
Сообщений: 226
По умолчанию Получение имён листов из закрытых книг

Добрый день уважаемые!

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

Код:
With TextBox3
TextToFind = "Текст"
iPath = "C:\Documents and Settings\"
file = Dir(iPath & "*.xlsb")
    Do While file <> ""
        a = "C4"
        For i = 1 To 5 ' ?
            ws = Sheets(i) ' ?
            If GetValue(iPath, file, ws, a) = TextToFind Then
                .Text = file
            End If
        Next ' ?
    Loop
End With

Код:
Private Function GetValue(path, file, sheet, ref)
Dim arg As String
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "-"
Exit Function
End If
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)
GetValue = ExecuteExcel4Macro(arg)
End Function
MaxxVer вне форума Ответить с цитированием
Старый 10.07.2014, 14:44   #2
MaxxVer
Форумчанин
 
Регистрация: 17.03.2009
Сообщений: 226
По умолчанию

Названия листов в закрытых книгах конечно не подчинены какой-либо логике (всегда разные), а перебрать надо все... Поэтому то что я написал выше не работает..(
MaxxVer вне форума Ответить с цитированием
Старый 10.07.2014, 15:51   #3
Hugo121
Старожил
 
Регистрация: 11.05.2010
Сообщений: 5,166
По умолчанию

Открывайте в цикле файлы, цикл по листам (рабочим!), смотрите ячейку.
Если делать с getobject() - будет незаметно и быстро. Но иногда оно не работает, нужно проверять на файлах.
webmoney: E265281470651 Z422237915069 R418926282008
Hugo121 вне форума Ответить с цитированием
Старый 10.07.2014, 16:01   #4
MaxxVer
Форумчанин
 
Регистрация: 17.03.2009
Сообщений: 226
По умолчанию

Спасибо! С getobject еще не имел дело, сейчас поищу, попробую...
MaxxVer вне форума Ответить с цитированием
Старый 10.07.2014, 16:03   #5
IgorGO
Новичок
СтарожилДжуниор
 
Аватар для IgorGO
 
Регистрация: 05.02.2008
Сообщений: 9,487
По умолчанию

а Вы полагаете, что ExecuteExcel4Macro не открывает книгу, а забирает данные из книги как-то по другому?

если уверены - можете продолжать эксперименты, а нет - откройте книги по очереди и переберите содержимое С4 всех листов.
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете
IgorGO вне форума Ответить с цитированием
Старый 10.07.2014, 16:16   #6
MaxxVer
Форумчанин
 
Регистрация: 17.03.2009
Сообщений: 226
По умолчанию

Я не гуру в VBA, мягко говоря, поэтому уверенности нет ни в чем..)) считал что ExecuteExcel4Macro не открывает книги.. Это не так? Если это не так, то конечно попытаюсь перебрать книги и листы...
MaxxVer вне форума Ответить с цитированием
Старый 11.07.2014, 05:46   #7
SAS888
Старожил
 
Аватар для SAS888
 
Регистрация: 05.12.2007
Сообщений: 4,180
По умолчанию

Любой способ получения значений из ячеек рабочего листа закрытой книги будет открывать файл (явно, или нет). Очень жаль, что разработчики Excel "выбросили" метод "FileSearch", который мог "в лоб" решить Вашу задачу, т. е. определить файл, содержащий искомое значение. Приходится решать по-другому. Например, так:
Код:
Sub qq()
    Dim wb As Workbook, ws As Worksheet, p As String, f As String, Item As String
    Item = "текст" 'искомое значение
    p = "E:\Temp\" 'путь к папке с файлами
    f = Dir(p & "*.xls*")
    Do While f <> ""
        Set wb = GetObject(p & f)
        For Each ws In wb.Sheets
            If ws.[C4] = Item Then MsgBox wb.Name
        Next
        wb.Close: f = Dir
    Loop
End Sub
Естественно, что можно определять не только полное, но и частичное совпадение значений (используя, например, InStr).
Что делать при обнаружении совпадений, автору лучше знать. Макрос просто выведет сообщение с именем искомой книги.
Чем шире угол зрения, тем он тупее.
SAS888 вне форума Ответить с цитированием
Старый 11.07.2014, 09:22   #8
MaxxVer
Форумчанин
 
Регистрация: 17.03.2009
Сообщений: 226
По умолчанию

Спасибо, SAS888! Реально жаль что в эксель невозможно сделать такое, вроде бы, простое действие без открытия книги... У меня книги очень "тяжелые", на их открытие тратится уйма времени. Да еще сообщение об обновлении связей выскакивает почему-то... Хотя я уже все что можно отключил (ScreenUpdating, DisplayAlerts, EnableEvents). Почему так, не подскажете?
MaxxVer вне форума Ответить с цитированием
Старый 11.07.2014, 14:02   #9
SAS888
Старожил
 
Аватар для SAS888
 
Регистрация: 05.12.2007
Сообщений: 4,180
По умолчанию

Цитата:
Да еще сообщение об обновлении связей выскакивает почему-то... Хотя я уже все что можно отключил (ScreenUpdating, DisplayAlerts, EnableEvents). Почему так, не подскажете?
В настройках файла установите "не обновлять связи".
Чем шире угол зрения, тем он тупее.
SAS888 вне форума Ответить с цитированием
Старый 11.07.2014, 15:12   #10
MaxxVer
Форумчанин
 
Регистрация: 17.03.2009
Сообщений: 226
По умолчанию

Кому интересно - удалось таки решить задачу как хотел. Имена листов можно запросить не открывая книги (через ADODB), потом методом ExecuteExcel4Macro вытащить нужные данные так же не открывая книг. Работает быстро, на 6 файлов (в каждом более 30 листов), тратит не больше 2 секунд.

Выкладываю код, он правда под мои цели заточен, но принцип кто захочет поймёт..
Код:
    Dim sPath As String, sFile As String, sShName As String
    Dim sAddress As String, vData
    Dim fname As String
    Dim tn As String
    
    Dim Cnn As ADODB.Connection
    Dim rS As ADODB.Recordset

    sPath = TextBox247
    sFile = Dir(sPath & "*.xls")
    
    Do While sFile <> ""
        fname = sPath & sFile
        Set Cnn = New ADODB.Connection
        Set rS = New ADODB.Recordset
        Cnn.Open "Driver={Microsoft Excel Driver (*.xls)};DBQ=" & fname & ";ReadOnly=1"
        Set rS = Cnn.OpenSchema(adSchemaTables)
        Do While Not rS.EOF
            tn = rS("TABLE_NAME")
            If Left(tn, 1) = "'" Then tn = Mid(tn, 2)
            
            If Len(tn) < InStr(1, tn, "$") + 2 Then
                tn = Left(tn, InStr(1, tn, "$") - 1)
                sShName = tn
                sAddress = "'" & sPath & "[" & sFile & "]" & sShName & "'!" & Range("C4").Address(ReferenceStyle:=xlR1C1)
                vData = ExecuteExcel4Macro(sAddress)
                If vData = TextBox201.value Then
                    TextBox3.Text = vData
                End If
                If vData = TextBox205.value Then
                    TextBox10.Text = vData
                End If
                If vData = TextBox203.value Then
                    TextBox5.Text = vData
                End If
                If vData = TextBox204.value Then
                    TextBox9.Text = vData
                End If
                If vData = TextBox207.value Then
                    TextBox15.Text = vData
                End If
                If vData = TextBox208.value Then
                    TextBox16.Text = vData
                End If
            End If
            rS.MoveNext
        Loop
        rS.Close
        sFile = Dir
    Loop
MaxxVer вне форума Ответить с цитированием
Ответ


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

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

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


Похожие темы
Тема Автор Раздел Ответов Последнее сообщение
Обьединение листов из закрытых книг Extril Microsoft Office Excel 31 28.11.2013 12:55
Получение имён, отправка ID. Как правильно обработать? Jopses JavaScript, Ajax 0 02.02.2013 02:54
Макрос аля ВПР для формирования свода из закрытых книг MaxxVer Microsoft Office Excel 15 28.08.2012 12:02
Получение данных из множества закрытых книг книг hardkain Microsoft Office Excel 1 27.09.2011 20:18
копирование листов из закрытых книг mephist Microsoft Office Excel 4 10.07.2009 17:18