Vba сохранить файл в формате csv
Обновлено: 04.07.2024
Часто при формировании прайс-листов требуется выгрузить большой объём данных в текстовый файл в формате CSV (разделитель - точка с запятой, или запятая)
И далеко не всегда может помочь сохранение файла в этом формате, поскольку в выгрузку попадают лишние данные (заголовки таблиц, лишние строки и столбцы, и т.д.)
В данном случае поможет экспорт заданного диапазона ячеек в файл CSV, что проще всего сделать макросом с использованием функции Range2CSV:
Вот код самой функции Range2CSV:
Улучшенная версия кода (работает заметно быстрее), и дополнительно заключает текст всех ячеек в кавычки:
Комментарии
Если нужно чтобы выводились данные до первой пустой ячейки первого столбца.:
После строки
If Len(Range2CSV) > 50000 Then buffer$ = buffer$ & Range2CSV: Range2CSV = ""
Добавляем строку:
If arr(i + 1, 1) = "" Then i = UBound(arr, 1) 'Если значение первой ячейки следующей строки массива пустое, то завершаем цикл
Автору спасибо! Много полезной инфы на сайте.
Спасибо огромное за помощь!
Доброго времени суток!
А как сделать,чтоб значения в ячейках выгружались в числовом или денежно формате с двумя знаками после запятой?
Вот например есть 4499795,40 выгружается 4499795,4 или 900 000,00 выгружается 900000
Всё хорошо. Только при импорте файла csv в престашоп возникает ошибка . С чем может быть связана?
спасибо! теперь запускается.
подскажите, почему таблица остается таблицей, а не переходит в вид столбца в выгружаемом файле?
при этом, если в строке
CSVtext$ = Range2CSV(ra, ";")
поменять точку с запятой на запятую, то таблица становится столбцом, но запятые в цифрах при этом путаются с запятыми-разделителями. если же поставить точку, то она будет путаться с точками в дате.
не понимаю, как работает эта последняя функция, поэтому не знаю, как это победить
В статье дана ссылка на функцию SaveTXTfile
Надо было код этой функции тоже вставить в ваш файл - и всё сразу заработает.
на SaveTXTfile пишет Sub or Function not defined
Ну так сделайте цикл, в котором будут просматриваться все строки таблицы.
Пример кода написать не могу, не видя ваш файл.
Если сами не справитесь - можете заказать разработку такого макроса (оформите заказ, прикрепив вашу таблицу, и разъяснив, что и куда должно выгружаться)
Подскажите, пожалуйста, как сделать, чтоб выгружать не сплошной диапазон, а только строки, у которых (например) в 11 колонке написано имя формируемого файла?
В идеале будет генерироваться несколько файлов, имена файлов которых прописаны в 11 колонке. В диапазоне возможно наличие строк, у которых значение равно цифре 0
Спасибо, за оперативный ответ.
В новом файле все заработало. Буду искать где были конфликты.
Отдельное спасибо за полезный ресурс.
Не должна эта строка выдавать ошибку, если только мой макрос не конфликтует с существующими в том же файле вашими макросами.
Попробуйте код на новом файле (в котором нет других макросов), и заново скопируйте необходимые макросы с моего сайта.
Если в новом файле проблема исчезнет - ищите проблему несовместимости в своих макросах.
В строке
txt = "": For j = LBound(arr, 2) To UBound(arr, 2): txt = txt & ColumnsSeparator$ & arr(i, j): Next j
Выделяет (txt =) с коментарием: Expected function or variable
Спасибо за оперативный ответ, попробуем
Ну почему же "код должен быть чисто своим"?
Я вот и чужой код часто использую - просто не удивляюсь, если он работает не совсем так, как хочелось бы, а дорабатываю его под свои нужды.
Я использовал функцию только для выгрузки диапазонов, заведомо не содержащих символа разделителя в обрабатываемых ячейках.
Если такие символы присутствуют - надо знать, как их экранировать.
Тут есть много вариантов:
- предварительно заменить разделитель в ячейке на какой-то другой символ
- заменить разделитель на другой символ только в выгрузке CSV (в ячейке оставить как было)
- экранировать сам символ разделителя (например, текст 123;456 заменить на 123";"456 или на 123/;456)
- экранировать всю ячейку в выгрузке (например, текст 123;456 заменить на "123;456")
- и т.д. и т.п. (при нескольких разделителях в ячейке появляются ещё варианты)
Предусматривать все эти варианты в макросе не очень хочется - объём кода заметно увеличится, а желающих внести какие-то дополнительные "навороты" только прибавится.
В вашем случае всё решалось заменой одной строки кода:
Надо вместо arr(i, j) подставить некую функцию, которая произведёт изменения текста в элементе массива при наличии в нём символов разделителей.
к сожалению не совсем корректно работает Function Range2CSV - если внутри ячейки содержится разделитель (у меня это была сложная гиперссылка) -исходнй ЦээСВэ искажается - при копипасте исохранить в формате csv -всё прекрасно
ну даже не знаю говорить ли спасибо? :) полдня промудохался
СПАСИБО - будет Муку наука - код должен быть чисто своим
Я копирую данные в электронную таблицу, использую VBA для ее форматирования, затем сохраняю этот лист в файл CSV.
Я использую следующий код:
ws - это лист, который я сохранил.
Это дает мне CSV файл с разделителями-запятыми.
Я хотел бы сохранить этот лист в файл, разделенный точкой с запятой.
Я нашел следующее:
- Выберите "Пуск"> "Настройки"> "Язык и региональные стандарты".
- Нажмите на кнопку Настроить
- Рядом с разделителем списка введите точку с запятой (;)
Я следовал вышеописанной процедуре и изменил свой код на:
Я все еще получаю CSV файл с разделителями-запятыми в качестве вывода.
Я использую Excel 2003, и моя ОС - Windows XP.
Я только что проверил это, потому что имел ту же проблему. В этом случае имя файла не имеет функциональности.
Это то, что сработало для меня:
Не нужно объявлять все эти переменные, просто добавьте local: = true в конце вашего метода SaveAs, например:
Просто используйте этот код: ActiveWorkbook.SaveAs "My File.csv", xlCSV, Local: = True
(не использовать: Filename: =)
Чтобы использовать vbs script следующую конструкцию succeded:
.SaveAs Filename, 6, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1
Последний "1" в "SaveAs" равен Local = True
Кроме того, точка с запятой должна быть определена как разделитель списка в региональных настройках ОС (см. ответы выше)
Использовать xlSCVMSDOS instread xlCSV
У меня сработало
Я столкнулся с той же проблемой и, подумав о попытке изменить "разделитель строк" в региональных настройках с использованием кода VBA и вызовов ядра, решил, что это будет намного сложнее, поэтому вместо этого я нашел несколько примеров использования Scripting.FileSystemObject. вместо этого, чтобы удовлетворить мои потребности.
Следующий код возьмет существующий CSV файл и заменит все запятые символом тильды "
Затем вы можете просто вызвать подпрограмму commaReplace из подпрограммы, которая создает файл csv.
Надеюсь, это поможет кому-то!
Посмотрите другие вопросы по меткам vba excel-vba excel или Задайте вопрос
Если у Вас возникла необходимость выгружать данные из базы MS SQL Server в текстовый формат CSV, то из данного материала Вы узнаете, как это можно сделать средствами Access 2003.
Итак, если у Вас встала задача выгрузить данные с SQL сервера в CSV файл, то ее можно решить несколькими способами, например:
Все они не универсальные и не идеальные, поэтому какой способ использовать зависит от ситуации. Например, для администратора или программиста баз данных SSIS будет отличным вариантов для массового экспорта данных, но он не подходит, если необходимо, например, интегрировать данную возможность в свое приложение, чтобы обычные пользователи могли пользоваться ей.
Программа BCP тоже не подходит, даже если вызывать ее средствами T-SQL, используя хранимую процедуру, так как для этого потребуется включить возможность выполнения xp_cmdshell, а это не безопасно.
Конструкция INSERT INTO OPENROWSET подразумевает наличие уже созданного CSV файла с нужными заголовками полей, тоже не подходит, так как это неудобно.
Остается только использовать что-то другое, например, Access, и для меня это оказалось лучшим вариантом, так как приложение, в которое мне необходимо было интегрировать возможность экспорта в CSV, как раз реализовано в виде ADP проекта Access 2003.
Сейчас в тестовом варианте я покажу Вам, как можно в ADP проекте Access 2003 реализовать возможность выгрузки результатов запросов в CSV файл.
Исходные данные
В качестве примера я буду использовать СУБД Microsoft SQL Server 2012 Express.
Допустим, у нас будет база данных Test, а в ней следующая таблица
Заполним таблицу тестовыми данными
Посмотрим, что у нас получилось
Заметка! Если Вы не знаете, что делает вышеуказанная инструкция, рекомендую посмотреть мой видеокурс «T-SQL. Путь программиста от новичка к профессионалу. Уровень 1 – Новичок», который предназначен для начинающих. В нем подробно рассмотрены все базовые конструкции языка T-SQL.
И эти данные нам нужно выгрузить в CSV файл и не просто выгрузить, а реализовать функционал, с помощью которого обычные пользователи могли бы сами экспортировать данные. В качестве разделителя допустим, нам необходимо использовать точку с запятой.
Реализация экспорта данных с SQL сервера в формат CSV в Access 2003
Так как CSV это обычный текстовый формат данных, где значения разделяются запятой или другим символом нам достаточно написать код VBA, который позволял бы сохранять текстовый файл с расширением CSV.
Для этого открываем свой ADP проект, он соответственно должен быть подключен к тестовой базе Test (или к той, которая нужна Вам).
Создаем форму или открываем на редактирование существующую форму и добавляем на ней одну кнопку, например, я ее назвал ExportInCSV.
Примечание! Сразу скажу, что в примере ниже я показываю сам принцип выгрузки, но данную возможность можно сделать более универсальной, например, на форму добавить текстовое поле и в него писать SQL запрос, для того чтобы передавать в процедуру или сделать возможность самостоятельно указывать любой разделитель, в примере ниже он прописан вручную в коде или, например, Вам нужно динамически формировать название экспортируемого файла все это под свои нужды Вы легко можете доработать самостоятельно.
В «Обработку события» нашей кнопки вставляем следующий код VBA (я его подробно прокомментировал):
Примечание! Для работы FileDialog требуется подключить библиотеку Microsoft Office 11.0 Object Library. Это делается следующим образом: в редакторе Microsoft Visual Basic (где вы вставляете код VBA) откройте меню Tools->References, найдите нужную библиотеку, поставьте галочку и нажмите «ОК».
Сохраняем и запускаем нашу форму, щелкаем по кнопке (в моем случае ExportInCSV), в итоге у нас откроется диалоговое окно выбора пути сохранения файла, мы выбираем каталог для экспорта и жмем «Сохранить». Затем подтверждаем свое действие и жмем «ОК».
Видим, что нужные данные у нас выгрузились и разделитель точка с запятой, все как мы и хотели.
Если вы часто пользуетесь Microsoft Excel, вы должны использовать в нем функцию «Сохранить как», которая позволяет сохранять открытую в настоящий момент книгу с другим именем или другим форматом (Excel Macro-enabled, CSV, PDF и т. Д.). Вы также можете сохранить файл в другой папке, используя этот метод. Однако возможно ли использовать ту же функцию под VBA? Ответ абсолютный Да! У нас также есть функция «Сохранить как» в VBA, которая помогает нам выполнять все вышеупомянутые задачи наряду с некоторыми дополнительными преимуществами (очевидно, автоматизация - одно из преимуществ). В этой статье мы рассмотрим различные примеры функции VBA SAVE AS.
Формула для функции Сохранить как в Excel VBA
Давайте посмотрим ниже формулу для функции Сохранить как в VBA.
- FileName - имя книги, которую нужно сохранить.
- FileFormat - формат файла, в котором файл должен быть сохранен (напр. Pdf, CSV и т. Д.)
- Пароль - пароль для защиты рабочей книги (рабочая книга не может быть доступна без пароля)
- WriteResPassword - Напишите пароль бронирования для книги.
- ReadOnlyRecommended - распознает, сохранена ли книга в формате только для чтения или нет.
- CreateBackup - определяет, создан ли файл резервной копии для книги.
- AccessMode - распознает режим доступа к книге .
- ConflictResolution - распознает конфликты, возникающие, когда рабочая книга является общей и используется более чем одним пользователем.
- AddToMru - проверяет, добавлена ли рабочая книга в недавно использованный файл или нет.
- Локальный - Проверяет, сохранена ли рабочая книга с законами Excel (местный язык) или с законами VBA (США - английский).
Тише! Много аргументов, верно? Но что, если я скажу вам, все эти аргументы являются необязательными и могут быть пропущены при использовании функции VBA SAVE AS. Однако верно то, что именно эти аргументы делают VBA SaveAs более гибкой в использовании функцией. «Выражение» в начале синтаксиса - не что иное, как выражение, против которого можно использовать эту функцию. Like Workbook - это выражение, с которым можно использовать SaveAs.
Примеры сохранения файла Excel с помощью функции VBA Save As
Ниже приведены различные примеры сохранения файла Excel с использованием функции VBA Save As.
Вы можете скачать этот шаблон VBA Сохранить как Excel здесь - VBA Сохранить как шаблон Excel
Пример № 1 - Как сохранить копию рабочей книги с другим именем?
Давайте посмотрим, как мы можем сохранить текущую книгу под другим именем.
Чтобы использовать функцию «Сохранить как» в Excel VBA, выполните следующие действия:
Шаг 1. Добавьте новый модуль в редактор Visual Basic (VBE). Перейдите на Вставить и затем выберите Модуль.
Шаг 2: Определите новую подпроцедуру, которая может хранить макрос.
Код:
Шаг 3: Определите новую переменную, которая может содержать имя, под которым будет сохранен файл.
Код:
Шаг 4: Теперь используйте оператор присваивания, чтобы присвоить имя этой переменной, используя который текущий файл может быть сохранен как.
Код:
Шаг 5: Теперь используйте функцию SaveAs с аргументом FileName, чтобы сохранить файл с именем «Example1».
Код:
Шаг 6: Вот и все, теперь запустите этот код, нажав F5 или вручную, используя кнопку Run, и посмотрите вывод.
Вы можете видеть, что файл с именем « Пример1 » сохраняется в Документах .
Если бы вы могли записать, файл сохраняется как Macro-Enabled File, потому что исходный файл, на котором я использовал функцию SaveAs, это файл с Macro-Enabled. Это означает, что эта функция в VBA автоматически проверяет формат файла предыдущего файла и сохраняет его в том же формате. Также по умолчанию файл будет сохранен в Документах на этом ПК . Это местоположение по умолчанию может быть предоставлено явно во время определения имени листа.
Пример №2 - Сохранение книги с предоставленным пользователем именем
Вместо первоначального определения имени, можно ли написать код, который позволяет пользователю сохранять рабочий лист под именем по своему выбору, так же как функция Excel Save As?
Выполните следующие шаги, чтобы использовать функцию «Сохранить как» в Excel VBA.
Шаг 1: Определите новую подпроцедуру в недавно вставленном модуле, который может хранить макрос.
Код:
Шаг 2: Определите новую переменную, которая может содержать значение пользовательского имени.
Код:
Причина, по которой эта переменная определяется как Variant, заключается в том, что этот тип данных делает соглашения об именах универсальными. Например, пользователь может добавить некоторый дополнительный специальный символ (который разрешен в соглашениях об именах) или может также добавить даты под именем файла.
Шаг 3: Теперь с помощью оператора присваивания и комбинации функций, называемой application.GetSaveAsFilename, создайте оператор, который позволит системе принять определенное пользователем имя. Посмотрите, как это было достигнуто на скриншоте ниже.
Код:
Шаг 4: Используйте условный IF, чтобы убедиться, что имя, которое вводит пользователь, является действительным согласно соглашениям об именах.
Код:
Это условие проверяет, соответствует ли имя, данное пользователем для сохранения рабочего листа, правилам именования, установленным для именования файла, или нет.
Шаг 5: Запишите утверждение, которое оценивается для данного условия IF.
Код:
Этот фрагмент кода оценивается, когда условие IF выполнено. Если это так, активная рабочая книга будет сохранена под именем, определенным в переменной Spreadsheet_Name (которая будет определяться пользователем)
Шаг 6: Завершите цикл IF и запустите этот код, чтобы увидеть результат.
Код:
Шаг 7: Как только вы запустите этот код, вы получите диалоговое окно Сохранить как, которое позволит вам ввести имя по вашему выбору и сохранить файл.
Пример № 3 - Как сохранить файл в формате PDF с помощью функции Excel VBA SaveAs?
Предположим, у вас есть данные, указанные ниже в вашей таблице Excel, и вам нужно преобразовать их в PDF.
Выполните следующие шаги, чтобы преобразовать этот файл в PDF с помощью функции VBA Сохранить как:
Шаг 1: Определите новую подпроцедуру для хранения макроса.
Код:
Шаг 2. Теперь используйте следующий код, чтобы сохранить этот файл в формате PDF.
Код:
Шаг 3: Запустите этот код, и вы увидите файл PDF, созданный в разделе «Этот ПК»> «Документы».
В этом коде ActiveSheet.SaveAs позволяет сохранить файл с тем же именем. Поскольку мы добавили расширение как .pdf в конец файла, оно экспортируется в файл PDF. Вы можете увидеть изображение выше для вашей справки.
То, что нужно запомнить
- По умолчанию местом сохранения файла, используемого в VBA SaveAs, будет Этот ПК> Документы. Тем не менее, вы можете указать каталог вручную во время определения имени файла.
- По умолчанию файл, сохраненный с помощью VBA SaveAs, будет сохранен в том же формате, что и исходный файл. Тем не менее, он также может быть определен в соответствии с требованиями пользователя во время определения переменной.
Рекомендуемые статьи
Это руководство к VBA Сохранить как. Здесь мы обсудим, как сохранить файл с помощью функции Excel VBA Save As вместе с примером и загружаемым шаблоном Excel. Ниже приведены несколько полезных статей о Excel, связанных с VBA.
Читайте также: