Перенос дат из текстового файла с помощью макроса

[Олег*]

Добрый день!

Проблема состоит в следующем.
Имеется текстовый файл с датами и прочими данными. По задумке, в Блокноте это все копируется в буфер обмена при помощи "Выделить все" > "Копировать", а потом в Экселе просто нажимаем самодельную кнопку "Вставить" и дальше все делается "по щучьему велению, по моему хотению"  Так задумано... Ну в прочем так все и происходит, кроме одного... Даты вставляются криво, хотя в их столбце назначения уже заранее выбран правильный, вроде бы, формат.



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

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

P.S. Текстовый файл с датами и рабочую книгу с макросом прилагаю.

[Serge 007]

Без макросов

ЗЫ Можно тоже самое записать макрорекордером
ЗЫЗЫ Можно так же настроить свойства (например периодичность обновления), как на этапе импорта, так и позже

[Hugo121]

Код Выделить Развернуть

Option Explicit
Private Sub CommandButton1_Click()
   Dim a, i&

   Columns("A:I").ClearContents

   With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
       .GetFromClipboard
       a = Split(.GetText(1), vbNewLine)
       On Error Resume Next
       For i = 1 To UBound(a)
           a(i) = CDate(a(i))
       Next
       Range("A1").Resize(UBound(a), 1) = Application.Transpose(a)
   End With

End Sub


[Олег*]

Без макросов

Спасибо за совет, но это все-таки не совсем то, что мне нужно. Мне нужно именно с помощью макроса. Дело в том, что макрос УЖЕ написан и работает. Большой хороший макрос (целую неделю разрабатывал и отлаживал). Он абсолютно все делает автоматически: вставляет данные из текстового файла в Эксель, преобразует их в удобный для анализа вид, обсчитывает, анализирует, выдает данные в виде таблицы с условным форматированием (для наглядности). И еще собираюсь сделать автоматический вывод диаграммы на основе полученных результатов. И все это делается автоматически.
Я (пользователь) по ходу всего процесса только заряжаю текстовый файл в "ствол", а потом нажимаю на две кнопки. Первая "Вставить", вторая - "Обработать".
А если без макроса, тогда, во-первых, будет много лишних тормозов, а во-вторых, пройдет определенное время, в течение которого я буду занят другими проблемами,  и я просто забуду как и что надо делать для получения конечного результата. А сейчас, когда я "в теме" на 100%, я сам себе хочу облегчить жизнь в будущем, по принципу: не задумываясь нажимай на кнопки и следуй своим же собственным подсказкам из прошлой жизни

[kuklp]

ЗЫ Можно тоже самое записать макрорекордером

Код Выделить Развернуть

Sub www()
' вместо "H:\Temp\6\Даты.txt" - свой путь.
    Workbooks.OpenText Filename:="H:\Temp\6\Даты.txt", Origin:=1251, StartRow _
        :=1, DataType:=xlDelimited, Tab:=True, FieldInfo:=Array(1, 4)
    [a1].CurrentRegion.Copy ThisWorkbook.Sheets(1).[a1]
    ActiveWorkbook.Close 0
End Sub

[Олег*]

Hugo121
Спасибо, работает как надо!!!
Правдо мудрёно очень, надо будет попробовать разобраться что к чему, но факт есть факт - работает.

KuklP
Спасибо за совет, но это все-таки не совсем то, что нужно. По этому методу надо будет всегда быть привязанным к определенному файлу с одним и тем же именем. А у меня файлы разные и в разных местах. Самое удобное для меня это щелкнуть нужный файл в Тотал Коммандере, потом "Выделить все", потом "Скопировать", ну а дальше уже на кнопки жать внутри Эксельного макроса

[Hugo121]

Есть ещё вариант - в тотале жмакнуть на файле cm_CopyFullNamesToClip, а далее совместить мой код с кодом Сергея

[Олег*]

Есть ещё вариант - в тотале жмакнуть на файле cm_CopyFullNamesToClip, а далее совместить мой код с кодом Сергея

Нет, нет, буду работать по вашему методу. Так мне гораздо удобнее будет.

P.S.А кстати, любопытно, что это за знак появляется после прогона?

[Олег*]

ЗЫ Можно тоже самое записать макрорекордером

На самом деле, первоначальный вариант как раз-то и был записан с помощью макрорекордера. Я прошел все шаги, описанные в вашем посте, но только не имя файла указывал, а из буфера обмена вставлял, т.е. вот так:





Записал весь процесс с помощью макрорекордера, а потом отредактировал "запись" и вставил в текст своей программы. Но парадокс: когда записываю макрорекордером, все вставляется как надо, а когда "воспроизвожу запись", вставляется криво. Почему так получается, совершенно не понятно.

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

P.S.А кстати, любопытно, что это за знак появляется после прогона?

Выяснил! Оказывается, это знак табуляции из моего текстового файла так отображается.

[Олег*]

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

Вариант, предложенный Hugo121, очень хорош, но, как выяснилось, подходит только для работы с текстовым файлом, состоящим только из одних дат, еще точнее,  из дат, расположенных в одной колонке. Поэтому мой первоначальный пробный файл Даты.txt был обработан Экселем без проблем, а вот более сложный файл (Даты+Имена.txt), состоящий уже из двух колонок с данными (даты и имена), был обработан неправильно.

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

В варианте Hugo121 мы вытаскиваем данные из буфера обмена, потом их обрабатываем, а затем сами вставляем в рабочую книгу.

А что если нам поступить похитрее, - подумал я, и меня озарило

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

Код Выделить Развернуть

       ActiveSheet.PasteSpecial Format:="Текст в кодировке Unicode", Link:=False, _
                                                                  DisplayAsIcon:=False

Эксель почему-то не любит даты в формате с косыми черточками, ну и не беда, ведь текст-то уже обработан. Мы заменили все косые черточки на точки, для того-то и вытаскивали содержимое буфера обмена наружу

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

Кому интересно, посмотрите приложенный архив с работающим вариантом и с пробным текстовым файлом для копирования и вставки.
Буду признателен, если услышу советы по оптимизации.

[Serge 007]

Спасибо за совет, но это все-таки не совсем то, что мне нужно. Мне нужно именно с помощью макроса.

Олег*, почему Вы так уверены что макросы - это панацея?

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

По большому счёту, макросы нужны только в тех ситуациях, когда с задачей не справляется сам Excel. В Вашем случае нет никаких причин применять макросы. И "тормоза" будут (особенно на больших массивах) именно из-за применения VBA. И забыть ничего не получится, т.к. делать руками ничего не надо будет

Он абсолютно все делает автоматически: вставляет данные из текстового файла в Эксель, преобразует их в удобный для анализа вид, обсчитывает, анализирует, выдает данные в виде таблицы с условным форматированием (для наглядности). И еще собираюсь сделать автоматический вывод диаграммы на основе полученных результатов. И все это делается автоматически.

Всё вышеперечисленное делается автоматически средствами Excel

Я (пользователь) по ходу всего процесса только заряжаю текстовый файл в "ствол", а потом нажимаю на две кнопки. Первая "Вставить", вторая - "Обработать"

Вы забыли про время, затраченное на написание макроса

Большой хороший макрос (целую неделю (!!!) разрабатывал и отлаживал)

Стандартными средствами Excel тоже самое делается за несколько минут
И даже "заряжать" и жать на кнопки не надо

ЗЫ Можете считать этот мой пост "советом по оптимизации" 

[Hugo121]

Чуть подсократил:

Код Выделить Развернуть

Private Sub CommandButton1_Click()
    Columns("A:I").Select
    Selection.ClearContents
    Range("A1").Select

    'Извлекаем текст из буфера обмена.
    With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .GetFromClipboard
        'Обработанный текст возвращаем обратно в буфер обмена.
        .SetText Replace(.GetText, "/", ".")
        .PutInClipboard
    End With

    'Ну а теперь доверимся советам макрорекордера:
    On Error GoTo ВыводСообщения
    Range("A1").Select
    ActiveSheet.PasteSpecial Format:="Текст в кодировке Unicode", Link:=False, _
                             DisplayAsIcon:=False
    On Error GoTo 0

    Exit Sub

ВыводСообщения:
    MsgBox "Ошибка копирования из буфера обмена!"
End Sub


[kuklp]

Имхо проще и гораздо надежней:

Код Выделить Развернуть

Sub www()
Dim s
ChDrive Left(ThisWorkbook.Path, 1): ChDir ThisWorkbook.Path
s = Application.GetOpenFilename("Text Files (*.txt),*.txt", , "Open textfile", , 0)
If s = False Then Exit Sub
   Workbooks.OpenText s, Origin:=1251, _
       StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
       Tab:=True, FieldInfo:=Array(Array(1, 4), Array(2, 1))
   [a1].CurrentRegion.Copy ThisWorkbook.Sheets(1).[a1]
   ActiveWorkbook.Close 0
End Sub

И никаких танцев с бубнами.

[Олег*]

Serge 007
Наверно, истина как всегда лежит где-то посередине

KuklP
И все-таки мне удобнее именно через буфер обмена эту проблему решать. Все равно ведь надо текстовый файл этот перед употреблением   открыть в Блокноте и посмотреть, что там внутри (тот ли это файл, ошибки может какие-нибудь сразу в глаза бросятся и т.п.). Ну а уж коль скоро он уже открыт, то там уже почти автоматом напрашивается "Выделить все" > "Копировать". Наверно, это дело вкуса и привычки, но для меня удобно именно так.

Hugo121
Спасибо!
Сейчас попробовал этот вариант. Отлично работает. Но, правда, задержку вставить все-таки все равно пришлось. Может от компа зависит. У меня старенький.

Эта самая Replace удобная штука, должен признать. А я о ней как-то и не знал. Все по-старинке пользуюсь Mid и InStr   Беру ее на вооружение

А то что можно не два раза вызывать GetObject, а только один, я догадывался, и сначала так и сделал, но начались глюки, тогда я решил ее "раздвоить"   И где-то одновременно с этим придумал вставить задержку. После этого все заработало как надо, ну я так и оставил. Разумеется, теперь переделал по вашему рецепту. Спасибо еще раз!