#excel #vba #date
#excel #vba #Дата
Вопрос:
У меня есть CSV-файл со столбцом дат в формате США (мое региональное форматирование — Великобритания). Если я открою файл CSV в Excel, а затем сохраню файл вручную, он слегка изменит записи дат (если они признаны действительными датами Великобритании), но сохранит их в том же формате. Однако, если я сохраню книгу с помощью макроса, используя ActiveWorkbook.Даты сохранения меняются местами (то есть те, которые признаны действительными датами Великобритании).
Что, черт возьми, происходит? Как мне сохранить с помощью макроса и избежать этого?
Пример входного файла:
Date
06/02/2020
06/30/2020
07/13/2020
07/22/2020
07/31/2020
08/11/2020
08/11/2020
08/31/2020
09/22/2020
09/29/2020
После сохранения вручную:
Date
6/02/2020
06/30/2020
07/13/2020
07/22/2020
07/31/2020
8/11/2020
8/11/2020
08/31/2020
09/22/2020
09/29/2020
После сохранения с помощью макроса:
Date
2/6/2020
06/30/2020
07/13/2020
07/22/2020
07/31/2020
11/8/2020
11/8/2020
08/31/2020
09/22/2020
09/29/2020
Решение:
Решение, как указано в ответе Рона, состояло в том, чтобы заменить строку сохранения на SaveAs, установив для локальной переменной значение True, чтобы она не использовала регион VBA US по умолчанию.
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.FullName, Local:=True
Application.DisplayAlerts = True
Ответ №1:
Ваша проблема связана с открытием файла csv в вашей версии Excel.
При открытии файла Excel предполагает, что все даты будут иметь тот же формат краткой даты, что и региональные настройки Windows.
Поскольку ваш WRS DMY и даты файла csv MDY
- Даты, в которых 2-я часть <= 12, будут неправильно интерпретироваться как
DMY - Даты, в которых 2-я часть> 12, будут сохранены в виде текста
Чтобы этого не произошло, перед тем, как файл CSV попадет в Excel (будет записан на лист Excel), вы должны указать Excel формат входящих дат.
Один из способов (и, на мой взгляд, лучший) — импортировать файл CSV, используя либо устаревший мастер, либо Power Query. Когда вы это сделаете, вам будет предоставлена возможность указать Excel формат входящих данных.
Обходным путем является создание CSV с однозначными датами. yyyy-mm-dd обычно работает, как и могло dd-mmm-yy бы, хотя у последнего будут проблемы с языковыми различиями, когда аббревиатуры месяцев разные.
Чтобы сохранить даты в том же формате, вы могли
- импортируйте даты, затем отформатируйте этот столбец в Excel, как
mm/dd/yyyперед его повторным сохранением. - импортируйте даты, но в процессе импорта обозначьте этот столбец как
text. Это будет работать только в том случае, если этот столбец не будет изменен или использован для каких-либо вычислений, пока книга открыта.
Редактировать
Другой возможностью, которая может помочь в вашем конкретном примере, может быть установка Local параметра Workbook.SaveAs метода на True , чтобы файл сохранялся на языке / настройках Excel, а не на VBA. Хотя в этом случае это может сработать, это далеко не пуленепробиваемый способ и может вызвать проблемы в будущем, в зависимости от того, что конечный пользователь хочет сделать с этими датами. Я предполагаю, что когда SaveAs выполняется из листа Excel, он будет сохранен с использованием языка / настроек Excel (я не видел документации для этого); в то время как VBA ориентирован на США, он сохраняется на языке VBA (т. Е. США), если не указан Local .
Комментарии:
1. Попытка Botton line — Excels «услужливо» распознать даты часто не только бесполезна, но и разрушительна. ИМХО, ваш обходной путь (однозначные даты) является лучшим и правильным решением, когда у вас есть контроль над источником. Если у вас нет контроля над источником, я обычно прибегаю к написанию собственного обработчика CSV, когда у меня есть априорное знание фактического формата
2. @chrisneilsen я согласен. Хотя в наши дни я, скорее всего, «напишу свой обработчик», настроив запрос с помощью Power Query.
3. Я не думаю, что вы вполне понимаете, о чем я спрашиваю, я имею в виду, почему сохранение вручную и сохранение VBA ведут себя совершенно по-разному? На самом деле я открываю существующий файл CSV, добавляю столбец и сохраняю его с помощью макроса. Когда макрос сохраняет файл, он меняет даты, однако, если я исключу функцию сохранения из макроса и сохраню файл вручную, это не отменит даты. Я хочу иметь возможность делать это в макросе, не искажая даты.
4. Чтобы уточнить, нет проблем с тем, как он открывает файл. Я совершенно доволен тем, что при открытии файла некоторые даты распознаются в формате UK. Если я сохраняю их вручную, сохраняются даты, которые он распознает как формат UK, что нормально, поскольку соответствует тому, как он их читает. Проблема в том, что функция сохранения в макросе сохраняет даты в формате US вместо формата UK, поэтому она меняет их на то, как она их открыла.
5. @GodKratos Даты, которые, по вашему мнению, распознаются, распознаются не так, как они были изначально задуманы.