Excel vba вставить таблицу в форму

Обновлено: 04.07.2024

Вы можете попытаться записать макрос, чтобы сделать это, но макросы позволят вам только автоматизировать задачи внутри Word. В этой статье вы узнаете, как писать код VBA для автоматизации этих действий между Excel и Word.

Скопируйте и вставьте диапазон Excel в Word с VBA

В обоих примерах мы начнем с образца электронной таблицы Excel. Этот образец представляет собой список заказов на покупку различных продуктов.

Допустим, вы хотите скопировать и вставить весь диапазон ячеек на этом листе в документ Word. Для этого вам нужно написать функцию VBA, которая будет запускаться при нажатии кнопки «Копировать в слово».

Выбрать разработчик из меню и выберите Вставить из группы управления на ленте. В раскрывающемся списке выберите элемент управления «Кнопка» в разделе «Элементы управления ActiveX».

Затем нарисуйте командную кнопку на правой стороне листа. Вы можете изменить заголовок на «Копировать в Word», щелкнув правой кнопкой мыши по кнопке и выбрав свойства, Измените текст заголовка, и вы можете использовать шрифт для обновления размера и стиля шрифта.

Запись: Если вы не видите разработчик в меню Excel, затем добавьте его. Выбрать файл, Опции, Настроить лентуи выберите Все команды из выпадающего списка слева. Тогда двигайся разработчик от левой панели вправо и выберите OK, чтобы закончить.

Напишите копию и вставьте код VBA

Теперь вы готовы начать писать код VBA. Чтобы начать, дважды щелкните новый Копировать в Word кнопка, чтобы открыть окно редактора кода.

Вы должны увидеть подпрограмму с именем Commandbutton1_Click (), как показано ниже.

Вы хотите скопировать каждый раздел кода ниже. Прежде чем приступить к написанию кода, для управления Word на вашем компьютере с помощью VBA необходимо включить справочную библиотеку Microsoft Word.

В редакторе кода выберите инструменты из меню и выберите Ссылки, В списке доступных ссылок прокрутите вниз и включите Библиотека объектов Microsoft Word 16.0,

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

Во-первых, вам нужно создать переменные и объекты, которые будут содержать диапазон и позволять вам управлять приложением Word.

Dim tblRange As Excel.Range
Dim WordApp As Word.Application
Dim WordDoc As Word.Document
Dim WordTable As Word.Table

Следующая строка кода выбирает определенный диапазон ячеек и сохраняет его в объекте диапазона Excel в VBA.

Set tblRange = ThisWorkbook.Worksheets("Sheet1").Range("A2:G44")

Затем вы хотите проверить, открыто ли приложение Word на компьютере. Для этого вы можете ссылаться на приложение Word, используя специальную ссылку «класс» с командой VBA GetObject. Если Word еще не открыт, то следующая строка запустит его с помощью функции CreateObject. Строка «On Error Resume Next» предотвращает любую ошибку первой функции GetObject (если Word еще не открыт) останавливать выполнение следующей строки в программе.

On Error Resume Next
Set WordApp = GetObject(class:="Word.Application")
If WordApp Is Nothing Then Set WordApp = CreateObject(class:="Word.Application")

Теперь, когда приложение Word запущено, вы хотите сделать его видимым для пользователя и активировать его для использования.

Далее вы хотите создать новый документ в приложении Word.

Set WordDoc = WordApp.Documents.Add

Наконец, вы скопируете и вставите диапазон ячеек в новую таблицу в документе Word.

tblRange.Copy
WordDoc.Paragraphs(1).Range.PasteExcelTable _
LinkedToExcel:=False, _
WordFormatting:=False, _
RTF:=False

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

Наконец, для работы с диапазонами Excel, которые шире, чем документ, вам нужно автоматически установить новую таблицу, чтобы она соответствовала полям вашего нового документа Word.

Set WordTable = WordDoc.Tables(1)
WordTable.AutoFitBehavior (wdAutoFitWindow)

И теперь вы сделали! Сохраните файл как файл Excel с поддержкой макросов (расширение .xlsm). Закройте редактор, снова сохраните исходный файл Excel, а затем нажмите командную кнопку, чтобы увидеть код в действии!

Записать результаты Excel в таблицу Word с помощью VBA

В следующем разделе вы напишете код VBA, который выполняет вычисления значений в Excel и записывает их в таблицу в Word.

Для этого примера мы возьмем данные на 10 строк, вычислим и запишем результаты в таблицу в документе Word. Кроме того, исходная таблица будет содержать четыре столбца, а код VBA извлечет первые десять строк данных из этого диапазона.

Как и в последнем разделе, мы рассмотрим каждый раздел за раз, чтобы вы понимали, что делает этот код и почему.

Сначала создайте переменные и объекты, которые будут содержать данные и позволят вам писать в приложение Word.

Dim tblRange As Excel.Range
Dim WrdRange As Word.Range
Dim WordApp As Word.Application
Dim WordDoc As Word.Document
Dim WordTable As Word.Table
Dim intRows
Dim intColumns
Dim strDate As String
Dim strItem As String
Dim intUnits As Variant
Dim intCost As Variant
Dim intTotal As Variant

Затем установите общее количество столбцов и строк, которые вы хотите прочитать из диапазона Excel.

intNoOfRows = 10
intNoOfColumns = 5

Повторите тот же код, что и в последнем разделе, который откроет Word, если он еще не открыт.

On Error Resume Next
Set WordApp = GetObject(class:="Word.Application")
If WordApp Is Nothing Then Set WordApp = CreateObject(class:="Word.Application")
WordApp.Visible = True
WordApp.Activate
Set WordDoc = WordApp.Documents.Add

Следующие четыре строки создают таблицу внутри вновь открытого документа Word.

Set WrdRange = WordDoc.Range(0, 0)
WordDoc.Tables.Add WrdRange, intNoOfRows, intNoOfColumns
Set WordTable = WordDoc.Tables(1)
WordTable.Borders.Enable = True

Наконец, следующий цикл выполнит эти действия:

Для каждой строки поместите дату заказа, позицию, единицы и стоимость в переменные
Рассчитайте стоимость за единицу времени (общую продажу) и сохраните ее в переменной
Для каждого столбца запишите значения в таблицу Word, включая рассчитанный общий объем продаж в последней ячейке.
Перейдите к следующему ряду и повторите процедуру выше

Вот как выглядит этот код:

For i = 1 To intNoOfRows
For j = 1 To intNoOfColumns
If j = 1 Then
strDate = tblRange.Cells(i + 1, j).Value
strItem = tblRange.Cells(i + 1, j + 1).Value
intUnits = Val(tblRange.Cells(i + 1, j + 2).Value)
intCost = Val(tblRange.Cells(i + 1, j + 3).Value)
intTotal = intUnits * intCost
End If
Select Case j
Case Is = 1
WordTable.Cell(i, j).Range.Text = strDate
Case Is = 2
WordTable.Cell(i, j).Range.Text = strItem
Case Is = 3
WordTable.Cell(i, j).Range.Text = intUnits
Case Is = 4
WordTable.Cell(i, j).Range.Text = intCost
Case Is = 5
WordTable.Cell(i, j).Range.Text = intTotal
Case Else
End Select
Next
Next

Функция «Ячейки» в первой части извлекает значения ячеек из Excel. Ячейки (x, y) означают, что он извлекает значение ячейки в строке x и столбце y.
Функция «Ячейка» в последней части записывает данные в ячейки таблицы Word, используя те же назначения строк и столбцов.

После сохранения и запуска этого кода VBA вы увидите результаты в только что созданном документе Word.

Как видите, не так сложно создать полезную автоматизацию между Excel и Word. Это просто вопрос понимания того, как работают различные «объекты», которые могут создавать и контролировать приложения Excel и Word на вашем компьютере.

Вэтой статье мы будем создавать программукоторая будет вставлять в документ Wordтаблицу. Ты конечно же скажешь: --Зачем мне нужна эта программа, если естьстандартная, ничем не хуже этой?, и будешькак всегда не прав. Ведь из этой статьи ты не только пополнишь знания в VBA, но и научишьсяписать и применять на практике ActiveXкомпоненты.

Еслиты совсем не знаком с программированием наVBA, то можешь прочитать мою статью "ИспользованиеUserForm в VBA". В ней дается краткоеописание языка, проводится параллель междуVBA и VB, их сходства и различия.

СозданиеActiveX компонента

Ура. Настала пора писать свой ActiveX компонент.Писать его мы будем на VisualBasic"e. И так,начнем. Запускаем Бейсик, перед вами появляется окно New Project, выбираем в нем"ActiveX Control". Перед вами должна появитсятакая форма:

Она похожа на обычнуюформу у которой свойство BorderStyle = 0 - None. Нуладно, идем дальше. Кидаем сюда текстовоеполе, свойство Text = 1, свойство Locked = True (чтобынельзя было ввести текст в это текстовоеполе). Нижеего ставим две кнопки. Свойство Caption 1 кнопки= "<", свойство Caption 2 кнопки = ">". Подгоняем размер User Control"a под наши кнопки итекстовое поле. Теперь надопереименовать наш проект и User Control . Дадим имя проекту Mein, UserContol"y"Spin". Ваш ActiveX долженвыглядеть примерно так:

Насталовремя писать программный код. Для началаобъявим переменную в разделе глобальныхобъявлений ActiveX контрола (это послестроки Option Explicit).

После того как мы ее объявили переходим ксобытию Initialize User Control"a, и вводим тудаследующий код:

Private Sub UserControl_Initialize()
Number = 1
End Sub

Здесьмы присваиваем переменной Number значение1. Теперь переходим к событию Click первойкнопки и вводим туда такой код:

Private Sub Command1_Click()
If Number > 1 Then
Number = Number - 1
Text1.Text = Number
End If
End Sub

Принажатии на нее мы проверяем переменную Number,если она больше 1 вычитаем из нее 1, втекстовое поле вставляем значение этойпеременной. В событие Click второй кнопкивставляем код:

Private Sub Command2_Click()
If Number < 100 Then
Number = Number + 1
Text1.Text = Number
End If
End Sub

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

Все,с созданием ActiveX закончено, осталось толькооткомпилировать его в ОСХ файл. Для этогозаходим File нажимаем на Make Mein.ocx,выбираем для этого подходящую директорию инажимаем OK.

VisualBasic при компиляции регистрирует OCX"сы всистеме, поэтому этот файл будетавтоматически добавлен в список ActiveX"овпрограмм пакета MS Office. А чтобы использоватьего в проектах VisualBasic"a придется нажать Project> Components > Browse. , открыть папку в которойнаходится нужный OCX и кликнуть по нему 2раза.

Созданиепрограммы для WORD"A

Кидаемдва таких инструмента на нашу форму, одинпод другим, слева от них ставим лейблы, вверхней метке свойство Caption = "Числостолбцов", в нижней "Число строк".Ниже всего этого ставим две кнопки, уверхней свойство Name = cmdNewTables, свойство Caption = "Создать таблицу", у нижнейсоответственно Name = "cmdCancel", Caption = "Отмена". С права можно поместить лейблс описанием программы. У вас должнополучится примерно так:

Синтерфейсом программы разобрались, а это,поверь мне читатель, совсем не мало.Приступаем к завершающей стадии нашейстатьи, пишем программный код нашейпрограммы. Он совсем не сложный благодарятому что мы используем ActiveX компоненты. Итак, начнем, перед этим слегка закатимрукава, так на всякий случай, чтобы немешали :))). Кликаем два раза по кнопке cmdNewTables и вносим туда такой код:

Private Sub cmdNewTables_Click()
Dim myrange
Set myrange = ActiveDocument.range(0, 0)
Selection.Tables.Add range:=myrange, NumRows:=Spin2.Number,_ NumColumns:=Spin1.Number
Selection.Collapse Direction:=wdCollapseEnd
Unload Me "выгружае наше окно
End Sub

Ондостаточно прост и я не буду заострятьвнимание на каждой строке программы (еслиты что-то не понятно можешь посмотретьсправку, в ней все подробно описано), а лишьобъясню подключение нашего ActiveX компонента,вернее двух его экземпляров.

Дляэтого еще раз всмотритесь в строку Selection.Tables.Addrange:=myrange, NumRows:=Spin2.Number, NumColumns:=Spin1.Number .Вспомните переменную Number которую мыобъявляли в области глобальных объявленийнашего ActiveX компонента. Когда мывставляем таблицу в документ намнеобходимо указать количество строк (NumRows) иколичество столбцов (NumColumns) в создаваемойтаблице, для этого мы использовали значениепеременной Number. Теперь напишем кодкнопки cmdCancel, он очень прост:

Private Sub cmdCancel_Click()
Unload Me "выгрузить это окно
End Sub

Осталсяпоследний штрих - вынести нашу программу напанель инструментов MS Word"а. Для этогосоздаем новый модуль(он создаетсяаналогично форме, даем ему имя modTables. Ивписываем в него следующий код:

Послечего закрываем редактор VBA и переходим вокно Word"a. Заходим в Сервис>Настройка>переходим в раздел "Команды" в окне "Категории"находим "Макросы" и в окне "Команды"находим (если вы все сделали как былонаписано выше) Normal.modTables.modTablesCount . Щелкаем поэтой надписи и не отпуская левой кнопкимыши тащим ее на удобную для вас панельинструментов и отпускаем. Получившуюсякнопку можно отредактировать, поменять вней надпись, использовать картинку. Дляэтого достаточно нажать правую кнопку мыши,над нашей кнопкой, главное чтобы при этойоперации было открыто окно "Настройка"иначе ничего не получится.

Послередактирования кнопки закрываем окно "Настройка"и жмем по новой кнопке. Если все сделалиправильно, должно открыться окно которое мыделали выше.

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

Вставить плавающую таблицу в Excel с кодом VBA

У вас нет прямого способа вставить плавающую таблицу, поэтому вам нужно преобразовать данные таблицы в изображение, а затем применить код для его завершения, пожалуйста, сделайте следующее:

1. Выберите таблицу данных, которую вы хотите сохранить видимой, затем щелкните Копировать > Копировать как изображение под Главная вкладку, см. снимок экрана:

2. В выскочившем Копировать картинку диалоговое окно, выберите Как показано на экране из Внешний вид , и выберите Картина под Формат разделом, щелкните OK чтобы закрыть диалоговое окно, а затем щелкните ячейку для вывода изображения, наконец, нажмите Ctrl + V чтобы вставить это изображение, см. снимок экрана:

3. После преобразования таблицы в изображение щелкните правой кнопкой мыши вкладку листа, содержащую изображение таблицы, которое вы хотите всегда видеть, и выберите Просмотреть код из контекстного меню, а в открывшемся Microsoft Visual Basic для приложений окна, скопируйте и вставьте следующий код в пустой модуль:

Код VBA: вставить плавающую таблицу в excel:

Внимание: В этом коде Picure1 - это имя созданного изображения, измените его по своему усмотрению.

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

Вставить плавающее текстовое поле в Excel с кодом VBA

Чтобы вставить плавающее текстовое поле, вот также код VBA для решения этой задачи.

1. Во-первых, вы должны вставить текстовое поле из разработчик вкладку нажмите разработчик > Вставить > Текстовое поле (элемент управления ActiveX), а затем нарисуйте текстовое поле и введите информацию в текстовое поле по мере необходимости. Смотрите скриншот:

2. Затем щелкните правой кнопкой мыши вкладку листа, содержащую текстовое поле, которое вы хотите всегда видеть видимым, и выберите Просмотреть код из контекстного меню, а в открывшемся Microsoft Visual Basic для приложений окна, скопируйте и вставьте следующий код в пустой Модуль:

Код VBA: вставить плавающее текстовое поле в Excel:

Внимание: В этом коде TextBox1 это имя текстового поля, которое вы вставили, измените его по своему усмотрению.

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

Развернуть или свернуть окно рабочего листа

Чтобы развернуть окно рабочего листа, можно освободить больше места для пользователей, в Excel вы можете развернуть окно рабочего листа до максимума, скрыв ленту, строку формул или строку состояния. Kutools for Excel's Рабочая зона может помочь вам скрыть ленту одним щелчком мыши. Загрузите бесплатную пробную версию Kutools for Excel прямо сейчас!

Основным преимуществом UserForm является то, что вы можете сэкономить время, которое вы тратите на то, как и как информация должна быть заполнена.

Создание пользовательской формы

Чтобы создать пользовательскую форму, выполните следующие действия:

Нажмите вкладку РАЗРАБОТЧИК на ленте.
Нажмите Visual Basic. Откроется окно Visual Basic для книги.
Нажмите Вставить,
Выберите UserForm из выпадающего списка.

Пользовательская форма появляется в правой части окна.

Понимание пользовательской формы

Вы находитесь в режиме дизайна сейчас. Вы можете вставить элементы управления в пользовательскую форму и написать код для соответствующих действий. Элементы управления доступны в панели инструментов. Свойства UserForm находятся в окне свойств. UserForm1 (подпись UserForm) указывается в разделе «Формы» в проводнике проектов.

Изменения отражаются в пользовательской форме, свойствах и проводнике проекта.

Элементы управления в панели инструментов

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

Элементы управления формой

Вы можете запускать макросы с помощью элементов управления формы. Вы можете назначить существующий макрос элементу управления, или написать или записать новый макрос. При нажатии на элемент управления макрос. Вы уже научились вставлять командную кнопку из элементов управления формы на листе для запуска макроса. Однако эти элементы управления не могут быть добавлены в пользовательскую форму.

Элементы управления ActiveX

Элементы управления ActiveX могут использоваться в пользовательских формах VBA. Элементы управления ActiveX имеют широкие свойства, которые можно использовать для настройки их внешнего вида, поведения, шрифтов и других характеристик.

Указатель
этикетка
Текстовое окно
Поле со списком
ListBox
CheckBox
OptionButton
Рамка
Кнопка-переключатель
CommandButton
TabStrip
MultiPage
Полоса прокрутки
в полях ввода
Образ

В следующих нескольких разделах вы поймете эти элементы управления и MsgBox. Затем вы сможете выбрать, какой из этих элементов управления требуется для разработки пользовательской формы.

этикетка

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

Текстовое окно

Вы можете использовать TextBox в виде прямоугольника для ввода, просмотра или редактирования текста. Вы также можете использовать TextBox в качестве статического текстового поля, которое представляет информацию только для чтения.

Список

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

Вставьте ListBox в пользовательскую форму.
Нажмите на список.
Введите ProjectCodes для Name в окне свойств ListBox.

Вы можете выбрать один из этих типов списков в окне свойств.

Щелкните правой кнопкой мыши на пользовательской форме.
Выберите View Code из выпадающего списка. Откроется окно кода UserForm.
Нажмите Инициализировать в правом верхнем углу окна кода.
Введите следующее в Private Sub UserForm_Initialize ().

Перейдите на вкладку «Выполнить» на ленте.
Выберите Run Sub / UserForm из выпадающего списка.

Далее вы можете написать код для действий по выбору элемента в списке. В противном случае вы можете просто отобразить выбранный текст, как в случае заполнения кода проекта в отчете.

Поле со списком

Вы можете использовать ComboBox, который объединяет текстовое поле со списком для создания выпадающего списка. Поле со списком является более компактным, чем поле со списком, но требует, чтобы пользователь щелкнул стрелку вниз, чтобы отобразить список элементов. Используйте поле со списком, чтобы выбрать только один элемент из списка.

Читайте также: