Fruitsekta.ru

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

Excel application saveas

Ron de Bruin
Excel Automation

Use VBA SaveAs in Excel 2007-2016

Information

You see a lot of old SaveAs code that does not specify the FileFormat parameter. In Excel versions before Excel 2007, code without this parameter will not cause too many problems because Excel will use the current FileFormat of the existing file and the default FileFormat for new files is a (xls) in 97-2003 because there are no other Excel file formats before Excel 2007.

But because there are so many new file formats in Excel 2007-2016, we shouldn’t use code like this that does not specify the FileFormat parameter. In Excel 2007-2016, SaveAs requires you to provide both the FileFormat parameter and the correct file extension.

For example, in Excel 2007-2016, this will fail if the ActiveWorkbook is not an xlsm file
ActiveWorkbook.SaveAs «C:ron.xlsm»

This code will always work
ActiveWorkbook.SaveAs «C:ron.xlsm», fileformat:=52
‘ 52 = xlOpenXMLWorkbookMacroEnabled = xlsm (with macro’s in 2007-2016)

These are the main file formats in Excel 2007-2016, Note: In Excel for the Mac the values are +1

51 = xlOpenXMLWorkbook (without macro’s in 2007-2016, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro’s in 2007-2016, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007-2016 with or without macro’s, xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007-2016, xls)

Note: I always use the FileFormat numbers instead of the defined constants in my code so that it will compile OK when I copy the code into an Excel 97-2003 workbook (For example, Excel 97-2003 won’t know what the xlOpenXMLWorkbookMacroEnabled constant is).

Examples

Below are two basic code examples to copy the ActiveSheet to a new Workbook and save it in a format that matches the file extension of the parent workbook. The second example use GetSaveAsFilename to ask you for a file path/name. Example 1 you can use in Excel 97-2016 , Example 2 you can use in Excel 2000-2016.

If you run the code in Excel 2007-2016 it will look at the FileFormat of the parent workbook and save the new file in that format. Only if the parent workbook is an xlsm file and if there is no VBA code in the new workbook it will save the new file as xlsx. If the parent workbook is not an xlsx, xlsm or xls then it will be saved as xlsb.

If you always want to save in a certain format you can replace this part of the macro:

With one of the one liners from this list

FileExtStr = «.xlsb»: FileFormatNum = 50
FileExtStr = «.xlsx»: FileFormatNum = 51
FileExtStr = «.xlsm»: FileFormatNum = 52

Or maybe you want to save the one worksheet workbook to csv, txt or prn.
(you can use this also if you run the code in Excel 97-2003)

FileExtStr = «.csv»: FileFormatNum = 6
FileExtStr = «.txt»: FileFormatNum = -4158
FileExtStr = «.prn»: FileFormatNum = 36

Сохранение документов Excel в PDF на сервере

Не так давно появилась задача создать простой сервис по созданию PDF отчетов на основе офисных документов для интранета. И вроде бы все просто, но вот с сохранением Excel в PDF возникли проблемы. Интересно? Прошу под кат.

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

Мои наработки использовали сom объекты excel.application и метод saveAs, это прекрасно работало, пока требовалось взять обычный, красивый документ и сделать из него pdf, но в данном случае файлы были нет так просты.

Во-первых, документы предполагаются трех форматов — xls, xlsx и xml. Во вторых все документы содержат макросы, а некоторые ссылки на другие документы. В третьих они содержат кучу листов, и перекрестные ссылки между листами. Не нужные для отчета листы делаются скрытыми, а на самих листах в каше вспомогательных цифр отчетная информация выделялась областью печати. Нужно ли говорить, что saveAs игнорирует все это богатство и на выходе после танца с бубнами получаем абсолютно не читаемую картину.

Читать еще:  Excel не обновляет значение в ячейках

Здесь я думаю необходимо сделать лирическое отступление и пояснить, почему с файлами творится такая неразбериха. Я работаю в очень крупной организации, бок обок с кучей бабушке пенсионного возраста. Они не могут даже сделать текст в ячейке жирным, но прекрасно могут говорить начальству о том, что они не «программисты» и не должны это уметь. Начальства у нас тоже много, и в силу жалости к бабушкам или своей компьютерной безграмотности, а быть может, по велению звезд жалобы бабушек поощряются, и все попытки разгрести бардак в документах пресекается.

Вернемся к нашим баранам. В выше упомянутой статье был предложен вариант конвертации «Как вижу» с использованием open office, этот вариант меня не устроил в силу ветреного отношения OO к MS. Некоторые документы действительно открывались в нем корректно, но чаще всего содержимое ехало еще до конвертации.

Был еще третий вариант. Печатать документы на виртуальный принтер, но я решил, что этот вариант я приберегу на самый крайний случай, так это костыль.

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

Он начисто игнорировал области печати, а без них получалась каша. Я гуглил, оставлял вопросы на множестве форумах, в том числе и на англоязычных. Ответа не было. Я даже перенес код статьи в C#, но результат не изменился.

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

Путем проб и ошибок, танцев с бубнами и увлекательным чтением гугла я выяснил, области печати игнорируется при вызове этого метода и печати в php, C#, но почему то при использовании VBScript все работает как надо. Чем это вызвано я, к сожалению, так и не выяснил.

И так, было решено из PHP открывать VBS скрип и уже из него преобразовывать excel в pdf. Да, это тоже костыль, но не такой неуклюжий как виртуальный принтер.

Вот получившийся скрипт:

Dim XL
Dim XLWkbk
Dim ObjArgs
Dim paramSourceBookPath
Dim paramExportFilePath

set objargs = wscript.arguments
if objArgs.count exec(APPLICATION_SCRIPT_FOLDER.’\excel.vbs C:\tmp\test.xlsx C:\tmp\test.pdf»);

В сухом остатке мы имеем не совсем красивый, но 100% рабочий метод по преобразованию Excel в PDF, который гарантирует результат «Как на печати» без подводных камней.

Как на VBA сохранить файл Excel с названием, взятым из ячейки?

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

Исходные данные

Сначала давайте разберем исходные данные, которые я буду использовать в примерах. Пусть это будет некая абстракция марок автомобилей с указанием их VIN номера.

Примечание! Я использую Excel 2013.

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

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

Сохранение файла Excel с названием из ячейки — с привязкой к этой ячейке

Итак, данные у нас есть, теперь необходимо написать процедуру на VBA (макрос), которая брала бы значение из конкретной ячейки, в данном случае это будет ячейка B14, и присваивала бы это значение имени файла.

Читать еще:  Как закрепить ячейку в excel

Ниже представлен код процедуры, я его подробно прокомментировал. Единственное скажу, что я во всех примерах сохраняю новые файлы без макросов (расширение .xlsx), т.е. по факту будет один файл с поддержкой макросов, а все производные — без.

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

Открываем в Excel редактор Visual Basic, и вставляем код следующей процедуры в исходный код этой книги (ЭтаКнига, открыть двойным кликом) или в модуль, который Вы предварительно должны создать.

Примечание! Для того чтобы открыть редактор Visual Basic в Excel, необходимо перейти на вкладку «Разработчик» и нажать на кнопку «Visual Basic». Файл Excel с кодом процедуры необходимо сохранить с типом «Книга Excel с поддержкой макросов».

Код процедуры

После сохранения файла запустите макрос («Макросы -> Выполнить -> SaveFile»).

В результате в моем случае процедура успешно выполнилась, файл сохранился с названием «Марка Авто 1», данное значение взято из ячейки B14, о чем будет свидетельствовать сообщение в конце процедуры. Файл сохранен в каталоге, где и исходный файл (во всех примерах ниже прописано то же самое, т.е. сохранение рядом с исходником, но это Вы можете изменить).

Добавление кнопки в Excel для запуска макроса

Каждый раз открывать окно с макросами и выбирать нужный макрос не очень удобно, поэтому можно легко добавить кнопку где-нибудь рядом с данными и просто нажимать ее. Это делается следующим образом «Вкладка Разработчик -> Вставить -> Кнопка (элемент управления формы)».

Затем выберите место, где вставить кнопку, и нажмите туда. После этого появится окно назначения действия, т.е. нужно выбрать, какой макрос запускать при нажатии этой кнопки, выбираем наш макрос, т.е. SaveFile, и нажимаем «ОК».

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

Сохранение файла Excel с названием из ячейки — без привязки к ячейке

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

Замените код процедуры следующим кодом, который совсем немного, но изменен.

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

Как видим, все отработало.

Сохранение файла Excel с названием, которое сформировано из значений двух ячеек

Теперь представим, что нам нужно сформировать файл с названием из значений двух ячеек. Например, в нашем случае это может быть «Марка Авто – VIN Номер», в качестве разделителя я указал символ – (дефис), но им может выступать любой символ или вовсе отсутствовать.

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

Код процедуры в данном случае будет выглядеть следующим образом.

Все ОК, файл создан.

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

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

Читать еще:  Excel workbooks open

Код процедуры

Становитесь на любую ячейку со значением в столбце B, и запускайте макрос.

У меня на этом все, надеюсь, материал был Вам полезен, пока!

Сохранение документов Excel в PDF на сервере

Не так давно появилась задача создать простой сервис по созданию PDF отчетов на основе офисных документов для интранета. И вроде бы все просто, но вот с сохранением Excel в PDF возникли проблемы. Интересно? Прошу под кат.

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

Мои наработки использовали сom объекты excel.application и метод saveAs, это прекрасно работало, пока требовалось взять обычный, красивый документ и сделать из него pdf, но в данном случае файлы были нет так просты.

Во-первых, документы предполагаются трех форматов — xls, xlsx и xml. Во вторых все документы содержат макросы, а некоторые ссылки на другие документы. В третьих они содержат кучу листов, и перекрестные ссылки между листами. Не нужные для отчета листы делаются скрытыми, а на самих листах в каше вспомогательных цифр отчетная информация выделялась областью печати. Нужно ли говорить, что saveAs игнорирует все это богатство и на выходе после танца с бубнами получаем абсолютно не читаемую картину.

Здесь я думаю необходимо сделать лирическое отступление и пояснить, почему с файлами творится такая неразбериха. Я работаю в очень крупной организации, бок обок с кучей бабушке пенсионного возраста. Они не могут даже сделать текст в ячейке жирным, но прекрасно могут говорить начальству о том, что они не «программисты» и не должны это уметь. Начальства у нас тоже много, и в силу жалости к бабушкам или своей компьютерной безграмотности, а быть может, по велению звезд жалобы бабушек поощряются, и все попытки разгрести бардак в документах пресекается.

Вернемся к нашим баранам. В выше упомянутой статье был предложен вариант конвертации «Как вижу» с использованием open office, этот вариант меня не устроил в силу ветреного отношения OO к MS. Некоторые документы действительно открывались в нем корректно, но чаще всего содержимое ехало еще до конвертации.

Был еще третий вариант. Печатать документы на виртуальный принтер, но я решил, что этот вариант я приберегу на самый крайний случай, так это костыль.

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

Он начисто игнорировал области печати, а без них получалась каша. Я гуглил, оставлял вопросы на множестве форумах, в том числе и на англоязычных. Ответа не было. Я даже перенес код статьи в C#, но результат не изменился.

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

Путем проб и ошибок, танцев с бубнами и увлекательным чтением гугла я выяснил, области печати игнорируется при вызове этого метода и печати в php, C#, но почему то при использовании VBScript все работает как надо. Чем это вызвано я, к сожалению, так и не выяснил.

И так, было решено из PHP открывать VBS скрип и уже из него преобразовывать excel в pdf. Да, это тоже костыль, но не такой неуклюжий как виртуальный принтер.

Вот получившийся скрипт:

Dim XL
Dim XLWkbk
Dim ObjArgs
Dim paramSourceBookPath
Dim paramExportFilePath

set objargs = wscript.arguments
if objArgs.count exec(APPLICATION_SCRIPT_FOLDER.’\excel.vbs C:\tmp\test.xlsx C:\tmp\test.pdf»);

В сухом остатке мы имеем не совсем красивый, но 100% рабочий метод по преобразованию Excel в PDF, который гарантирует результат «Как на печати» без подводных камней.

Ссылка на основную публикацию
Adblock
detector