Tooprogram.ru

Компьютерный справочник
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Vba создать файл excel

Макрос на VBA Excel – Формируем документы по шаблону

Очень часто бывает такое, что нужно сформировать документы по определенному шаблону, на основе каких-то данных, например, по каждому сотруднику или по каждому лицевому счету. И делать это вручную бывает достаточно долго, когда этих самых сотрудников или лицевых счетов много, поэтому сегодня мы рассмотрим примеры реализации таких задач в Excel с помощью макроса написанного на VBA Excel.

Немного поясню задачу, допустим, нам необходимо сформировать какие-то специфические документы по шаблону массово, т.е. в итоге их получится очень много, как я уже сказал выше, например, по каждому сотруднику. И это нужно сделать непосредственно в Excel, если было бы можно это сделать в Word, то мы бы это сделали через «Слияние», но нам нужно именно в Excel, поэтому для этой задачи мы будем писать макрос.

Мы с Вами уже выгружали данные по шаблону через клиент Access из базы MSSql 2008 в Word и Excel вот в этой статье — Выгрузка данных из Access в шаблон Word и Excel. Но сейчас допустим, у нас данные располагаются в базе, в клиенте которой нельзя или слишком трудоемко реализовать такую задачу, поэтому мы просто выгрузим необходимые данные в Excel и на основе таких данных по шаблону сформируем наши документы.

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

Напомню, что на данном сайте тема VBA Excel уже затрагивалась, например, в материале – Запрет доступа к листу Excel с помощью пароля

И так приступим!

Пишем макрос на VBA Excel по формированию документов

Реализовывать нашу задачу будем на примере «Электронной карточке сотрудника» (я это просто придумал:), хотя может такие и на самом деле есть), т.е. документ в котором хранится личные данные сотрудника вашего предприятия, в определенном виде, именно в Excel.

Примечание! Программировать будем в Excel 2010.

И для начала приведем исходные данные, т.е. сами данные и шаблон

Данные.

Лист, на котором расположены эти данные так и назовем «Данные»

Шаблон.

Лист, на котором расположен шаблон, тоже так и назовем «Шаблон»

Далее, нам необходимо присвоить имена полей для вставки, так более удобней к ним обращаться чем, например, по номеру ячейки.

Это делается очень просто, выделяете необходимую ячейку или диапазон, и жмете правой кнопкой мыши и выбираете «Присвоить имя», пишите имя ячейки и жмете «ОК»

Свои поля я назвал следующим образом:

  • ФИО – fio;
  • № — number;
  • Должность – dolgn;
  • Адрес проживания – addres;
  • Тел. № сотрудника – phone;
  • Комментарий – comment.

Код макроса на VBA Excel

Для того чтобы написать код макроса, открывайте на ленте вкладку «Разработчик», далее макросы.

Примечание! По умолчанию данной вкладке в Excel 2010 может и не быть, чтобы ее отобразить нажмите правой кнопкой по ленте пункт меню «Настройка ленты»

затем, в правой области поставьте галочку напротив пункта «Разработчик»

После вкладка разработчик станет отображаться на ленте.

Далее, когда Вы откроете вкладку разработчик и нажмете кнопку «Макросы» у Вас отобразится окно создания макроса, Вы пишите название макросы и жмете «создать».

После у Вас откроется окно редактора кода, где собственно мы и будем писать свой код VBA. Ниже представлен код, я его как обычно подробно прокомментировал:

Теперь осталось выполнить этот макрос, для этого откройте вкладку разработчик->макросы->выполнить наш макрос:

и после выполнения у Вас в той же папке появится вот такие файлы

Вот с таким содержимым:

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

Макрос создания текстовых файлов по таблице Excel

Макрос предназначен для создания текстовых файлов в кодировке UTF-8.

Исходными данными является таблица Excel из 12 столбцов.

Сначала, макрос создаёт папку для будущих текстовых файлов.
Папка создаётся в том же каталоге, где расположена книга Excel.

Далее, для каждой строки таблицы, макрос формирует подпапку,
используя в качестве её названия текст из 7-го столбца таблицы.

И потом, когда папка для файла создана, макрос создаёт текстовый файл с содержимым из 10 столбца таблицы,
и сохраняет его под именем, взятым из второго столбца той же таблицы Excel.
После создания файла, у него меняется кодировка на UTF-8 (изначально, при создании, файлы имеют кодировку Unicode)

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

Пример макроса смотрите в прикреплённом файле.

Код макроса, создающего папки, подпапки, и текстовые файлы по данным из таблицы Excel:

ВложениеРазмерЗагрузкиПоследняя загрузка
prays.xls38.5 КБ2331 год 47 недель назад
  • 52363 просмотра

Комментарии

Спасибо автору за идею макроса. Вы не могли бы подсказать, а можно ли его усложнить, чтобы перенести все строки таблицы Эксель в виде колонок в текстовые файлы. (те чтобы каждая строка Эксель файла переносилась бы в виде столбца в новый текстовый файл) Полагаю, что править нужно следующую часть макроса ts.Write Trim(arr(i, 10)) путем использования Application.Transpose применительно к строке, мучаюсь никак не могу правильно построить команду.

Здравствуйте, Наталья
Ограничений по текстовым файлам никаких нет
Если файл пустой, — скорее всего, макрос не все данные с листа собирает для вывода в файл.
Если сами не разберётесь, — могу написать макрос под заказ.

Здравствуйте пользуюсь вашим макросом, подскажите, существуют ли ограничения при формировании текстового файла? Столкнулась с тем что файл после формирования пуст или вставлена часть данных которые идут в начале, может ли это быть из-за того что в ячейках excel содержатся большие количества символов, около 240 в каждой и ячеек 26 шт? В чем еще может быть причина?

как сделать перенос строк, подскажите плиз

Здравствуйте, Никита.
Где почитать — не знаю (я в гугле обычно ищу, и читаю)
Можно поискать похожие макросы в инете, и доработать под свою задачу.
Можно заказать написание макроса, — сделаем под заказ.

Добрый день!
Подскажите пожалуйста, где почитать инфу для решения задачи:
Есть таблица Excel #1, количество строк около 2000 тысяч.
Нужно чтобы автоматически создавались отдельные файлы Excel по каждой строке из таблицы Excel #1.

Здравствуйте, Александр
Да, могу и такой макрос сделать, — оформляйте заказ через сайт с примером исходного файла и примером результата.

Здравствуйте, макрос очень полезен! Спасибо!

А не могли бы вы такой же макрос сделать, но только чтобы файлы создавались по столбцам, а имя бралось из первой строки. То есть все что написано в столбце А2:A100, попадало в файл с именем как в А1. И ещё бы хорошо было, чтобы макрос вставлял до первой пустой ячейки.

Наталья, можно сделать как угодно, — макросы могут всё)
Оформляйте заказ, — сделаем.

Подскажите, а мог бы макрос формировать файлы с данными не из одной строки Excel, а из нескольких опираясь на общий признак, например по группам? Например: есть группа чай и в нее входят поля из четырех строк Excel, нужно чтобы все они присутствовали в одном файле и над ними первой строкой была бы шапка «Шапка+название группы». Файл Excelя будет отсортирован по этому признаку, в нем не будет более 500 строк.
Пример:
«Шапка чай »
супер чай для всех
супер пупер чай для каждого
быстрый чай
не очень быстрый чай

возможно неверно выразился. я все экспериментировал с Filename$ = Folder$ & Trim(arr(i, 2)) & «.txt»
для того, чтобы из столбца и уникальной ячейки выходило с номером. Т.е. имея данные столбце: товар А, товар Б, товар В получать на выходе файлы типа: 1.товар А, 2.товар Б, 3.товар В
как такое провернуть?
буду благодарен за ответ)

Дмитрий, вместо строки

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

Set ts = FSO.CreateTextFile(Filename$, True, True)
txt = Trim(arr(i, 10)) ‘Данные в файл из ячейки 10-го столбца
txt = Replace(txt, vbNewLine, vbCrLf) ‘ заменяем Последовательность символов перехода на новую строку на Сочетание символов возврата каретки и перевода строки.
txt = Replace(txt, vbLf, vbCrLf) ‘ заменяем Символ перевода строки на Сочетание символов возврата каретки и перевода строки.
ts.Write txt ‘ Пишем текстовое значение в файл
ts.Close

Текстовый формат таблицы (он же CSV) не поддерживает переносы строк внутри значений.
Можно, например, заменять символы переводы строки внутри значений на какой-нибудь символ типа точки с запятой.

для этого замените код

Огромное тебе спасибо мил человек! Все работает изюмительно!
Подскажи только, как сделать так, чтобы данные как многострочный текст сохранялись, ибо в ячейке текст многострочный http://joxi.ru/xAeGVebtY6MJVm. Иначе он их склеивает
http://joxi.ru/J2bej5bc4Yg83m
Заранее благодарю!

Вопрос снят. Справилась.

Спасибо за макрос! А если мне надо что бы
ts.Write Trim(arr(i, 10)) ‘ данные в файл — из ячейки 10-го столбца
несколько раз вставлялись в файл С НОВОЙ СТРОКИ, т.е. визуально располагаясь друг под другом?
Есть такая возможность?

Добрый день. Очень нужна помощь. У меня в Excel в качестве разделителя целой и дробной части стоит точка. Но когда я посредством макроса экспортирую данные в txt формат, точка становится запятой. Как это можно предотвратить?
Дописано: Я понимаю, что можно в блокноте заменить «.» на «,», но у меня куча этих файлов, так что это не вариант. Надо как-то в макросе это изобразить.
Спасибо.

В коде написано:

Соответственно, если в некоторых строках в 10-м столбце пусто — то и файл пустой будет создан

спасибо за макрос, работает, создает файлы около 100 шт
но первые 10 корректные, а остальные пустые.
Можете подсказать как исправить?

Василий, если у вас мои макросы не работают, — это только потому, что вы их не можете правильно применить
У меня всё всегда работает на отлично)

Если даже для вас ценность этого макроса невелика, — то я, тем более, не вижу смысла тратить на него свое время.
Вы мне хотите сказать, что этот макрос нужнее мне, чем вам?))
PS: А трафика мне на сайте хватает)

1. я не то чтобы не могу в них разобраться, те макросы, что вы предложили попросту не работают у меня, ни один, ни второй. Что получилось у меня после их применения я выложил на Яндекс.Диск — это даже не критика, а просто так скажем «сообщение об ошибке администратору»

2. задача, с которой я к вам обратился относится к категории «удобная добавка», то есть то же самое делается вручную за 15 секунд, то есть ценность её, скажем так, невелика. Просто реализовав её вы могли бы привлечь дополнительный трафик к себе на сайт, то есть выгода для вас тоже имеется. А вот платить за неё я не вижу никакого смысла — и так, честно говоря, жаль времени, потраченного на поиски её решения

Если вы сами не можете разобраться в моих макросах, — не вопрос, я могу сам все сделать как надо, — но это уже не бесплатно
Если хотите бесплатно, — обратитесь на форумы по Excel, выложите там свои наработки, — и вам помогут.

мой предыдущий ответ видимо оказался слишком мудрёным 🙂 а жаль.

спасибо за желание помочь, но боюсь, что это что-то не то
если я правильно всё понимаю, то приведённый вами код состоит из 2 частей:

1. макрос, который если его запустить в открытом в Excel файле .csv перекодирует данный файл в utf-8 без BOM. Не знаю почему, но у меня он не работает.

Делаю ровно следующее — есть простенькая таблица в Excel, сохраняю её как .csv, запускаю макрос, файл закрывается. Если его после этого открывать хоть Excel хоть Notepad++, то там абракадабра в одну строчку, при этом Notepad++ как показывал в оригинальном файле кодировку ANSI, так и показывает его в новом файле.

2. функция, которая перекодирует заданный .csv в нужную кодировку. То есть допустим можно сохранить свою таблицу Excel в .csv, указать путь к данному файлу на диске и функция поменяет кодировку данного файла

во-первых, непонятно что указывать в аргументе SourceCharset
во-вторых, если SourceCharset не указыавать, то получается абракадабра в одну строчку как в случае выше, хотя тут уж Notepad++ соглашается, что кодировка стала utf-8 без BOM
в-третьих, изначальная задача звучала совсем по-другому, а именно

«есть табличка Excel в формате, допустим .xlsx. При нажатии на макрос, он сохраняет данную табличку в той же папке, но в формате .csv, причём .csv этот в кодировке utf-8 без BOM» (собственно, возможность указать кодировку при сохранении .csv есть в OpenOffice Calc, но любимый Excel почему-то не может себе это позволить. )

когда я искал, как это можно реализовать, все отвечавшие либо предлагали делать это вручную, либо давали ссылку на вашу статью http://excelvba.ru/code/Encode но эта статья не содержит в себе решения на задачу выше, она только позволяет перекодировать уже готовый .csv — в принципе получается всё тот же ручной способ

в общем, думаю, если получится реализовать вариант как в OpenOffice Calc или вариант проще, то есть без выбора папки для сохранения (можно отдельную статью этому посвятить, кстати), то приток дополнительных посетителей из нуждающихся вебмастеров к вам на сайт обеспечен. А сайт, кстати, очень крутой, прям не верится, что он российского происхождения.

дабы не быть голословным по поводу не рабочести макросов прилагаю все использованные и получившиеся материалы http://yadi.sk/d/Svq04uDVPXHdv

Здравствуйте, Василий.
Макрос и кнопка при всём желании в CSV не сохранятся, — CSV это текстовый формат.

Макрос можно упростить, — если сохранять весь файл в CSV
Если вы вручную сохранили файл в формате CSV, — в макросе достаточно будет 3 строк:
(сам макрос можно сохранить в личной книге макросов, и запускать по нажатию горячей клавиши)

Здравствуйте! Чувствую, что в этом макросе есть всё что нужно для моей задачи, но как его вычленить знаний не хватает.

А требуется очень простое, но с другой стороны очень нужное для вебмастеров действие (в инете обыскался — куча вопрошающих, ноль отвечающих): сохранить текущий .xls в .csv в кодировке «utf-8 без ВОМ», а нужно это для импорта на сайт данных из excel.

Каждый раз менять кодировку csv в блокноте утомительно, а так будет быстрее (а я правильно понимаю, что макрос и кнопка его запуска сами удалятся из csv?)

Удалось сделать то, что мне нужно благодаря этому вашему макросу http://excelvba.ru/code/ExtendArray Объединил оба проекта, получил пусть неоптимизированный, зато рабочий макрос 🙂 Результат здесь: http://zalil.ru/34769096 Спасибо за ваши работы, очень выручили 🙂

Уважаемый администратор! Спасибо за помощь, только это не совсем то, что мне нужно 🙂 Мне нужно писать данные из первого столбца не в название файла, а в сам файл. Допустим у меня в первом столбце имеются строки: 34,35,36. В во втором столбце соответственно строки 11, 12, 12. На выходе я хотел бы получить соответственно 2 файла txt: Первый будет называться «11» и в нем будут данные: «34», а второй будет называться «12» и в нем будут данные: «35/36». Заранее спасибо за помощь! 🙂

Замените в коде эту строку

PS: знак «/» нельзя использовать в имени файла, — потому разделителем поставил символ «-«

Немного модифицировал ваш макрос под себя, но кое-что не могу сделать. У меня имеется 2 столбца с числами. Мне нужно формировать текстовые файлы с названиями из 2 столбца, в них писать данные из первого столбца. Это делается хорошо. Но если во 2 столбце число совпадает с предыдущем, то создается файл с таким же названием и он перезаписывает прошлый файл. А мне нужно чтобы туда дописывалось число из первого столбца через знак «/». Например 123456/789012. Как это сделать?

Такому не учат нигде — можно только самому научиться, если есть интерес к программированию.

Супер. Это просто праздник какой-то! Где этому учат? Всё работает как нужно.

Excel VBA: Создать файл xls, вставить в него данные, закрыть и .

Чтобы отправить ответ, вы должны войти или зарегистрироваться

Сообщения 4

1 Тема от -red- 2012-02-24 15:51:57

  • -red-
  • Разработчик
  • Неактивен
  • Рейтинг : [ 0 | 0 ]

Тема: Excel VBA: Создать файл xls, вставить в него данные, закрыть и .

. обратиться к нему как к БД через ADO.

Проблема в том что создаваемый файл xls не должен появляться на экране. Кострукция типа:

Выводит на экран файл и всё тут

Для ясности напишу зачем я это делаю.
Есть задача отобразить данные из MS SQL сервера, при условиях:
1. Доступ к БД через интернет (достаочно медленный) — количество запросов должно быть сведено к минимуму.
2. Данные в БД хранятся в «сыром» т.е. бинарном виде — требуется преобразовать эти данные в привычную человеку форму.
3. В один из столбцов в БД пишется дата (формат DateTime) нужно вывести 3 вида отчета — дневной, месячный и годовой.

Дневной вид отчета делается легко: запрос с between, на лету из RecordSet данные конвертирую в человеческий формат. А вот месячный и годовой, решил сделать так: Сначала такой же запрос как и в дневном (только период дат побольше), потом копирование всего этого хозяйства в локальную таблицу, потом обращение к этой таблице как к БД через ADO с группировкой по месяцу или году и вуаля.
. но тут блин выскакивает на экран новый файл и как с ним боротться не знаю

Сейчас буду копать в сторону встроенных процедур на сервере с БД

2 Ответ от JSman 2012-02-24 21:42:13

  • JSman
  • Администратор
  • Неактивен
  • Рейтинг : [ 33 | 0 ]

    Re: Excel VBA: Создать файл xls, вставить в него данные, закрыть и .

    Может быть стоит запускать это из WSH?

    3 Ответ от Xameleon 2012-02-25 00:05:35

    • Xameleon
    • Модератор
    • Неактивен
    • Рейтинг : [ 36 | 0 ]

      Re: Excel VBA: Создать файл xls, вставить в него данные, закрыть и .

      1) А вы книгу же не закрываете. По крайней мере в коде я этого не вижу.

      2) Может стоит всё таки оптимизировать сам запрос к серверу или страницу выдачи информации ? На чём серверная часть собрана PHP / ASP ? Или её нет ? И все обращения делаются через механизмы самого SQL сервака ?

      3) Думаю стоит связаться по ICQ. Удобнее будет. 252505765

      Атанас Йонков Блоггер, Веб-разработчик
      yonkov.atanas@gmail.com

      В этом уроке я покажу Вам самые популярные макросы в VBA Excel, которые вы сможете использовать для оптимизации своей работы. VBA — это язык программирования, который может использоваться для расширения возможностей MS Excel и других приложений MS Office. Это чрезвычайно полезно для пользователей MS Excel, поскольку VBA может использоваться для автоматизации вашей работы и значительно увеличить Вашу эффективность. В этой статье Вы познакомитесь с VBA и я вам покажу некоторые из наиболее полезных, готовых к использованию примеров VBA. Вы сможете использовать эти примеры для создания собственных скриптов, соответствующих Вашим потребностям.

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

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

      Table of Contents

      Как включить макросы в Excel

      В Excel нажмите комбинацию клавиш alt + F11. Это приведет вас к редактору VBA в MS Excel. Затем щелкните правой кнопкой мыши папку Microsoft Excel Objects слева и выберите Insert => Module. Это место, где сохраняются макросы. Чтобы использовать макрос, вам нужно сохранить документ Excel как макрос. Из табуляции File => Save as, выберите Save as macro-enabled Workbok (расширение .xlsm) Теперь пришло время написать свой первый макрос!

      1. Копирование данных из одного файла в другой.

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

      2. Отображение скрытых строк

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

      3. Удаление пустых строк и столбов

      Пустые строки в Excel — может быть проблемой для обработки данных. Вот как избавиться от них:

      4. Нахождение пустых ячеек

      13. Создание сводной таблицы

      14. Отправка активного файла по электронной почте

      Мой любимый код VBA. Он позволяет вам прикреплять и отправлять файл, с которым вы работаете, с предопределенным адресом электронной почты, заголовком сообщения и телом сообщения! Сначала Вам нужно сделать референцию в Excel на Microsoft Outlook (в редакторе Excel VBA, нажмите tools => references и выберите Microsoft Outlook).

      15. Вставка всех графиков Excel в презентацию PowerPoint

      Очень удобный макрос, который позволяет вам добавлять все ваши графики Excel в презентацию Powerpoint одним щелчком мыши:

      16. Вставка таблицы Excel в MS Word

      Таблицы Excel обычно помещаются внутри текстовых документов. Вот один автоматический способ экспорта таблицы Excel в MS Word:

      17. Извлечение слов из текста

      Мы можем использовать формулы, если хотим извлечь определенное количество символов. Но что, если мы хотим извлечь только одно слово из предложения или диапазон слов в ячейке? Для этого мы можем сами создать функцию Excel с помощью VBA. Это одна из самых удобных функций VBA, поскольку она позволяет создавать собственные формулы, которые отсутствуют в MS Excel. Давайте продолжим и создадим две функции: findword() и findwordrev():

      Отлично, мы уже создали две новые функции в Excel! Теперь попробуйте использовать их в Excel. Функция = FindWordRev (A1,1) берет последнее слово из ячейки A1. Функция = FindWord (A1,3) берет третье слово из ячейки A1 и т. Д.

      18. Защита данных в MS Excel

      Иногда мы хотим защитить данных нашего файла, чтобы только мы могли его изменять. Вот как это сделать с VBA:

      Поздравления! Поскольку вы все еще читаете это, вы действительно заинтересованы в изучении VBA. Как вы уже сами видели, язык программирования VBA чрезвычайно полезен и может сэкономить нам много времени. Надеюсь, вы нашли эту информацию полезной и использовали ее, чтобы стать мастером MS Excel, VBA и компьютерных наук в целом.

      Читать еще:  Value в excel что означает
Ссылка на основную публикацию
Adblock
detector