Vba открыть файл excel и скопировать данные
Имеется книга 12345.xlsm (в приложении) с 2-мя листами.
на 1-м листе есть кнопка, которая открывает окно выбора файлов. (GetOpenFile)
Задача:
1. Выбрать один или несколько файлов, можно несколько одновременно (выбираем файлы Excel)
2. Скопировать таблицу из выбранных файлов с Листа1 на Лист2 текущей книги (Важно, чтоб таблицу можно было пополнять)
3. Формат выбираемых файлов представлен в книге 123.xlsx (копировать надо только данные которые выделены зеленым, синие не надо)
4. Строк в таблице, в выбираемых файлах может быть много, 100 и больше
Имеется книга 12345.xlsm (в приложении) с 2-мя листами.
на 1-м листе есть кнопка, которая открывает окно выбора файлов. (GetOpenFile)
Задача:
1. Выбрать один или несколько файлов, можно несколько одновременно (выбираем файлы Excel)
2. Скопировать таблицу из выбранных файлов с Листа1 на Лист2 текущей книги (Важно, чтоб таблицу можно было пополнять)
3. Формат выбираемых файлов представлен в книге 123.xlsx (копировать надо только данные которые выделены зеленым, синие не надо)
4. Строк в таблице, в выбираемых файлах может быть много, 100 и больше
Имеется книга 12345.xlsm (в приложении) с 2-мя листами.
на 1-м листе есть кнопка, которая открывает окно выбора файлов. (GetOpenFile)
Задача:
1. Выбрать один или несколько файлов, можно несколько одновременно (выбираем файлы Excel)
2. Скопировать таблицу из выбранных файлов с Листа1 на Лист2 текущей книги (Важно, чтоб таблицу можно было пополнять)
3. Формат выбираемых файлов представлен в книге 123.xlsx (копировать надо только данные которые выделены зеленым, синие не надо)
4. Строк в таблице, в выбираемых файлах может быть много, 100 и больше
Спасибо! Автор - Aleksej
Дата добавления - 23.03.2016 в 18:22
Aleksej, опять информация не полная.
Вы не указали,
1. Во всех ли файлах типа 123 таблицы идентичны.
2. Все ли данные начинаются с 9-й строки?
Вам предложат макрос и окажется - не так, потому, что таблицы разные и начинаются с разных колонок и разных строк. А это уже другой подход.
Aleksej, опять информация не полная.
Вы не указали,
1. Во всех ли файлах типа 123 таблицы идентичны.
2. Все ли данные начинаются с 9-й строки?
Вам предложат макрос и окажется - не так, потому, что таблицы разные и начинаются с разных колонок и разных строк. А это уже другой подход. Wasilich
Wasilich,
1. Формат файлов идентичный
2. которые есть проверил все с 9 строки.
Извиняюсь, сразу забыл добавить, что там в конце таблицы есть ИТОГО, его тоже не надо копировать (копируются только строки таблицы)
в приложении исправил.
Wasilich,
1. Формат файлов идентичный
2. которые есть проверил все с 9 строки.
Извиняюсь, сразу забыл добавить, что там в конце таблицы есть ИТОГО, его тоже не надо копировать (копируются только строки таблицы)
в приложении исправил. Aleksej
Извиняюсь, сразу забыл добавить, что там в конце таблицы есть ИТОГО, его тоже не надо копировать (копируются только строки таблицы)
в приложении исправил. Автор - Aleksej
Дата добавления - 23.03.2016 в 19:09
Информация нужна не лично мне. А всем, кто решится помочь.
Мои же варианты, как всегда, не совсем профессиональны, растянуты, типа для начинающих, как и я сам. У Сергея KuklP, весь код был бы ~ строчек 5-6.
[vba]
Sub www()
Dim iFile$, rFile$, otkuda As Range
rFile = ActiveWorkbook.Name 'рабочий файл
iFile = Application.GetOpenFilename _
("Excel files(*.xls*),*.xls*", 1, "Выбрать Excel файлы", , False)
iFile = Dir(iFile) 'Выбранный файл
If iFile = "" Then
Exit Sub 'нажата кнопка отмены - выход из процедуры
End If
'MsgBox "Выбран файл: " & iFile
Application.ScreenUpdating = False 'отключаем экран (что б не моргал)
Application.Workbooks.Open Filename:=iFile 'открываем выбранный файл
PS = Sheets("Лист1").Range("B" & Rows.Count).End(xlUp).Row 'номер последней заполненной строки листа
Set otkuda = Sheets("Лист1").Range("A9:X" & PS)
Workbooks(rFile).Activate 'активируем рабочий файл
PS = Sheets("Лист2").Range("B" & Rows.Count).End(xlUp).Row + 1 'номер последней заполненной строки листа
otkuda.Copy Sheets("Лист2").Range("B" & PS)
Workbooks(iFile).Close
Application.ScreenUpdating = True
End Sub
Информация нужна не лично мне. А всем, кто решится помочь.
Мои же варианты, как всегда, не совсем профессиональны, растянуты, типа для начинающих, как и я сам. У Сергея KuklP, весь код был бы ~ строчек 5-6.
[vba]
Sub www()
Dim iFile$, rFile$, otkuda As Range
rFile = ActiveWorkbook.Name 'рабочий файл
iFile = Application.GetOpenFilename _
("Excel files(*.xls*),*.xls*", 1, "Выбрать Excel файлы", , False)
iFile = Dir(iFile) 'Выбранный файл
If iFile = "" Then
Exit Sub 'нажата кнопка отмены - выход из процедуры
End If
'MsgBox "Выбран файл: " & iFile
Application.ScreenUpdating = False 'отключаем экран (что б не моргал)
Application.Workbooks.Open Filename:=iFile 'открываем выбранный файл
PS = Sheets("Лист1").Range("B" & Rows.Count).End(xlUp).Row 'номер последней заполненной строки листа
Set otkuda = Sheets("Лист1").Range("A9:X" & PS)
Workbooks(rFile).Activate 'активируем рабочий файл
PS = Sheets("Лист2").Range("B" & Rows.Count).End(xlUp).Row + 1 'номер последней заполненной строки листа
otkuda.Copy Sheets("Лист2").Range("B" & PS)
Workbooks(iFile).Close
Application.ScreenUpdating = True
End Sub
Информация нужна не лично мне. А всем, кто решится помочь.
Мои же варианты, как всегда, не совсем профессиональны, растянуты, типа для начинающих, как и я сам. У Сергея KuklP, весь код был бы ~ строчек 5-6.
[vba]
Sub www()
Dim iFile$, rFile$, otkuda As Range
rFile = ActiveWorkbook.Name 'рабочий файл
iFile = Application.GetOpenFilename _
("Excel files(*.xls*),*.xls*", 1, "Выбрать Excel файлы", , False)
iFile = Dir(iFile) 'Выбранный файл
If iFile = "" Then
Exit Sub 'нажата кнопка отмены - выход из процедуры
End If
'MsgBox "Выбран файл: " & iFile
Application.ScreenUpdating = False 'отключаем экран (что б не моргал)
Application.Workbooks.Open Filename:=iFile 'открываем выбранный файл
PS = Sheets("Лист1").Range("B" & Rows.Count).End(xlUp).Row 'номер последней заполненной строки листа
Set otkuda = Sheets("Лист1").Range("A9:X" & PS)
Workbooks(rFile).Activate 'активируем рабочий файл
PS = Sheets("Лист2").Range("B" & Rows.Count).End(xlUp).Row + 1 'номер последней заполненной строки листа
otkuda.Copy Sheets("Лист2").Range("B" & PS)
Workbooks(iFile).Close
Application.ScreenUpdating = True
End Sub
Задача состоит в том, чтобы скопировать определенный диапазон текущего листа, открыть другую книгу, и вставить эти скопированные данные в определенную ячейку, сохранить этот файл и закрыть. Ниже приведен код VBA.
Вариант 2: В открывшейся книге запускаем макрос, чтобы он открыл нужную нам книгу, скопировал от туда нужные нам данные и вставил в нашу открытую книгу, закрыв файл из которого эти данные были скопированы
Еще пример — Скопировать диапазоны данных из активной открытой книги Excel нескольких листов (в нашем примере 3-х листов) в другую книгу, которая хранится в определенном месте. Данные будут вставлены как значения, плюс будут перенесены форматы ячеек.
Если статья была вам полезна, то буду благодарен, если вы поделитесь ей со своими друзьями с помощью кнопок расположенных ниже.
Спасибо за внимание.
Related posts:
Здравствуйте.
А если, например, открыто две книги и находясь в активной книге, как не используя путь, скопировать данные в скрытом листе пассивной книги и вставить их в активную.
Здравствуйте
А если например открыто две книги и находясь в активной книге, как не используя путь скопировать данные с пассивной книги и вставить их в активную.
Hey. I sent a screenshot. Did you get it?
Здравствуйте.
редактирую Ваш код. Возникло 2 проблемы:
1.начало таблицы, из которой необходимо скопировать данные каждый раз начинается с разного номера строки так как в первоначальном файле данные формируются расширенным фильтром (выделено красным)
2.перед тем как скопируются новые данные во второй файл, необходимо , чтобы из этого столбца удалились старые
Sub Макрос()
Range(«b20000:b20100»).Select
Selection.Copy
Workbooks.Open Filename:= «путь к файлу, в который копируем»
Range(«A2»).Select
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub
Можно так сделать или нужно писать другой макрос?
Добрый день. Подскажите что надо поменять в это строчке что бы не прописывать полный путь к файлу, а можно было выбирать диалоговым окном путь и имя файла
Set bookconst = Workbooks.Open(«C:\Users\User\Desktop\1.xlsx»)
Вызов диалогового окна нашел. Спасибо за ответ
Sub AttachFile_test() ‘ пример использования
Filename$ = GetFilePath()
If Filename$ = «» Then Exit Sub
MsgBox «Выбран файл: » & Filename$
End Sub
Function GetFilePath(Optional ByVal Title As String = «Выберите файл для обработки», _
Optional ByVal InitialPath As String = «c:\», _
Optional ByVal FilterDescription As String = «Файлы счетов», _
Optional ByVal FilterExtention As String = «*.*») As String
On Error Resume Next
With Application.FileDialog(msoFileDialogOpen)
.ButtonName = «Выбрать»: .Title = Title:
.InitialFileName = GetSetting(Application.Name, «GetFilePath», «folder», InitialPath)
.Filters.Clear: .Filters.Add FilterDescription, FilterExtention
If .Show -1 Then Exit Function
GetFilePath = .SelectedItems(1)
folder$ = Left(.SelectedItems(1), InStrRev(.SelectedItems(1), «\»))
SaveSetting Application.Name, «GetFilePath», «folder», folder$
End With
End Function
Добрый день! Могли бы Вы помочь в решении проблемы, есть лист который необходимо поделить на несколько новых книг по параметру Город ( т.е. будет новая книга :Москва в которой будет шапка, город, марка,номер, водитель, Питер в которой будет шапка, город, марка,номер, водитель и т.д.) при этом сохранились форматы ячеек, а сама книга будет названа как город который будет в ней. Потом необходимо поделить лист на книги по параметру Марка и книга будет называться как Марка которая в ней будет. Заранее благодарна!
Как можно изменить код макроса, если при открытии файла его нет по указанному адресу. Нужно чтобы макрос остановится и открылось окно с информацией, что файл не найден. Иначе выдаёт ошибку.
Здравствуйте! Помогите, пожалуйста, в решении задачи:
Есть 2 файла. В первом файле хранятся столбцы с данными, файл ежедневно обновляется данными. Во втором — итоговая таблица. Нужно автоматически копировать столбцы из первого файла и вставить в конец таблицы второго.
Помогите, пожалуйста.
Нужен макрос, чтобы по нажатию, копировал данные из определенного статичного столбца в активный столбец (на котором стоит курсор).
Заранее, спасибо!
Включите запись макрорекодера, проделайте всю операцию и остановите запись. Получится нужный вам макрос
Добрый день! Вы просто герой! всем отвечаете и помогаете!
Помогите и мне пожалуйста, Вот код:
Sub форЮля()
Range(«A1:G1»).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Selection.Copy
ChDir «C:\Users\karlov\Desktop\mind»
Workbooks.Open Filename:=»1″
Range(«A1»).Select
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CutCopyMode = False
End Sub
как сделать так что бы запись в фаил «1» всегда была на одну строчку ниже?
2 дня уже не могу сделать , надеюсь на Вас. Спасибо.
Попробуйте добавить между
Range («A1»).Select и ActiveSheet.Paste
ActiveSheet.Range("A1").End(xlDown).Offset(1, 0).Select
ДД!
спасибо большое за Ваш пост! Нужно было переносить строки из общей таблицы на разные листы другой книги.
Подскажите, можно ли добавить условие в код? У меня большая таблица хронометража по разным показателям. Возможно ли копирование только тех ячеек значение которых больше 0?
То, что я имею сейчас:
Sub Перенос_строк_состав_комм_КМ()
Workbooks.Open Filename:=C:\1\2.xlsx»
Workbooks(«2.xlsx»).Worksheets(«приложение 3»).Range(«F30:BC30»).Copy
Workbooks(«КФ.xlsm»).Activate
ActiveWorkbook.Worksheets(«составление комм КМ»).Range(«C5»).Select
ActiveSheet.Paste
Workbooks(«2.xlsx»).Close
End Sub
Добрый день. Ваш код мне очень подходит. Вопрос, как изменить диапазон ячеек в коде, при условии, что я хочу перенести в другую книгу в определенный лист несколько разных ячеек с одного листа НАПРИМЕР диапазоны B7:С24 и H7:I24 . Подскажите как правильно это нужно указать. Спасибо.
Решили вопрос? Вам нужно скопировать сначала один диапазон, потом снова выделить книгу откуда вы копируете, например так
Workbooks(«Книга1.xlsm»).Activate
снова повторить копирование уже другого диапазона
У меня более 30 вкладок в одной книге, и часть ячеек не должна копировать в другую книгу, они должны совместиться. Я уже много перепробовала, но почему то копирует только второй диапазон, а первый будто не видит… например я пишу код
abook.Worksheets(«3»).Activate
Range(«B7:C39», «H7:I39»).Copy
bookconst.Worksheets(«3»).Activate
Range(«B7»).Select ‘выделяем диапазон
а копирует он только диапазон «H7:I39», игнорируя диапазон B7:C39
Так я и не решила эту проблему..
На мой взгляд у вас проблема о объединенных ячейках. У вас 61 и другие строки объединены. Вы попробуйте вручную выделить диапазон B7:C39 — у вас не получится и у VBA тоже не получается
Нужно делать по-отдельности. Сначала одни диапазон, потом второй и так далее
abook.Worksheets («3»).Activate
Range («B7:C39»).Copy
bookconst.Worksheets («3»).Activate
Range («B7»).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
После этого снова активируете первую книгу и копируете второй диапазон
abook.Worksheets («3»).Activate
Range («H7:I39»).Copy
…
Эти Варианты я все перепробовала, решение оказалось таким:
Range(«B9:C2900, D9:E2900, K9:L2900»).Copy
bookconst.Worksheets(«благ»).Activate
Range(«B4»).Select ‘встаем на ячейку А1
При этом в заданных диапазонах не должны быть пересечения объеденных ячеек.
СПАСИБО АВТОРУ
Добрый день,
В коде при варианте 1 выводит ошибку 1004, выделяя желтым строку кода:
Range(«A6»).Select
В этой строке вряд ли ошибка может быть. Проверил код, должно все работать
Единственное вот тут неправильное описание
Workbooks.Open Filename:= «путь к файлу»
здесь нужно указать не путь к файлу, а только название файла, путь к папке мы указали в чуть выше в коде
Вопрос не актуален, спасибо!
Решение найдено!
Отлично, в чем была причина?
Также, вот чуток модифицированный вариант для того, чтобы данные не перезаписывались, а вставлялись в новую строку:
Range(«A1:E1»).Select
Selection.Copy
ChDir «C:\Users\50545\Desktop»
Workbooks.Open Filename:=»123.xlsx»
ActiveSheet.Range(«A1»).Select
If ActiveSheet.Range(«A1»).Value = «» Then
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
Else
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
Создал отдельный модуль для макроса (до этого был в модуле листа)…
Добрый день,
К сожалению, ошибку все еще выдает 🙁
Прикладываю сам файл и принт-скрин…
Буду признателен за помощь в исправлении…
Доброго времени суток.
Подскажите пожалуйста код макроса со следующим функционалом:
Есть несколько документов. Первый документ — база со всеми данными, а остальные — документы в которых нужно скопировать некоторые данные из базы.
На словах макрос я бы описал так:
Открываем документ «База» вкладка «Лист1» копируем значение ячейки С15, после чего вставляем её в документ «Отчёт» вкладка «Данные» в поле F19.
Далее
Открываем документ «База» вкладка «Лист1» копируем значение ячейки С18, после чего вставляем её в документ «Отчёт» вкладка «Данные» в поле D20.
Далее
Открываем документ «База» вкладка «Лист1» копируем значение ячейки С24, после чего вставляем её в документ «Отчёт» вкладка «Данные» в поле L3.
И так далее…
Да, необходимо копировать только значение (стиль и т.д. не нужно) И если например в базе какая то ячейка стала пустой, то на пустоту необходимо заменить значение, что было прописано ранее в остальных документах.
В программировании не силён, если можно с кратким объяснением.
Пример приложил. Значения цифровые и текстовые, могут быть одновременно. Должны быть скопированы в неизменном виде (как в примере). Поля Параметр 2, Параметр 3 и т.д. роли не играют. Этим я просто показал что ячейки могут находиться в разных местах.
Да, в примере имена людей совпадают, но в реальности они могут отличаться, потому то я и прошу скрипт с указанием с какой в какую ячейку копировать данные. Положение в списке как правило не меняется.
Добрый вечер. Это опять я.
Появилась новая задача. Есть файл источник в котором есть данные.
Тот макрос, который Вы помогли запустить копирует только из этого, определенного файла.
А можно сделать так, чтобы макрос работал из любого другого файла.
То есть я копирую макрос в другой файл , а он сохраняет в отдельный.
нет. все равно заменяет.
может Вы можете помочь немного по другому.
Вот макрос, который копирует нужный диапазон и копирует в нужную книгу.
НО как и в первом варианте надо , после изменения в основном файле данных, вставлять не в туже строку , а в следующую.
Sub Кнопка2 Щелчок()
‘
‘ Кнопка2_Щелчок Макрос
‘
‘ Сочетание клавиш: Ctrl+j
‘
Range(«A7:J7″).Select
Selection.Copy
Workbooks.Open Filename:=»C:\Users\Slava\Desktop\Neuer Ordner\1.xlsm»
Range(«A1:J1»).Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close
End Sub
может быть так проще?
Нет, не проще. Проверьте еще раз внимательно. У меня все работает. Данные не заменяются, а вставляются в следующие строку.
Сейчас, кстати заметил, что надо брать не G5000, а F5000 так как в столбце G ничего не вставляется, поэтому он и заменяет данные, так как последняя строчка всегда одна и та же
Добрый вечер. Хочу Вас поблагодарить. Все получилось.
Только я методом «тыка» изменил немного код. А именно вот эту строку
iLastRow = Workbooks(«Книга1.xlsm»).Worksheets(«Лист1»).Range(«F5000»).End(xlUp).Row + 1
у Вас стоял («Книга1.xlsm») а я написал («1.xlsm») указал ту книгу в которую производить копирование и всё пошло нормально.
Еще раз спасибо.
Да, все верно. Я просто тестировал на своих файлах и забыл поменять. Удачи!
Всем нам приходится - кому реже, кому чаще - повторять одни и те же действия и операции в Excel. Любая офисная работа предполагает некую "рутинную составляющую" - одни и те же еженедельные отчеты, одни и те же действия по обработке поступивших данных, заполнение однообразных таблиц или бланков и т.д. Использование макросов и пользовательских функций позволяет автоматизировать эти операции, перекладывая монотонную однообразную работу на плечи Excel. Другим поводом для использования макросов в вашей работе может стать необходимость добавить в Microsoft Excel недостающие, но нужные вам функции. Например функцию сборки данных с разных листов на один итоговый лист, разнесения данных обратно, вывод суммы прописью и т.д.
Макрос - это запрограммированная последовательность действий (программа, процедура), записанная на языке программирования Visual Basic for Applications (VBA). Мы можем запускать макрос сколько угодно раз, заставляя Excel выполнять последовательность любых нужных нам действий, которые нам не хочется выполнять вручную.
Способ 1. Создание макросов в редакторе Visual Basic
Для ввода команд и формирования программы, т.е. создания макроса необходимо открыть специальное окно - редактор программ на VBA, встроенный в Microsoft Excel.
- В старых версиях (Excel 2003 и старше) для этого идем в меню Сервис - Макрос - Редактор Visual Basic(Toos - Macro - Visual Basic Editor).
- В новых версиях (Excel 2007 и новее) для этого нужно сначала отобразить вкладку Разработчик (Developer) . Выбираем Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon) и включаем в правой части окна флажок Разработчик (Developer) . Теперь на появившейся вкладке нам будут доступны основные инструменты для работы с макросами, в том числе и нужная нам кнопка Редактор Visual Basic(Visual Basic Editor)
:
К сожалению, интерфейс редактора VBA и файлы справки не переводятся компанией Microsoft на русский язык, поэтому с английскими командами в меню и окнах придется смириться:
Макросы (т.е. наборы команд на языке VBA) хранятся в программных модулях. В любой книге Excel мы можем создать любое количество программных модулей и разместить там наши макросы. Один модуль может содержать любое количество макросов. Доступ ко всем модулям осуществляется с помощью окна Project Explorer в левом верхнем углу редактора (если его не видно, нажмите CTRL+R). Программные модули бывают нескольких типов для разных ситуаций:
-
Обычные модули - используются в большинстве случаев, когда речь идет о макросах. Для создания такого модуля выберите в меню Insert - Module. В появившееся окно нового пустого модуля можно вводить команды на VBA, набирая их с клавиатуры или копируя их из другого модуля, с этого сайта или еще откуда нибудь:
Обычный макрос, введенный в стандартный модуль выглядит примерно так:
Давайте разберем приведенный выше в качестве примера макрос Zamena:
С ходу ясно, что вот так сразу, без предварительной подготовки и опыта в программировании вообще и на VBA в частности, сложновато будет сообразить какие именно команды и как надо вводить, чтобы макрос автоматически выполнял все действия, которые, например, Вы делаете для создания еженедельного отчета для руководства компании. Поэтому мы переходим ко второму способу создания макросов, а именно.
Способ 2. Запись макросов макрорекордером
Макрорекордер - это небольшая программа, встроенная в Excel, которая переводит любое действие пользователя на язык программирования VBA и записывает получившуюся команду в программный модуль. Если мы включим макрорекордер на запись, а затем начнем создавать свой еженедельный отчет, то макрорекордер начнет записывать команды вслед за каждым нашим действием и, в итоге, мы получим макрос создающий отчет как если бы он был написан программистом. Такой способ создания макросов не требует знаний пользователя о программировании и VBA и позволяет пользоваться макросами как неким аналогом видеозаписи: включил запись, выполнил операци, перемотал пленку и запустил выполнение тех же действий еще раз. Естественно у такого способа есть свои плюсы и минусы:
- Макрорекордер записывает только те действия, которые выполняются в пределах окна Microsoft Excel. Как только вы закрываете Excel или переключаетесь в другую программу - запись останавливается.
- Макрорекордер может записать только те действия, для которых есть команды меню или кнопки в Excel. Программист же может написать макрос, который делает то, что Excel никогда не умел (сортировку по цвету, например или что-то подобное).
- Если во время записи макроса макрорекордером вы ошиблись - ошибка будет записана. Однако смело можете давить на кнопку отмены последнего действия (Undo) - во время записи макроса макрорекордером она не просто возрвращает Вас в предыдущее состояние, но и стирает последнюю записанную команду на VBA.
Чтобы включить запись необходимо:
- в Excel 2003 и старше - выбрать в меню Сервис - Макрос - Начать запись(Tools - Macro - Record New Macro)
- в Excel 2007 и новее - нажать кнопку Запись макроса (Record macro) на вкладке Разработчик (Developer)
Затем необходимо настроить параметры записываемого макроса в окне Запись макроса:
- Имя макроса - подойдет любое имя на русском или английском языке. Имя должно начинаться с буквы и не содержать пробелов и знаков препинания.
- Сочетание клавиш - будет потом использоваться для быстрого запуска макроса. Если забудете сочетание или вообще его не введете, то макрос можно будет запустить через меню Сервис - Макрос - Макросы - Выполнить(Tools - Macro - Macros - Run) или с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или нажав ALT+F8.
- Сохранить в. - здесь задается место, куда будет сохранен текст макроса, т.е. набор команд на VBA из которых и состоит макрос.:
- Эта книга - макрос сохраняется в модуль текущей книги и, как следствие, будет выполнятся только пока эта книга открыта в Excel
- Новая книга - макрос сохраняется в шаблон, на основе которого создается любая новая пустая книга в Excel, т.е. макрос будет содержаться во всех новых книгах, создаваемых на данном компьютере начиная с текущего момента
- Личная книга макросов - это специальная книга Excel с именем Personal.xls, которая используется как хранилище макросов. Все макросы из Personal.xls загружаются в память при старте Excel и могут быть запущены в любой момент и в любой книге.
После включения записи и выполнения действий, которые необходимо записать, запись можно остановить командой Остановить запись (Stop Recording) .
Запуск и редактирование макросов
Управление всеми доступными макросами производится в окне, которое можно открыть с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или - в старых версиях Excel - через меню Сервис - Макрос - Макросы (Tools - Macro - Macros) :
- Любой выделенный в списке макрос можно запустить кнопкой Выполнить(Run) .
- Кнопка Параметры(Options) позволяет посмотреть и отредактировать сочетание клавиш для быстрого запуска макроса.
- Кнопка Изменить(Edit) открывает редактор Visual Basic (см. выше) и позволяет просмотреть и отредактировать текст макроса на VBA.
Создание кнопки для запуска макросов
Чтобы не запоминать сочетание клавиш для запуска макроса, лучше создать кнопку и назначить ей нужный макрос. Кнопка может быть нескольких типов:
Кнопка на панели инструментов в Excel 2003 и старше
Откройте меню Сервис - Настройка (Tools - Customize) и перейдите на вкладку Команды (Commands) . В категории Макросы легко найти веселый желтый "колобок" - Настраиваемую кнопку (Custom button) :
Перетащите ее к себе на панель инструментов и затем щелкните по ней правой кнопкой мыши. В контекстом меню можно назначить кнопке макрос, выбрать другой значок и имя:
Кнопка на панели быстрого доступа в Excel 2007 и новее
Щелкните правой кнопкой мыши по панели быстрого доступа в левом верхнем углу окна Excel и выберите команду Настройка панели быстрого доступа (Customise Quick Access Toolbar) :
Затем в открывшемся окне выберите категорию Макросы и при помощи кнопки Добавить (Add) перенесите выбранный макрос в правую половину окна, т.е. на панель быстрого доступа:
Кнопка на листе
Этот способ подходит для любой версии Excel. Мы добавим кнопку запуска макроса прямо на рабочий лист, как графический объект. Для этого:
- В Excel 2003 и старше - откройте панель инструментов Формы через меню Вид - Панели инструментов - Формы (View - Toolbars - Forms)
- В Excel 2007 и новее - откройте выпадающий список Вставить (Insert) на вкладке Разработчик (Developer)
Выберите объект Кнопка (Button) :
Затем нарисуйте кнопку на листе, удерживая левую кнопку мыши. Автоматически появится окно, где нужно выбрать макрос, который должен запускаться при щелчке по нарисованной кнопке.
Создание пользовательских функций на VBA
Создание пользовательских функций или, как их иногда еще называют, UDF-функций (User Defined Functions) принципиально не отличается от создания макроса в обычном программном модуле. Разница только в том, что макрос выполняет последовательность действий с объектами книги (ячейками, формулами и значениями, листами, диаграммами и т.д.), а пользовательская функция - только с теми значениями, которые мы передадим ей как аргументы (исходные данные для расчета).
Чтобы создать пользовательскую функцию для расчета, например, налога на добавленную стоимость (НДС) откроем редактор VBA, добавим новый модуль через меню Insert - Module и введем туда текст нашей функции:
Обратите внимание, что в отличие от макросов функции имеют заголовок Function вместо Sub и непустой список аргументов (в нашем случае это Summa). После ввода кода наша функция становится доступна в обычном окне Мастера функций (Вставка - Функция) в категории Определенные пользователем (User Defined) :
После выбора функции выделяем ячейки с аргументами (с суммой, для которой надо посчитать НДС) как в случае с обычной функцией:
Достаточно часто появляется вопрос: как извлечь данные из закрытой книги 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 в другое приложение. Я возьму для примера Word. Например скопировать ячейки и вставить. Обычно мы это так и делаем - скопировали в Excel, открыли Word - вставили. Но сделать это при помощи кода чуть сложнее, хотя если разобраться никаких сложностей нет. Ниже приведен пример кода, который открывает Word, открывает в нем определенный документ, копирует данные из Excel и вставляет в открытый документ Word.
Sub OpenWord() Dim objWrdApp As Object, objWrdDoc As Object 'создаем новое приложение Word Set objWrdApp = CreateObject("Word.Application") 'Можно так же сделать приложение Word видимым. По умолчанию открывается в скрытом режиме 'objWrdApp.Visible = True 'открываем документ Word - документ "Doc1.doc" должен существовать Set objWrdDoc = objWrdApp.Documents.Open("C:\Doc1.doc") 'Копируем из Excel диапазон "A1:A10" Range("A1:A10").Copy 'вставляем скопированные ячейки в Word - в начала документа objWrdDoc.Range(0).Paste 'закрываем документ Word с сохранением objWrdDoc.Close True ' False - без сохранения 'закрываем приложение Word - обязательно! objWrdApp.Quit 'очищаем переменные Word - обязательно! Set objWrdDoc = Nothing: Set objWrdApp = Nothing End Sub
Tips_Macro_OpenWord.xls (49,5 KiB, 5 922 скачиваний)
В файле-примере, приложенном к данной статье, в комментариях к коду есть несколько добавлений. Например, как вставить текст из ячеек в определенные закладки Word-а и как добавить новый документ, а не открывать уже имеющийся. Так же так есть код проверки - открыто ли приложение Word в данный момент. Порой это тоже может пригодиться, чтобы работать с запущенным приложением Word, а не создавать новое:В принципе, активировать или вызвать(если закрыто) другое приложение Офиса можно одной строкой:
Sub Open_AnotherApp() Application.ActivateMicrosoftApp xlMicrosoftWord End Sub
но данный метод может пригодиться только в том случае, если Вам необходимо действительно лишь активировать другое приложение, но дальше обращаться к этому приложению Вы уже не сможете.
По сути, методами CreateObject и GetObject можно обратиться к любому стороннему приложению(например Internet Explorer). Куда важнее при обращении к этим объектам знать объектную модель того приложения, к которому обращаетесь. Чтобы увидеть свойства и методы объектной модели приложения, можно в редакторе VBA подключить необходимую библиотеку, объявить переменную, назначив ей тип приложения. Покажу на примере того же Word-а.
Для начала открываем меню Tools - References :
Подключаем библиотеку:
Затем объявляем переменную и присваиваем ей тип нужного приложения:Sub OpenWord() Dim objWrdApp As Word.Application Set objWrdApp = New Word.Application objWrdApp.Visible = True End Sub
Если теперь в редакторе, внутри этой процедуры в любом месте ниже объявления переменной набрать objWrdApp и точку, то сразу после ввода точки выпадет меню, в котором будут перечислены все доступные методы и свойства этого приложения.
Так же можно нажать F2 и через поиск найти Word и просмотреть все методы и свойства данного приложения.Метод установки ссылки на библиотеку приложения через Tools-References называют еще ранним связыванием. Подобный метод позволяет создать ссылку на приложение быстрее и, как описано выше, предоставляет разработчику доступ к визуальному отображению свойств и методов объекта. Но есть существенный минус: если в своем коде Вы установите ссылку на Word 12 Object Libbary(Word 2007), то на ПК с установленным Word 2003 получите ошибку MISSING, т.к. Word 2003 относится к библиотеке Word 11 Object Libbary. Подробнее можно прочитать в статье Ошибка — Cant find project or library.
Метод же CreateObject еще называется методом позднего связывания. Применяя его не возникнет проблем с MISSING, очень часто возникающих при раннем связывании. Поэтому я рекомендовал бы при разработке использовать раннее связывание для удобства использования свойств и методов(если Вы их не знаете), а перед распространением приложения в коде заменить все именованные константы(типа wdLine) на числовые константы(для wdLine это 5) и применить позднее связывание. Посмотреть числовое значение константы можно просто записав её в коде, начать выполнение кода через F8 и навести курсор мыши на эту константу. Всплывающая подсказка покажет числовое значение. Так же можно отобразить окно Immediate(View -Immediate Window или сочетание клавиш Ctrl + G ), записать вопросительный знак и вставить эту константу и нажать Enter :
?wdLine
ниже будет выведено числовое представление этой константы.
А заменять эти константы их числовыми значениями в случае с поздним связыванием необходимо, т.к. Excel не знает их значений.
Попробую пояснить поподробнее про эти константы и почему их надо заменять какими-то числами: при подключении библиотеки Wordа(Word 12 Object Libbary) мы так же подключаем и все свойства, методы и константы, которые доступны из Wordа. И их использование напрямую становится доступно из Excel и мы можем смело написать что-то вроде wbLine и Excel поймет эту константу. При позднем же связывании мы уже не подключаем библиотеки Word(во избежание ошибок совместимости) и как следствие - методы, свойства и константы Wordа для Excel становятся чем-то неизвестным и не документированным и мы получим ошибку "Variable not defined"(если включена директива Option Explicit) при попытке назначить свойство через wdLine. Если же Option Explicit не включена - то хоть ошибки не будет, но и код будет работать неверно, т.к. для неизвестной для Excel переменной wbLine будет назначено значение 0(Empty). Поэтому и надо все константы другого приложения заменять их числовыми значениями.Главная ошибка новичка
И хочу так же упомянуть про ошибку, которую очень часто совершают при обращении к одному приложению из другого. Допустим, необходимо скопировать из Word все данные в Excel. Часто начинающие делают это так:Sub OpenWord() Dim objWrdApp As Object, objWrdDoc As Object 'создаем новое приложение Word Set objWrdApp = CreateObject("Word.Application") 'Можно так же сделать приложение Word видимым. По умолчанию открывается в скрытом режиме 'objWrdApp.Visible = True 'открываем документ Word - документ "Doc1.doc" должен существовать Set objWrdDoc = objWrdApp.Documents.Open("C:\Doc1.doc") 'Копируем из Word все данные, обращаясь к объекту Range документа Range.Copy 'вставляем скопированное в ячейку А1 активного листа Excel ActiveSheet.Paste 'закрываем документ Word без сохранения objWrdDoc.Close False 'закрываем приложение Word objWrdApp.Quit 'очищаем переменные Word - обязательно! Set objWrdDoc = Nothing: Set objWrdApp = Nothing End Sub
На строке Range.Copy обязательно получите ошибку от VBA, указывающую, что нужен аргумент для объекта. Можно попробовать добавить этот аргумент: Range(1).Copy. Но все равно получим ошибку. Можно, конечно, указать даже ячейки: Range("A1").Copy. Но это приведет к тому, что скопирована будет ячейка А1 активного листа Excel.
Все дело в том, что мы хотим скопировать данные из Word-а, выполняя при этом код из Excel. А у Excel тоже есть объект Range с другими аргументами. И если не указать какому приложению, листу или документу принадлежит Range, то по умолчанию он будет отнесен к тому приложению, из которого выполняется код. Т.е. к Excel. Если совсем кратко об этом - всегда надо указывать какому приложению или объекту принадлежит используемый объект или свойство. Правильно код должен выглядеть так:Sub OpenWord() Dim objWrdApp As Object, objWrdDoc As Object 'создаем новое приложение Word Set objWrdApp = CreateObject("Word.Application") 'Можно так же сделать приложение Word видимым. По умолчанию открывается в скрытом режиме 'objWrdApp.Visible = True 'открываем документ Word - документ "Doc1.doc" должен существовать Set objWrdDoc = objWrdApp.Documents.Open("C:\Doc1.doc") 'Копируем из Word все данные, обращаясь к объекту Range документа 'при этом перед Range явно указываем откуда его брать - из документа Word -objWrdDoc("C:\Doc1.doc") objWrdDoc.Range.Copy 'вставляем скопированное из Word в активную ячейку активного листа Excel ActiveSheet.Paste 'закрываем документ Word без сохранения objWrdDoc.Close False 'закрываем приложение Word objWrdApp.Quit 'очищаем переменные Word - обязательно! Set objWrdDoc = Nothing: Set objWrdApp = Nothing End Sub
Вместо Range ту же ошибку делают и с Selection(потому что Selection часто присутствует в записанных макрорекордером макросах), т.к. этот объект есть и в Excel и в Word и без явного указания приложения будет относится к приложению, в котором записано.
Tips_Macro_OpenWord.xls (49,5 KiB, 5 922 скачиваний)
А в архиве ниже - практически готовое решение заполнения всевозможных бланков Word из Excel. Как это работает. У нас есть таблица Excel с данными для заполнения бланков заявлений на пособия:
Обращаю внимание, что в первой строке расположены метки. Они нужны для того, чтобы код мог понять значения какого столбца в какое место шаблона Word должны попасть. А в самом шаблоне Word мы должны проставить эти самые метки:
Фигурные скобки сделаны для того, чтобы код 100% искал и заменял только метку в шаблоне, исключая при этом замену случайного текста вне скобок(ведь слово "Должность" может встречаться и само по себе).
А здесь я схематично привел то, как будут происходить замены:
Сначала программа создаст новую папку, в которую и будет сохранять создаваемые файлы(имя папки состоит из даты и времени запуска кода). Далее программа циклом пройдется по каждой строке таблицы, создаст на основании шаблона Word( "Шаблон.doc" ) новый файл для этой строки, заполнит этот шаблона данными на основании меток, и сохранит созданный файл под новым именем. Сам файл шаблона при этом не изменяется - все метки в нем сохраняются как были настроены до запуска кода. Конкретно в приложенном коде значение для имени нового файла берется из первого столбца "ФИО с инициалами". Но это можно изменить в коде при необходимости. Делается это в этой строке:'считываем фамилию с инициалами sWDDocName = .Cells(lr, 1).Value
Что еще важно: файл шаблона Word должен находиться в той же папке, что и файл с кодом. Название файла в приложенном к статье файле должно быть "Шаблон.doc". Но его так же можно изменить, не забыв изменив его в коде в этой строке:
'имя шаблона Word с основным текстом и метками Const sWDTmpl As String = "Шаблон.doc"
Читайте также: