Vba excel копировать из закрытой книги
Приветствую участников Форума!
Вопрос к макросоведам.
На одном из Excelевских сайтов (Excel - это не сложно) подсмотрел следующий код:
[vba]
Sub Get_Value_From_Close_Book_Formula()
Dim sPath As String, sFile As String, sShName As String
sPath = "C:\Documents and Settings\"
sFile = "Книга1.xls"
sShName = "Лист1"
With Range("A1:A100")
.Formula = "='" & sPath & "[" & sFile & "]" & sShName & "'!" & "A1:A100"
.Value = .Value
End With
End Sub
[/vba]
Копируется диапазон данных из закрытой книги. Путь к закрытому файлу прописан в коде.
Все здорово работает, спасибо авторам.
В то же время мне необходимо немого переделать под себя:
1. Данный вставляются в тоже самое место, где они и располагались в исходном файле, а мне надо, ну предположим, в C2:С101;
2. Мне нужно, чтобы после запуска кода вылетало окошко с запросом, из какого файла вставить данные. Все файлы "исходники" в одной папке, структура данных везде одинакова.
Заранее благодарен.
Приветствую участников Форума!
Вопрос к макросоведам.
На одном из Excelевских сайтов (Excel - это не сложно) подсмотрел следующий код:
[vba]
Sub Get_Value_From_Close_Book_Formula()
Dim sPath As String, sFile As String, sShName As String
sPath = "C:\Documents and Settings\"
sFile = "Книга1.xls"
sShName = "Лист1"
With Range("A1:A100")
.Formula = "='" & sPath & "[" & sFile & "]" & sShName & "'!" & "A1:A100"
.Value = .Value
End With
End Sub
[/vba]
Копируется диапазон данных из закрытой книги. Путь к закрытому файлу прописан в коде.
Все здорово работает, спасибо авторам.
В то же время мне необходимо немого переделать под себя:
1. Данный вставляются в тоже самое место, где они и располагались в исходном файле, а мне надо, ну предположим, в C2:С101;
2. Мне нужно, чтобы после запуска кода вылетало окошко с запросом, из какого файла вставить данные. Все файлы "исходники" в одной папке, структура данных везде одинакова.
Заранее благодарен. Мур
Sub Get_Value_From_Close_Book_Formula()
Dim sPath As String, sFile As String, sShName As String
sPath = "C:\Documents and Settings\"
sFile = "Книга1.xls"
sShName = "Лист1"
With Range("A1:A100")
.Formula = "='" & sPath & "[" & sFile & "]" & sShName & "'!" & "A1:A100"
.Value = .Value
End With
End Sub
[/vba]
Копируется диапазон данных из закрытой книги. Путь к закрытому файлу прописан в коде.
Все здорово работает, спасибо авторам.
В то же время мне необходимо немого переделать под себя:
1. Данный вставляются в тоже самое место, где они и располагались в исходном файле, а мне надо, ну предположим, в C2:С101;
2. Мне нужно, чтобы после запуска кода вылетало окошко с запросом, из какого файла вставить данные. Все файлы "исходники" в одной папке, структура данных везде одинакова.
Заранее благодарен. Автор - Мур
Дата добавления - 26.06.2011 в 17:35
Вот файлик. Положите его в одну папку с файлами-исходниками.
Жмем кнопку, в списке выбираем нужный файл - Ок (ну, или не Ок).
Вот файлик. Положите его в одну папку с файлами-исходниками.
Жмем кнопку, в списке выбираем нужный файл - Ок (ну, или не Ок). nilem
1. With Range("C2:С101")
2. Окошко сделать можно, но тогда ещё нужно окошко с указанием листа, или всегда с первого брать будете?
1. With Range("C2:С101")
2. Окошко сделать можно, но тогда ещё нужно окошко с указанием листа, или всегда с первого брать будете? Hugo
Игорь, так не получается.
Мур, в модуле формы вот так надо (поторопился):
[vba]
Private Sub CommandButton1_Click()
If f = "" Then MsgBox "Не выбран файл", 64: Exit Sub
Call Module1.GetValue(Fldr, f): Unload Me
End Sub
Игорь, так не получается.
Мур, в модуле формы вот так надо (поторопился):
[vba]
Private Sub CommandButton1_Click()
If f = "" Then MsgBox "Не выбран файл", 64: Exit Sub
Call Module1.GetValue(Fldr, f): Unload Me
End Sub
Игорь, так не получается.
Мур, в модуле формы вот так надо (поторопился):
[vba]
Private Sub CommandButton1_Click()
If f = "" Then MsgBox "Не выбран файл", 64: Exit Sub
Call Module1.GetValue(Fldr, f): Unload Me
End Sub
[/vba] понятно, спасибо!
А данные всегда берутся с одного и того же листа. Автор - Мур
Дата добавления - 26.06.2011 в 18:47
Sub Get_Value_From_Close_Book_Formula()
Dim sPath As String, sFile As String, sShName As String
sShName = "Лист1"
With Application.FileDialog(msoFileDialogFilePicker)
.Filters.Clear
.Filters.Add "Microsoft Excel files", "*.xls"
.AllowMultiSelect = False
.InitialFileName = ThisWorkbook.Path
If .Show = 0 Then Exit Sub
sep_ = Application.PathSeparator
sFile = Split(.SelectedItems(1), sep_)(UBound(Split(.SelectedItems(1), sep_))) ' "Книга1.xls"
sPath = Replace(.SelectedItems(1), sFile, "") '"C:\Documents and Settings\"
End With
With Range("C2:C101")
.Formula = "='" & sPath & "[" & sFile & "]" & sShName & "'!" & "A1:A100"
.Value = .Value
End With
End Sub
Application.FileDialog(msoFileDialogFilePicker) не работает под 2000 Экселем!
А With Range("C2:C101") в моём примере выше вероятно кириллицей набрано было, поэтому не работало :(((
Sub Get_Value_From_Close_Book_Formula()
Dim sPath As String, sFile As String, sShName As String
sShName = "Лист1"
With Application.FileDialog(msoFileDialogFilePicker)
.Filters.Clear
.Filters.Add "Microsoft Excel files", "*.xls"
.AllowMultiSelect = False
.InitialFileName = ThisWorkbook.Path
If .Show = 0 Then Exit Sub
sep_ = Application.PathSeparator
sFile = Split(.SelectedItems(1), sep_)(UBound(Split(.SelectedItems(1), sep_))) ' "Книга1.xls"
sPath = Replace(.SelectedItems(1), sFile, "") '"C:\Documents and Settings\"
End With
With Range("C2:C101")
.Formula = "='" & sPath & "[" & sFile & "]" & sShName & "'!" & "A1:A100"
.Value = .Value
End With
End Sub
Application.FileDialog(msoFileDialogFilePicker) не работает под 2000 Экселем!
А With Range("C2:C101") в моём примере выше вероятно кириллицей набрано было, поэтому не работало :((( Hugo
Sub Get_Value_From_Close_Book_Formula()
Dim sPath As String, sFile As String, sShName As String
sShName = "Лист1"
With Application.FileDialog(msoFileDialogFilePicker)
.Filters.Clear
.Filters.Add "Microsoft Excel files", "*.xls"
.AllowMultiSelect = False
.InitialFileName = ThisWorkbook.Path
If .Show = 0 Then Exit Sub
sep_ = Application.PathSeparator
sFile = Split(.SelectedItems(1), sep_)(UBound(Split(.SelectedItems(1), sep_))) ' "Книга1.xls"
sPath = Replace(.SelectedItems(1), sFile, "") '"C:\Documents and Settings\"
End With
With Range("C2:C101")
.Formula = "='" & sPath & "[" & sFile & "]" & sShName & "'!" & "A1:A100"
.Value = .Value
End With
End Sub
Application.FileDialog(msoFileDialogFilePicker) не работает под 2000 Экселем!
А With Range("C2:C101") в моём примере выше вероятно кириллицей набрано было, поэтому не работало :((( Автор - Hugo
Дата добавления - 26.06.2011 в 19:10
Hugo!,
Замечательно работает, Огромное спасибо!
Я вооще не понимаю как это работает, просто копирую коды и все.
А результат налицо. Еще раз спасибо.
Hugo!,
Замечательно работает, Огромное спасибо!
Я вооще не понимаю как это работает, просто копирую коды и все.
А результат налицо. Еще раз спасибо. Мур
With Range("C2:C101")
.Formula = "='" & sPath & "[" & sFile & "]" & sShName & "'!" & "A1:A100"
.Value = .Value
End With
[/vba]
По-моему, все же так не должно правильно работать, когда диапазоны смещены по строкам. Т.е. значения записываются, но в С2, например, попадает значение из А2, а не из А1. Или это только у меня так?
[/vba]
А с этим да, упс. Кстати, посмотрите в атрибутах файла: "авторы John Walkenbach" - так что все вопросы к нему
With Range("C2:C101")
.Formula = "='" & sPath & "[" & sFile & "]" & sShName & "'!" & "A1:A100"
.Value = .Value
End With
[/vba]
По-моему, все же так не должно правильно работать, когда диапазоны смещены по строкам. Т.е. значения записываются, но в С2, например, попадает значение из А2, а не из А1. Или это только у меня так?
[/vba]
А с этим да, упс. Кстати, посмотрите в атрибутах файла: "авторы John Walkenbach" - так что все вопросы к нему nilem
With Range("C2:C101")
.Formula = "='" & sPath & "[" & sFile & "]" & sShName & "'!" & "A1:A100"
.Value = .Value
End With
[/vba]
По-моему, все же так не должно правильно работать, когда диапазоны смещены по строкам. Т.е. значения записываются, но в С2, например, попадает значение из А2, а не из А1. Или это только у меня так?
[/vba]
А с этим да, упс. Кстати, посмотрите в атрибутах файла: "авторы John Walkenbach" - так что все вопросы к нему Автор - nilem
Дата добавления - 26.06.2011 в 19:55
Sub GetValue(sPath As String, sFile As String)
With Cells(2, 3)
.Formula = "=GetExtData('" & sPath & "[" & sFile & "]Лист1'!A1:A100)"
.Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
End With: arr = Empty
End Sub
Sub GetValue(sPath As String, sFile As String)
With Cells(2, 3)
.Formula = "=GetExtData('" & sPath & "[" & sFile & "]Лист1'!A1:A100)"
.Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
End With: arr = Empty
End Sub
Sub GetValue(sPath As String, sFile As String)
With Cells(2, 3)
.Formula = "=GetExtData('" & sPath & "[" & sFile & "]Лист1'!A1:A100)"
.Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
End With: arr = Empty
End Sub
Чтобы снять подозрения с Джона. Это давнишний файлик из его примеров. Я там уже сто раз все переписывал, так что от Уокенбаха ничего не осталось Да, sPath пропустил.
Ну а вот это
[vba]
With Range("C2:C101")
.Formula = "='" & sPath & "[" & sFile & "]" & sShName & "'!" & "A1:A100"
.Value = .Value
End With
Чтобы снять подозрения с Джона. Это давнишний файлик из его примеров. Я там уже сто раз все переписывал, так что от Уокенбаха ничего не осталось Да, sPath пропустил.
Ну а вот это
[vba]
With Range("C2:C101")
.Formula = "='" & sPath & "[" & sFile & "]" & sShName & "'!" & "A1:A100"
.Value = .Value
End With
Чтобы снять подозрения с Джона. Это давнишний файлик из его примеров. Я там уже сто раз все переписывал, так что от Уокенбаха ничего не осталось Да, sPath пропустил.
Ну а вот это
[vba]
With Range("C2:C101")
.Formula = "='" & sPath & "[" & sFile & "]" & sShName & "'!" & "A1:A100"
.Value = .Value
End With
Т.е. Вы хотите запускать с любого листа, а вставлять в определённый?
Попробуйте так поменять одну строку (я сейчас проверить сам не могу):
[vba]
[/vba]
Будет вставлять на лист с кодовым именем "Лист1".
Можно указать обычное имя листа или индекс:
[vba]
Про извлечение в коде из архива ничего не знаю. Вернее что-то видел, но вроде сложно. Думаю, не стоит заморачиваться.
Т.е. Вы хотите запускать с любого листа, а вставлять в определённый?
Попробуйте так поменять одну строку (я сейчас проверить сам не могу):
[vba]
[/vba]
Будет вставлять на лист с кодовым именем "Лист1".
Можно указать обычное имя листа или индекс:
[vba]
Про извлечение в коде из архива ничего не знаю. Вернее что-то видел, но вроде сложно. Думаю, не стоит заморачиваться. Hugo
[/vba]
Будет вставлять на лист с кодовым именем "Лист1".
Можно указать обычное имя листа или индекс:
[vba]
Про извлечение в коде из архива ничего не знаю. Вернее что-то видел, но вроде сложно. Думаю, не стоит заморачиваться. Автор - Hugo
Дата добавления - 29.06.2011 в 13:46
Достаточно часто появляется вопрос: как извлечь данные из закрытой книги Excel через VBA? Звучит может быть странновато, но это так: вопрос регулярно поднимается на форумах. Собственно, именно в связи с этим и появилась на свет данная статья. В принципе ничего сложного в задаче нет. При этом получить данные можно разными способами, в том числе при помощи функций пользователя(UDF).
Хотя если вдаваться в технические подробности, то получить данные из закрытой книги вообще нельзя. Так или иначе, на уровне системы файл все равно открывается, различие лишь в том как именно и к чему при этом предоставляется доступ. Поэтому переозвучим классическую постановку задачи в более распространенную в жизни: "Как получить данные из книги, не открывая её так, чтобы об этом узнал пользователь"
Попробуем разобраться с некоторыми методами, их плюсами и минусами:
Получение данных из закрытой книги при помощи процедуры VBA
Sub Get_Value_From_Close_Book_Formula() Dim sPath As String, sFile As String, sShName As String sPath = "C:\Documents and Settings\" '" sFile = "Книга1.xls" '" sShName = "Лист1" '" Application.DisplayAlerts = 0 With Range("A1:A100") .Formula = "='" & sPath & "[" & sFile & "]" & sShName & "'!" & "A1" '" '"A1" - указывается начальная ячейка диапазона, из которого необходимо получить значения .Value = .Value End With Application.DisplayAlerts = 1 End Sub
Данный код работает достаточно медленно, но с его помощью можно "вытащить" из закрытой книги значения сразу нескольких ячеек. Код ниже работает быстрее, но с его помощью можно извлечь значения лишь одной ячейки:
Sub Get_Value_From_Close_Book_Excel4Macro() Dim sPath As String, sFile As String, sShName As String Dim sAddress As String, vData sPath = "C:\Documents and Settings\" '" sFile = "Книга1.xls" '" sShName = "Лист1" '" sAddress = "'" & sPath & "[" & sFile & "]" & sShName & "'!" & Range("A1").Address(ReferenceStyle:=xlR1C1) '" vData = ExecuteExcel4Macro(sAddress) End Sub
Если честно, сам я не очень-то люблю ни один из данных методов, т.к. они совершенно лишены гибкости. С их помощью можно получить исключительно значения ячеек. Форматы, формулы или другие свойства ячеек получить уже не получится. Поэтому я предпочитаю открывать книгу и копировать то, что мне надо. Делаю это, скрывая от пользователя при помощи свойства ScreenUpdating объекта Application.
Sub Get_Value_From_Close_Book() Dim sShName As String, sAddress As String, vData Dim objCloseBook As Workbook 'Отключаем обновление экрана Application.ScreenUpdating = False Set objCloseBook = Workbooks.Open("C:\Documents and Settings\Книга1.xls") sAddress = "A1:C100" 'или одна ячейка - "A1" 'получаем значение vData = Sheets("Лист1").Range(sAddress).Value 'Записываем данные на активный лист книги, 'с которой запустили макрос If IsArray(vData) Then [A1].Resize(UBound(vData, 1), UBound(vData, 2)).Value = vData Else [A1] = vData End If 'если надо копировать ячейки с форматами, 'то можно использовать стандартные методы копирования вставки 'objCloseBook.Sheets("Лист1").Range(sAddress).Copy '[A1].PasteSpecial xlPasteValues 'вставляем значения '[A1].PasteSpecial xlPasteFormats 'вставляем форматы 'закрываем книгу(из которой получали значения) без сохранения objCloseBook.Close False 'Включаем обновление экрана Application.ScreenUpdating = True End Sub
Есть и более экзотический метод - при помощи GetObject:
Sub Get_Value_From_Close_Book2() Dim sShName As String, sAddress As String, vData Dim objCloseBook As Object 'Отключаем обновление экрана Application.ScreenUpdating = False Set objCloseBook = GetObject("C:\Documents and Settings\Книга1.xls") sAddress = "A1:C100" 'или одна ячейка - "A1" 'получаем значение vData = objCloseBook.Sheets("Лист1").Range(sAddress).Value 'Записываем данные на активный лист книги, 'с которой запустили макрос If IsArray(vData) Then [A1].Resize(UBound(vData, 1), UBound(vData, 2)).Value = vData Else [A1] = vData End If 'если надо копировать ячейки с форматами, 'то можно использовать стандартные методы копирования вставки 'objCloseBook.Sheets("Лист1").Range(sAddress).Copy '[A1].PasteSpecial xlPasteValues 'вставляем значения '[A1].PasteSpecial xlPasteFormats 'вставляем форматы 'закрываем книгу(из которой получали значения) без сохранения objCloseBook.Close False 'Включаем обновление экрана Application.ScreenUpdating = True End Sub
При таком подходе пользователь разницы не увидит, а действия можно производить с ячейками разные: и сравнение, и отбор по критериям, и фильтровать, и сортировать и т.д. Плюс из книги можно переносить не только значения ячеек, но и форматы, формулы. Но выбирать метод получения значений из закрытых книг вам. Все зависит от ситуации. Все указанные коды работают. Если не работают - то проверьте верно ли указаны все исходные данные(имя книги и расширение, имя листа, путь к папке с книгой).
Получение данных из закрытой книги при помощи UDF
Тот же код, что уже был рассмотрен выше, но оформленный в виде UDF(функции пользователя):
Function Get_Value_From_Close_Book(sWb As String, sShName As String, sAddress As String) Dim vData, objCloseBook As Object Set objCloseBook = GetObject(sWb) 'получаем значение vData = objCloseBook.Sheets(sShName).Range(sAddress).Value objCloseBook.Close False Set objCloseBook = Nothing 'Возвращаем данные в ячейку с функцией Get_Value_From_Close_Book = vData End Function
Синтаксис функции (вызов с листа):
=Get_Value_From_Close_Book("C:\Книга1.xls";"Лист1";"B1")
sWb - полный путь до книги, данные из которой необходимо извлечь ( "C:\Книга1.xls" )
sShName - имя листа в указанной книге, данные из которого необходимо извлечь ( "Лист1" )
sAddress - адрес ячейки(диапазона) данные которой необходимо получить ( "B1" )
Чтобы получить массив ячеек(например B1:B10), необходимо выделить необходимое количество ячеек и ввести в них эту функцию, как формулу массива.
Думаю, не надо пояснять, что любой аргумент может быть задан не статичным текстом, а ссылкой на ячейку с этим текстом. Именно в этом и преимущество использования именно функций, а не процедур.
ПОЛУЧЕНИЕ ДАННЫХ ПРИ ПОМОЩИ ЗАПРОСА ADO
Так же есть еще один достаточно экзотический метод получения данных из действительно закрытой книги - через ADO(ActiveX Data Objects). По сути это получение данных через запрос SQL, используя для этого технологию ADO.
'--------------------------------------------------------------------------------------- ' Procedure : Extract_Value_ADO ' DateTime : 02.07.2014 16:47 ' Author : The_Prist(Щербаков Дмитрий) ' http://www.excel-vba.ru ' Purpose : Функция получения данных из закрытой книги при помощи ADO ' в таком виде не может быть использована вызовом с листа '--------------------------------------------------------------------------------------- Function Extract_Value_ADO(sPath As String, sFileName As String, sShName As String, sRng As String) Dim objADO_Con As Object, objRS As Object Dim sFullFileName As String, sADORng As String 'проверяем наличие слеша в пути к файлу If Right(sPath, 1) <> "\" Then sPath = sPath & "\" 'если ячейка только одна - меняем вид адресации на ячейка:ячейка, как того требует ADO If Range(sRng).Count = 1 Then sADORng = sRng & ":" & sRng Else sADORng = sRng End If sFullFileName = sPath & sFileName With CreateObject("ADODB.Connection") 'подключаемся к файлу .Open "Driver=;ReadOnly=1;DBQ=" & sFullFileName & ";" 'извлекаем записи из указанного диапазона в objRS Set objRS = .Execute("select * FROM [" & sShName & "$" & sADORng & "]") 'выгружаем извлеченные данные на активный лист, начиная с ячейки А1 Cells(1, 1).CopyFromRecordset objRS 'Extract_Value_ADO = objRS.Fields(0).Value End With Set objRS = Nothing End Function
Вызывать эту функцию следует из другой процедуры или функции. Пример процедуры, для вызова этой функции:
Для вызова функции Extract_Value_ADO непосредственно с листа(в виде функции UDF) придется несколько изменить приведенный выше код функции, либо извлекать функцией значение только одной ячейки, что будет не очень экономично с точки зрения ресурсов и использование для этого ADO будет слишком неоправданным. Если кому необходимо, то для вызова функции с ячейки листа и возврата значения одной ячейки, необходимо заменить строку:
Cells(1, 1).CopyFromRecordset objRS
Синтаксис вызова с листа в таком случае будет следующим:
=Extract_Value_ADO("C:\"; "Книга1.xls"; "Лист1"; "A1")
Важно: если данные извлекаются только из одной ячейки, то следует указать две ячейки: А1:А2 . Это особенность работы с запросами
Если же необходимо извлекать данные диапазона ячеек, то в этом случае можно применить такую функцию:
'--------------------------------------------------------------------------------------- ' Procedure : Extract_Value_ADO ' DateTime : 02.07.2014 16:47 ' Author : The_Prist(Щербаков Дмитрий) ' http://www.excel-vba.ru ' Purpose : Функция получения данных из закрытой книги при помощи ADO ' вызывается с листа как функция массива(если получаем данные с диапазона) '--------------------------------------------------------------------------------------- Function Extract_Value_ADO_Sh(sPath As String, sFileName As String, sShName As String, sRng As String) Dim objADO_Con As Object, objRS As Object Dim sFullFileName As String, sADORng As String Dim avTmp(), avRes(), li As Long, lr As Long, lc As Long 'проверяем наличие слеша в пути к файлу If Right(sPath, 1) <> "\" Then sPath = sPath & "\" 'если ячейка только одна - меняем вид адресации на ячейка:ячейка, как того требует ADO If Range(sRng).Count = 1 Then sADORng = sRng & ":" & sRng Else sADORng = sRng End If sFullFileName = sPath & sFileName With CreateObject("ADODB.Connection") 'подключаемся к файлу .Open "Driver=;ReadOnly=1;DBQ=" & sFullFileName & ";" 'получаем кол-во строк в запросе Set objRS = .Execute("SELECT COUNT(*) FROM [" & sShName & "$" & sADORng & "]") li = objRS.Fields(0).Value 'извлекаем записи из указанного диапазона в objRS Set objRS = .Execute("SELECT * FROM [" & sShName & "$" & sADORng & "]") 'выгружаем извлеченные данные на активный лист, начиная с ячейки А1 ReDim avRes(1 To li, 1 To objRS.Fields.Count) avTmp = objRS.getrows(li, 0) 'получаем массив данных запроса For lr = 0 To li - 1 'цикл по строкам For lc = 0 To UBound(avTmp, 1) 'цикл по столбцам 'значения Null не допускаются, поэтому приходится их подменять до выгрузки на лист If IsNull(avTmp(lc, lr)) Then avTmp(lc, lr) = Empty End If avRes(lr + 1, lc + 1) = avTmp(lc, lr) Next lc Next lr End With Extract_Value_ADO_Sh = avRes Set objRS = Nothing End Function
Синтаксис вызова с листа точно такой же как и в функции выше, только нужно будет выделить необходимое количество ячеек и ввести в них эту функцию, как формулу массива.:
=Extract_Value_ADO_Sh("C:\"; "Книга1.xls"; "Лист1"; "A1:B10")
sPath - путь к папке с книгой, данные из которой необходимо извлечь ( "C:\" )
sWb - имя книги, включая расширение(.xls в примере), данные из которой необходимо извлечь ( "Книга1.xls" )
sShName - имя листа в указанной книге, данные из которого необходимо извлечь ( "Лист1" )
sAddress - адрес ячейки(диапазона) данные которой необходимо получить ( "A1" )
Важно: если данные извлекаются только из одной строки, то следует все равно указать минимум две строки: А1:B10 . Это особенность работы с запросами. При попытке указать только одну строку А1:A10 функция вернет значение ошибки. При этом первая строка воспринимается как заголовки. Т.е. данные должны начинаться как минимум со второй строк(A2), а в A1 - заголовок
Хоть эта функция имеет определенные недостатки - она может быть в разы быстрее предыдущей.
Получение данных из закрытой книги при помощи Power Query
Если еще не работали с надстройкой PowerQuery и не знаете что это такое, то для начала лучше ознакомиться со статьей: Power Query - что такое и почему её необходимо использовать в работе?
Переходим на вкладку Данные(для Excel ниже 2016 вкладка PowerQuery) -Получить данные -Из файла -Из книги
Выбираем нужный лист
Если необходимы данные всего листа, то внизу этого окна нажимаем кнопку Загрузить. Все, через пару секунд все данные выбранного листа будут помещены на новый лист текущей книги в умную таблицу.
Достаточно часто появляется вопрос: как извлечь данные из закрытой книги Excel через VBA? Звучит может быть странновато, но это так: вопрос регулярно поднимается на форумах. Собственно, именно в связи с этим и появилась на свет данная статья. В принципе ничего сложного в задаче нет. При этом получить данные можно разными способами, в том числе при помощи функций пользователя(UDF).
Хотя если вдаваться в технические подробности, то получить данные из закрытой книги вообще нельзя. Так или иначе, на уровне системы файл все равно открывается, различие лишь в том как именно и к чему при этом предоставляется доступ. Поэтому переозвучим классическую постановку задачи в более распространенную в жизни: "Как получить данные из книги, не открывая её так, чтобы об этом узнал пользователь"
Попробуем разобраться с некоторыми методами, их плюсами и минусами:
Получение данных из закрытой книги при помощи процедуры VBA
Sub Get_Value_From_Close_Book_Formula() Dim sPath As String, sFile As String, sShName As String sPath = "C:\Documents and Settings\" '" sFile = "Книга1.xls" '" sShName = "Лист1" '" Application.DisplayAlerts = 0 With Range("A1:A100") .Formula = "='" & sPath & "[" & sFile & "]" & sShName & "'!" & "A1" '" '"A1" - указывается начальная ячейка диапазона, из которого необходимо получить значения .Value = .Value End With Application.DisplayAlerts = 1 End Sub
Данный код работает достаточно медленно, но с его помощью можно "вытащить" из закрытой книги значения сразу нескольких ячеек. Код ниже работает быстрее, но с его помощью можно извлечь значения лишь одной ячейки:
Sub Get_Value_From_Close_Book_Excel4Macro() Dim sPath As String, sFile As String, sShName As String Dim sAddress As String, vData sPath = "C:\Documents and Settings\" '" sFile = "Книга1.xls" '" sShName = "Лист1" '" sAddress = "'" & sPath & "[" & sFile & "]" & sShName & "'!" & Range("A1").Address(ReferenceStyle:=xlR1C1) '" vData = ExecuteExcel4Macro(sAddress) End Sub
Если честно, сам я не очень-то люблю ни один из данных методов, т.к. они совершенно лишены гибкости. С их помощью можно получить исключительно значения ячеек. Форматы, формулы или другие свойства ячеек получить уже не получится. Поэтому я предпочитаю открывать книгу и копировать то, что мне надо. Делаю это, скрывая от пользователя при помощи свойства ScreenUpdating объекта Application.
Sub Get_Value_From_Close_Book() Dim sShName As String, sAddress As String, vData Dim objCloseBook As Workbook 'Отключаем обновление экрана Application.ScreenUpdating = False Set objCloseBook = Workbooks.Open("C:\Documents and Settings\Книга1.xls") sAddress = "A1:C100" 'или одна ячейка - "A1" 'получаем значение vData = Sheets("Лист1").Range(sAddress).Value 'Записываем данные на активный лист книги, 'с которой запустили макрос If IsArray(vData) Then [A1].Resize(UBound(vData, 1), UBound(vData, 2)).Value = vData Else [A1] = vData End If 'если надо копировать ячейки с форматами, 'то можно использовать стандартные методы копирования вставки 'objCloseBook.Sheets("Лист1").Range(sAddress).Copy '[A1].PasteSpecial xlPasteValues 'вставляем значения '[A1].PasteSpecial xlPasteFormats 'вставляем форматы 'закрываем книгу(из которой получали значения) без сохранения objCloseBook.Close False 'Включаем обновление экрана Application.ScreenUpdating = True End Sub
Есть и более экзотический метод - при помощи GetObject:
Sub Get_Value_From_Close_Book2() Dim sShName As String, sAddress As String, vData Dim objCloseBook As Object 'Отключаем обновление экрана Application.ScreenUpdating = False Set objCloseBook = GetObject("C:\Documents and Settings\Книга1.xls") sAddress = "A1:C100" 'или одна ячейка - "A1" 'получаем значение vData = objCloseBook.Sheets("Лист1").Range(sAddress).Value 'Записываем данные на активный лист книги, 'с которой запустили макрос If IsArray(vData) Then [A1].Resize(UBound(vData, 1), UBound(vData, 2)).Value = vData Else [A1] = vData End If 'если надо копировать ячейки с форматами, 'то можно использовать стандартные методы копирования вставки 'objCloseBook.Sheets("Лист1").Range(sAddress).Copy '[A1].PasteSpecial xlPasteValues 'вставляем значения '[A1].PasteSpecial xlPasteFormats 'вставляем форматы 'закрываем книгу(из которой получали значения) без сохранения objCloseBook.Close False 'Включаем обновление экрана Application.ScreenUpdating = True End Sub
При таком подходе пользователь разницы не увидит, а действия можно производить с ячейками разные: и сравнение, и отбор по критериям, и фильтровать, и сортировать и т.д. Плюс из книги можно переносить не только значения ячеек, но и форматы, формулы. Но выбирать метод получения значений из закрытых книг вам. Все зависит от ситуации. Все указанные коды работают. Если не работают - то проверьте верно ли указаны все исходные данные(имя книги и расширение, имя листа, путь к папке с книгой).
Получение данных из закрытой книги при помощи UDF
Тот же код, что уже был рассмотрен выше, но оформленный в виде UDF(функции пользователя):
Function Get_Value_From_Close_Book(sWb As String, sShName As String, sAddress As String) Dim vData, objCloseBook As Object Set objCloseBook = GetObject(sWb) 'получаем значение vData = objCloseBook.Sheets(sShName).Range(sAddress).Value objCloseBook.Close False Set objCloseBook = Nothing 'Возвращаем данные в ячейку с функцией Get_Value_From_Close_Book = vData End Function
Синтаксис функции (вызов с листа):
=Get_Value_From_Close_Book("C:\Книга1.xls";"Лист1";"B1")
sWb - полный путь до книги, данные из которой необходимо извлечь ( "C:\Книга1.xls" )
sShName - имя листа в указанной книге, данные из которого необходимо извлечь ( "Лист1" )
sAddress - адрес ячейки(диапазона) данные которой необходимо получить ( "B1" )
Чтобы получить массив ячеек(например B1:B10), необходимо выделить необходимое количество ячеек и ввести в них эту функцию, как формулу массива.
Думаю, не надо пояснять, что любой аргумент может быть задан не статичным текстом, а ссылкой на ячейку с этим текстом. Именно в этом и преимущество использования именно функций, а не процедур.
ПОЛУЧЕНИЕ ДАННЫХ ПРИ ПОМОЩИ ЗАПРОСА ADO
Так же есть еще один достаточно экзотический метод получения данных из действительно закрытой книги - через ADO(ActiveX Data Objects). По сути это получение данных через запрос SQL, используя для этого технологию ADO.
'--------------------------------------------------------------------------------------- ' Procedure : Extract_Value_ADO ' DateTime : 02.07.2014 16:47 ' Author : The_Prist(Щербаков Дмитрий) ' http://www.excel-vba.ru ' Purpose : Функция получения данных из закрытой книги при помощи ADO ' в таком виде не может быть использована вызовом с листа '--------------------------------------------------------------------------------------- Function Extract_Value_ADO(sPath As String, sFileName As String, sShName As String, sRng As String) Dim objADO_Con As Object, objRS As Object Dim sFullFileName As String, sADORng As String 'проверяем наличие слеша в пути к файлу If Right(sPath, 1) <> "\" Then sPath = sPath & "\" 'если ячейка только одна - меняем вид адресации на ячейка:ячейка, как того требует ADO If Range(sRng).Count = 1 Then sADORng = sRng & ":" & sRng Else sADORng = sRng End If sFullFileName = sPath & sFileName With CreateObject("ADODB.Connection") 'подключаемся к файлу .Open "Driver=;ReadOnly=1;DBQ=" & sFullFileName & ";" 'извлекаем записи из указанного диапазона в objRS Set objRS = .Execute("select * FROM [" & sShName & "$" & sADORng & "]") 'выгружаем извлеченные данные на активный лист, начиная с ячейки А1 Cells(1, 1).CopyFromRecordset objRS 'Extract_Value_ADO = objRS.Fields(0).Value End With Set objRS = Nothing End Function
Вызывать эту функцию следует из другой процедуры или функции. Пример процедуры, для вызова этой функции:
Для вызова функции Extract_Value_ADO непосредственно с листа(в виде функции UDF) придется несколько изменить приведенный выше код функции, либо извлекать функцией значение только одной ячейки, что будет не очень экономично с точки зрения ресурсов и использование для этого ADO будет слишком неоправданным. Если кому необходимо, то для вызова функции с ячейки листа и возврата значения одной ячейки, необходимо заменить строку:
Cells(1, 1).CopyFromRecordset objRS
Синтаксис вызова с листа в таком случае будет следующим:
=Extract_Value_ADO("C:\"; "Книга1.xls"; "Лист1"; "A1")
Важно: если данные извлекаются только из одной ячейки, то следует указать две ячейки: А1:А2 . Это особенность работы с запросами
Если же необходимо извлекать данные диапазона ячеек, то в этом случае можно применить такую функцию:
'--------------------------------------------------------------------------------------- ' Procedure : Extract_Value_ADO ' DateTime : 02.07.2014 16:47 ' Author : The_Prist(Щербаков Дмитрий) ' http://www.excel-vba.ru ' Purpose : Функция получения данных из закрытой книги при помощи ADO ' вызывается с листа как функция массива(если получаем данные с диапазона) '--------------------------------------------------------------------------------------- Function Extract_Value_ADO_Sh(sPath As String, sFileName As String, sShName As String, sRng As String) Dim objADO_Con As Object, objRS As Object Dim sFullFileName As String, sADORng As String Dim avTmp(), avRes(), li As Long, lr As Long, lc As Long 'проверяем наличие слеша в пути к файлу If Right(sPath, 1) <> "\" Then sPath = sPath & "\" 'если ячейка только одна - меняем вид адресации на ячейка:ячейка, как того требует ADO If Range(sRng).Count = 1 Then sADORng = sRng & ":" & sRng Else sADORng = sRng End If sFullFileName = sPath & sFileName With CreateObject("ADODB.Connection") 'подключаемся к файлу .Open "Driver=;ReadOnly=1;DBQ=" & sFullFileName & ";" 'получаем кол-во строк в запросе Set objRS = .Execute("SELECT COUNT(*) FROM [" & sShName & "$" & sADORng & "]") li = objRS.Fields(0).Value 'извлекаем записи из указанного диапазона в objRS Set objRS = .Execute("SELECT * FROM [" & sShName & "$" & sADORng & "]") 'выгружаем извлеченные данные на активный лист, начиная с ячейки А1 ReDim avRes(1 To li, 1 To objRS.Fields.Count) avTmp = objRS.getrows(li, 0) 'получаем массив данных запроса For lr = 0 To li - 1 'цикл по строкам For lc = 0 To UBound(avTmp, 1) 'цикл по столбцам 'значения Null не допускаются, поэтому приходится их подменять до выгрузки на лист If IsNull(avTmp(lc, lr)) Then avTmp(lc, lr) = Empty End If avRes(lr + 1, lc + 1) = avTmp(lc, lr) Next lc Next lr End With Extract_Value_ADO_Sh = avRes Set objRS = Nothing End Function
Синтаксис вызова с листа точно такой же как и в функции выше, только нужно будет выделить необходимое количество ячеек и ввести в них эту функцию, как формулу массива.:
=Extract_Value_ADO_Sh("C:\"; "Книга1.xls"; "Лист1"; "A1:B10")
sPath - путь к папке с книгой, данные из которой необходимо извлечь ( "C:\" )
sWb - имя книги, включая расширение(.xls в примере), данные из которой необходимо извлечь ( "Книга1.xls" )
sShName - имя листа в указанной книге, данные из которого необходимо извлечь ( "Лист1" )
sAddress - адрес ячейки(диапазона) данные которой необходимо получить ( "A1" )
Важно: если данные извлекаются только из одной строки, то следует все равно указать минимум две строки: А1:B10 . Это особенность работы с запросами. При попытке указать только одну строку А1:A10 функция вернет значение ошибки. При этом первая строка воспринимается как заголовки. Т.е. данные должны начинаться как минимум со второй строк(A2), а в A1 - заголовок
Хоть эта функция имеет определенные недостатки - она может быть в разы быстрее предыдущей.
Получение данных из закрытой книги при помощи Power Query
Если еще не работали с надстройкой PowerQuery и не знаете что это такое, то для начала лучше ознакомиться со статьей: Power Query - что такое и почему её необходимо использовать в работе?
Переходим на вкладку Данные(для Excel ниже 2016 вкладка PowerQuery) -Получить данные -Из файла -Из книги
Выбираем нужный лист
Если необходимы данные всего листа, то внизу этого окна нажимаем кнопку Загрузить. Все, через пару секунд все данные выбранного листа будут помещены на новый лист текущей книги в умную таблицу.
Достаточно часто появляется вопрос: как извлечь данные из закрытой книги Excel через VBA? Звучит может быть странновато, но это так: вопрос регулярно поднимается на форумах. Собственно, именно в связи с этим и появилась на свет данная статья. В принципе ничего сложного в задаче нет. При этом получить данные можно разными способами, в том числе при помощи функций пользователя(UDF).
Хотя если вдаваться в технические подробности, то получить данные из закрытой книги вообще нельзя. Так или иначе, на уровне системы файл все равно открывается, различие лишь в том как именно и к чему при этом предоставляется доступ. Поэтому переозвучим классическую постановку задачи в более распространенную в жизни: "Как получить данные из книги, не открывая её так, чтобы об этом узнал пользователь"
Попробуем разобраться с некоторыми методами, их плюсами и минусами:
Получение данных из закрытой книги при помощи процедуры VBA
Sub Get_Value_From_Close_Book_Formula() Dim sPath As String, sFile As String, sShName As String sPath = "C:\Documents and Settings\" '" sFile = "Книга1.xls" '" sShName = "Лист1" '" Application.DisplayAlerts = 0 With Range("A1:A100") .Formula = "='" & sPath & "[" & sFile & "]" & sShName & "'!" & "A1" '" '"A1" - указывается начальная ячейка диапазона, из которого необходимо получить значения .Value = .Value End With Application.DisplayAlerts = 1 End Sub
Данный код работает достаточно медленно, но с его помощью можно "вытащить" из закрытой книги значения сразу нескольких ячеек. Код ниже работает быстрее, но с его помощью можно извлечь значения лишь одной ячейки:
Sub Get_Value_From_Close_Book_Excel4Macro() Dim sPath As String, sFile As String, sShName As String Dim sAddress As String, vData sPath = "C:\Documents and Settings\" '" sFile = "Книга1.xls" '" sShName = "Лист1" '" sAddress = "'" & sPath & "[" & sFile & "]" & sShName & "'!" & Range("A1").Address(ReferenceStyle:=xlR1C1) '" vData = ExecuteExcel4Macro(sAddress) End Sub
Если честно, сам я не очень-то люблю ни один из данных методов, т.к. они совершенно лишены гибкости. С их помощью можно получить исключительно значения ячеек. Форматы, формулы или другие свойства ячеек получить уже не получится. Поэтому я предпочитаю открывать книгу и копировать то, что мне надо. Делаю это, скрывая от пользователя при помощи свойства ScreenUpdating объекта Application.
Sub Get_Value_From_Close_Book() Dim sShName As String, sAddress As String, vData Dim objCloseBook As Workbook 'Отключаем обновление экрана Application.ScreenUpdating = False Set objCloseBook = Workbooks.Open("C:\Documents and Settings\Книга1.xls") sAddress = "A1:C100" 'или одна ячейка - "A1" 'получаем значение vData = Sheets("Лист1").Range(sAddress).Value 'Записываем данные на активный лист книги, 'с которой запустили макрос If IsArray(vData) Then [A1].Resize(UBound(vData, 1), UBound(vData, 2)).Value = vData Else [A1] = vData End If 'если надо копировать ячейки с форматами, 'то можно использовать стандартные методы копирования вставки 'objCloseBook.Sheets("Лист1").Range(sAddress).Copy '[A1].PasteSpecial xlPasteValues 'вставляем значения '[A1].PasteSpecial xlPasteFormats 'вставляем форматы 'закрываем книгу(из которой получали значения) без сохранения objCloseBook.Close False 'Включаем обновление экрана Application.ScreenUpdating = True End Sub
Есть и более экзотический метод - при помощи GetObject:
Sub Get_Value_From_Close_Book2() Dim sShName As String, sAddress As String, vData Dim objCloseBook As Object 'Отключаем обновление экрана Application.ScreenUpdating = False Set objCloseBook = GetObject("C:\Documents and Settings\Книга1.xls") sAddress = "A1:C100" 'или одна ячейка - "A1" 'получаем значение vData = objCloseBook.Sheets("Лист1").Range(sAddress).Value 'Записываем данные на активный лист книги, 'с которой запустили макрос If IsArray(vData) Then [A1].Resize(UBound(vData, 1), UBound(vData, 2)).Value = vData Else [A1] = vData End If 'если надо копировать ячейки с форматами, 'то можно использовать стандартные методы копирования вставки 'objCloseBook.Sheets("Лист1").Range(sAddress).Copy '[A1].PasteSpecial xlPasteValues 'вставляем значения '[A1].PasteSpecial xlPasteFormats 'вставляем форматы 'закрываем книгу(из которой получали значения) без сохранения objCloseBook.Close False 'Включаем обновление экрана Application.ScreenUpdating = True End Sub
При таком подходе пользователь разницы не увидит, а действия можно производить с ячейками разные: и сравнение, и отбор по критериям, и фильтровать, и сортировать и т.д. Плюс из книги можно переносить не только значения ячеек, но и форматы, формулы. Но выбирать метод получения значений из закрытых книг вам. Все зависит от ситуации. Все указанные коды работают. Если не работают - то проверьте верно ли указаны все исходные данные(имя книги и расширение, имя листа, путь к папке с книгой).
Получение данных из закрытой книги при помощи UDF
Тот же код, что уже был рассмотрен выше, но оформленный в виде UDF(функции пользователя):
Function Get_Value_From_Close_Book(sWb As String, sShName As String, sAddress As String) Dim vData, objCloseBook As Object Set objCloseBook = GetObject(sWb) 'получаем значение vData = objCloseBook.Sheets(sShName).Range(sAddress).Value objCloseBook.Close False Set objCloseBook = Nothing 'Возвращаем данные в ячейку с функцией Get_Value_From_Close_Book = vData End Function
Синтаксис функции (вызов с листа):
=Get_Value_From_Close_Book("C:\Книга1.xls";"Лист1";"B1")
sWb - полный путь до книги, данные из которой необходимо извлечь ( "C:\Книга1.xls" )
sShName - имя листа в указанной книге, данные из которого необходимо извлечь ( "Лист1" )
sAddress - адрес ячейки(диапазона) данные которой необходимо получить ( "B1" )
Чтобы получить массив ячеек(например B1:B10), необходимо выделить необходимое количество ячеек и ввести в них эту функцию, как формулу массива.
Думаю, не надо пояснять, что любой аргумент может быть задан не статичным текстом, а ссылкой на ячейку с этим текстом. Именно в этом и преимущество использования именно функций, а не процедур.
ПОЛУЧЕНИЕ ДАННЫХ ПРИ ПОМОЩИ ЗАПРОСА ADO
Так же есть еще один достаточно экзотический метод получения данных из действительно закрытой книги - через ADO(ActiveX Data Objects). По сути это получение данных через запрос SQL, используя для этого технологию ADO.
'--------------------------------------------------------------------------------------- ' Procedure : Extract_Value_ADO ' DateTime : 02.07.2014 16:47 ' Author : The_Prist(Щербаков Дмитрий) ' http://www.excel-vba.ru ' Purpose : Функция получения данных из закрытой книги при помощи ADO ' в таком виде не может быть использована вызовом с листа '--------------------------------------------------------------------------------------- Function Extract_Value_ADO(sPath As String, sFileName As String, sShName As String, sRng As String) Dim objADO_Con As Object, objRS As Object Dim sFullFileName As String, sADORng As String 'проверяем наличие слеша в пути к файлу If Right(sPath, 1) <> "\" Then sPath = sPath & "\" 'если ячейка только одна - меняем вид адресации на ячейка:ячейка, как того требует ADO If Range(sRng).Count = 1 Then sADORng = sRng & ":" & sRng Else sADORng = sRng End If sFullFileName = sPath & sFileName With CreateObject("ADODB.Connection") 'подключаемся к файлу .Open "Driver=;ReadOnly=1;DBQ=" & sFullFileName & ";" 'извлекаем записи из указанного диапазона в objRS Set objRS = .Execute("select * FROM [" & sShName & "$" & sADORng & "]") 'выгружаем извлеченные данные на активный лист, начиная с ячейки А1 Cells(1, 1).CopyFromRecordset objRS 'Extract_Value_ADO = objRS.Fields(0).Value End With Set objRS = Nothing End Function
Вызывать эту функцию следует из другой процедуры или функции. Пример процедуры, для вызова этой функции:
Для вызова функции Extract_Value_ADO непосредственно с листа(в виде функции UDF) придется несколько изменить приведенный выше код функции, либо извлекать функцией значение только одной ячейки, что будет не очень экономично с точки зрения ресурсов и использование для этого ADO будет слишком неоправданным. Если кому необходимо, то для вызова функции с ячейки листа и возврата значения одной ячейки, необходимо заменить строку:
Cells(1, 1).CopyFromRecordset objRS
Синтаксис вызова с листа в таком случае будет следующим:
=Extract_Value_ADO("C:\"; "Книга1.xls"; "Лист1"; "A1")
Важно: если данные извлекаются только из одной ячейки, то следует указать две ячейки: А1:А2 . Это особенность работы с запросами
Если же необходимо извлекать данные диапазона ячеек, то в этом случае можно применить такую функцию:
'--------------------------------------------------------------------------------------- ' Procedure : Extract_Value_ADO ' DateTime : 02.07.2014 16:47 ' Author : The_Prist(Щербаков Дмитрий) ' http://www.excel-vba.ru ' Purpose : Функция получения данных из закрытой книги при помощи ADO ' вызывается с листа как функция массива(если получаем данные с диапазона) '--------------------------------------------------------------------------------------- Function Extract_Value_ADO_Sh(sPath As String, sFileName As String, sShName As String, sRng As String) Dim objADO_Con As Object, objRS As Object Dim sFullFileName As String, sADORng As String Dim avTmp(), avRes(), li As Long, lr As Long, lc As Long 'проверяем наличие слеша в пути к файлу If Right(sPath, 1) <> "\" Then sPath = sPath & "\" 'если ячейка только одна - меняем вид адресации на ячейка:ячейка, как того требует ADO If Range(sRng).Count = 1 Then sADORng = sRng & ":" & sRng Else sADORng = sRng End If sFullFileName = sPath & sFileName With CreateObject("ADODB.Connection") 'подключаемся к файлу .Open "Driver=;ReadOnly=1;DBQ=" & sFullFileName & ";" 'получаем кол-во строк в запросе Set objRS = .Execute("SELECT COUNT(*) FROM [" & sShName & "$" & sADORng & "]") li = objRS.Fields(0).Value 'извлекаем записи из указанного диапазона в objRS Set objRS = .Execute("SELECT * FROM [" & sShName & "$" & sADORng & "]") 'выгружаем извлеченные данные на активный лист, начиная с ячейки А1 ReDim avRes(1 To li, 1 To objRS.Fields.Count) avTmp = objRS.getrows(li, 0) 'получаем массив данных запроса For lr = 0 To li - 1 'цикл по строкам For lc = 0 To UBound(avTmp, 1) 'цикл по столбцам 'значения Null не допускаются, поэтому приходится их подменять до выгрузки на лист If IsNull(avTmp(lc, lr)) Then avTmp(lc, lr) = Empty End If avRes(lr + 1, lc + 1) = avTmp(lc, lr) Next lc Next lr End With Extract_Value_ADO_Sh = avRes Set objRS = Nothing End Function
Синтаксис вызова с листа точно такой же как и в функции выше, только нужно будет выделить необходимое количество ячеек и ввести в них эту функцию, как формулу массива.:
=Extract_Value_ADO_Sh("C:\"; "Книга1.xls"; "Лист1"; "A1:B10")
sPath - путь к папке с книгой, данные из которой необходимо извлечь ( "C:\" )
sWb - имя книги, включая расширение(.xls в примере), данные из которой необходимо извлечь ( "Книга1.xls" )
sShName - имя листа в указанной книге, данные из которого необходимо извлечь ( "Лист1" )
sAddress - адрес ячейки(диапазона) данные которой необходимо получить ( "A1" )
Важно: если данные извлекаются только из одной строки, то следует все равно указать минимум две строки: А1:B10 . Это особенность работы с запросами. При попытке указать только одну строку А1:A10 функция вернет значение ошибки. При этом первая строка воспринимается как заголовки. Т.е. данные должны начинаться как минимум со второй строк(A2), а в A1 - заголовок
Хоть эта функция имеет определенные недостатки - она может быть в разы быстрее предыдущей.
Получение данных из закрытой книги при помощи Power Query
Если еще не работали с надстройкой PowerQuery и не знаете что это такое, то для начала лучше ознакомиться со статьей: Power Query - что такое и почему её необходимо использовать в работе?
Переходим на вкладку Данные(для Excel ниже 2016 вкладка PowerQuery) -Получить данные -Из файла -Из книги
Выбираем нужный лист
Если необходимы данные всего листа, то внизу этого окна нажимаем кнопку Загрузить. Все, через пару секунд все данные выбранного листа будут помещены на новый лист текущей книги в умную таблицу.
Как скопировать данные с защищенного листа?
Предположим, вы получили файл Excel от другого человека, рабочие листы которого защищены пользователем, теперь вы хотите скопировать и вставить необработанные данные в другую новую книгу. Но данные на защищенном листе не могут быть выбраны и скопированы из-за того, что пользователь снял флажки «Выбрать заблокированные ячейки» и «Выбрать разблокированные ячейки» при защите рабочего листа. В этой статье я расскажу о некоторых быстрых приемах решения этой задачи в Excel.
Скопируйте данные с защищенного листа с кодом VBA
Следующий простой код может помочь вам скопировать данные в активный защищенный лист, сделайте следующее:
1. Активируйте защищенный лист, с которого вы хотите скопировать данные, а затем удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно, затем нажмите Ctrl + G Ключи для открытия Немедленная окно, а затем скопируйте приведенный ниже код сценария в Немедленная окна:
Activesheet.Cells.Select
2. И нажмите Enter клавишу, затем нажмите Ctrl + C , чтобы скопировать данные листа, а затем активировать другую книгу или новый лист, куда вы хотите вставить этот лист, и поместите курсор в ячейку A1, а затем удерживайте Ctrl + V чтобы вставить его.
Копирование данных с защищенного листа с помощью поля имени
Если вам нужно скопировать определенный диапазон данных с листа защиты, вы можете применить Имя Box чтобы закончить эту работу.
1. Введите диапазон ячеек (D2: H13) в поле Имя Box напрямую и нажмите Enter ключ, см. снимок экрана:
2. После нажатия Enter ключ, видимо ничего не происходит. Пожалуйста, ничего не делайте, просто нажмите Ctrl + C чтобы скопировать определенный диапазон, вы увидите, что данные в диапазоне D2: H13 были окружены пунктирной линией, см. снимок экрана:
3, Затем нажмите Ctrl + V чтобы вставить его на другой лист, куда вы хотите скопировать данные.
Читайте также: