Распакуйте архив. Положите все свои файлы в папку XML. Нажмите кнопку на 2-м листе. Макрос соберет данные на 2-й лист. Потом можно сделать сводную.
Добрый день, спасибо Вам за такие полезные макросы! У меня есть вопрос по импорту данных в Exel. Я собираю информацию из ячеек с временем из файлов .xhf , это тот же xml, только в программе станка он записывается с таким расширением. В файле есть 6 или 12 колонок. Меня интересует 3,4,5,6 колонки. Все ячейки в колонках открываются как общий формат, из-за чего теряется точность длинного числа в 3 колонке (более 15 цифр) и время в .xhf файле записано в таком виде - 2023-07-14T07:49:57.9947049+03:00, Excel не понимает что это время и тоже формирует ячейки как обычный формат, а нужно время хотя бы с одной десятой секунды. В общем мне приходится сначала в Notepad ++ переформатировать .xhf файл создавая в каждом стиль с указанием ss:Format="@" для 3 колонки , ss:Format="mm/dd/yyyy hh:mm:s.0" для 5 и 6 , 4 остается с общим форматом. А потом только открываю в Excel и делаю арифметические действия с колонками время, ориентируясь на 3 и 4 колонки (в 3 колонке идентификатор задания он может быть от 17 до 19 цифр, он важен для расчета времени). Пакетной обработки для Notepad++ нет, поэтому приходится делать по одному, а потом по одному также открывать в Excel. Увидел ваш скрипт который собирает все xml файлы в одну книгу, понял, что можно быстрее получать информацию за определенный период - кинул в папку XML столько файлов, сколько дней я хочу проанализировать , запускаю ваш скрипт (файл), а потом свой скрипт и он считает сразу по всему списку!
У меня к Вам большая просьба, а можно что-то дописать в Ваш скрипт, чтобы автоматически добавить стиль к 3, 5 и 6 колонке? (к 3 - текст) (к 5 и 6 - дата.время). Чтобы мне не запускать сначала для каждого файла Notepad++ с макросом для каждого...
Несколько оригинальных файлов .xhf без стиля за 10,11,12 число, я приложил в архиве "для отчета.zip" Спасибо!
Распакуйте архив. Положите все свои файлы в папку XML. Нажмите кнопку на 2-м листе. Макрос соберет данные на 2-й лист. Потом можно сделать сводную.
Добрый день, спасибо Вам за такие полезные макросы! У меня есть вопрос по импорту данных в Exel. Я собираю информацию из ячеек с временем из файлов .xhf , это тот же xml, только в программе станка он записывается с таким расширением. В файле есть 6 или 12 колонок. Меня интересует 3,4,5,6 колонки. Все ячейки в колонках открываются как общий формат, из-за чего теряется точность длинного числа в 3 колонке (более 15 цифр) и время в .xhf файле записано в таком виде - 2023-07-14T07:49:57.9947049+03:00, Excel не понимает что это время и тоже формирует ячейки как обычный формат, а нужно время хотя бы с одной десятой секунды. В общем мне приходится сначала в Notepad ++ переформатировать .xhf файл создавая в каждом стиль с указанием ss:Format="@" для 3 колонки , ss:Format="mm/dd/yyyy hh:mm:s.0" для 5 и 6 , 4 остается с общим форматом. А потом только открываю в Excel и делаю арифметические действия с колонками время, ориентируясь на 3 и 4 колонки (в 3 колонке идентификатор задания он может быть от 17 до 19 цифр, он важен для расчета времени). Пакетной обработки для Notepad++ нет, поэтому приходится делать по одному, а потом по одному также открывать в Excel. Увидел ваш скрипт который собирает все xml файлы в одну книгу, понял, что можно быстрее получать информацию за определенный период - кинул в папку XML столько файлов, сколько дней я хочу проанализировать , запускаю ваш скрипт (файл), а потом свой скрипт и он считает сразу по всему списку!
У меня к Вам большая просьба, а можно что-то дописать в Ваш скрипт, чтобы автоматически добавить стиль к 3, 5 и 6 колонке? (к 3 - текст) (к 5 и 6 - дата.время). Чтобы мне не запускать сначала для каждого файла Notepad++ с макросом для каждого...
Несколько оригинальных файлов .xhf без стиля за 10,11,12 число, я приложил в архиве "для отчета.zip" Спасибо!antonovakatyapetya
могу предложить PQ - в выгрузке специально показал форматом, что все 15 разрядов сохранены (это чуть точнее, чем 0,0001 секунды, точнее экселька не сможет, а на стороне pq можно и последующие преобразования сделать без потери точности) [vba]
Код
let from = Folder.Files(Excel.CurrentWorkbook(){[Name="adres"]}[Content]{0}[Column1])[Content], f=(x)=>Xml.Tables(x), g=(x)=>[a=Text.Length(Text.SplitAny(x,".+"){1}), b=if a=7 then x else Text.Replace(x,"+",Text.PadStart("+",8-a,"0")), c=DateTimeZone.FromText(b,[Format="O",Culture="en-US"]), d=if x =null then null else c][d], tr=List.Transform(from,f), tbl=Table.Combine(tr), exp = Table.ExpandTableColumn(tbl, "Table", {"CutWorkId", "WorkPhase", "StartTime", "EndTime"}), to=Table.TransformColumns(exp,{{"StartTime",g,type nullable datetimezone},{"EndTime",g,type nullable datetimezone}}) in to
[/vba]
могу предложить PQ - в выгрузке специально показал форматом, что все 15 разрядов сохранены (это чуть точнее, чем 0,0001 секунды, точнее экселька не сможет, а на стороне pq можно и последующие преобразования сделать без потери точности) [vba]
Код
let from = Folder.Files(Excel.CurrentWorkbook(){[Name="adres"]}[Content]{0}[Column1])[Content], f=(x)=>Xml.Tables(x), g=(x)=>[a=Text.Length(Text.SplitAny(x,".+"){1}), b=if a=7 then x else Text.Replace(x,"+",Text.PadStart("+",8-a,"0")), c=DateTimeZone.FromText(b,[Format="O",Culture="en-US"]), d=if x =null then null else c][d], tr=List.Transform(from,f), tbl=Table.Combine(tr), exp = Table.ExpandTableColumn(tbl, "Table", {"CutWorkId", "WorkPhase", "StartTime", "EndTime"}), to=Table.TransformColumns(exp,{{"StartTime",g,type nullable datetimezone},{"EndTime",g,type nullable datetimezone}}) in to
могу предложить PQ - в выгрузке специально показал форматом, что все 15 разрядов сохранены (это чуть точнее, чем 0,0001 секунды, точнее экселька не сможет, а на стороне pq можно и последующие преобразования сделать без потери точности)
Большое Вам спасибо! ДА это то что мне нужно, сэкономим уйму времени. Дело в том, что у компьютер управляющего станком, вышел из строя накопитель, привычный для всех файл отчета, который формируется на основе потерянных файлов, стал недоступен. Смог найти только эти небольшие файлы на каждый день. Информации там мало, но есть то что нужно для нашего техотдела. Я механик, ремонтирую оборудование, попросили найти данные, я нашёл, но в сыром виде девочки из техотдела с ними ничего не могут сделать. Начал писать скрипты, долго писал, учился.... Получилось сделать выборку даже, из двух циклов для подсчета времени. А вот как преобразовать ячейку, так не понял. Еще раз спасибо за помощь!
могу предложить PQ - в выгрузке специально показал форматом, что все 15 разрядов сохранены (это чуть точнее, чем 0,0001 секунды, точнее экселька не сможет, а на стороне pq можно и последующие преобразования сделать без потери точности)
Большое Вам спасибо! ДА это то что мне нужно, сэкономим уйму времени. Дело в том, что у компьютер управляющего станком, вышел из строя накопитель, привычный для всех файл отчета, который формируется на основе потерянных файлов, стал недоступен. Смог найти только эти небольшие файлы на каждый день. Информации там мало, но есть то что нужно для нашего техотдела. Я механик, ремонтирую оборудование, попросили найти данные, я нашёл, но в сыром виде девочки из техотдела с ними ничего не могут сделать. Начал писать скрипты, долго писал, учился.... Получилось сделать выборку даже, из двух циклов для подсчета времени. А вот как преобразовать ячейку, так не понял. Еще раз спасибо за помощь!antonovakatyapetya
Сообщение отредактировал antonovakatyapetya - Суббота, 05.08.2023, 17:15
doober, У Вас еще удобнее вариант! Выбор папки - очень удобно! Вы уж извините за навязчивость, а можно еще мой макрос встроить, что я записывал для окончательного подсчета времени по второй кнопке или сразу вместе с Вашим? Теперь он не работает, хотя колонки по счету совпадают. Может переменные совпали? Начальный отсчет для переменной " i " я делал с второй строки, все равно скрипт останавливается на условии поиска значения WorkIdle.
Там много наверное лишнего у меня, не обращайте внимание, я только начал изучать. У меня в итоге на каждый день получалась вот такая табличка: , без фильтра по цвету: Rолонка "D" с окончанием времени не нужна, всё время есть в колонке "C", проверял с отчетами созданными машиной в нормальные дни, всё сходиться с точностью до д.секунд.
Sub Âðåìÿ_ÐÊ() Dim i As Integer Dim s As String Dim id As String Dim tim As Date Dim k As Integer k = 0 i = 1 While (Cells(i, "B") <> "") If (Cells(i, "B") = "WorkIdle") Then s = Cells(i, "B") id = Cells(i, "A") tim = Cells(i, "C") k = i While ((Cells(k, "B") = "WorkIdle") Or (Cells(k, "B") = "Queue") Or (Cells(k, "B") = "Collimation")) And (id = Cells(k, "A")) k = k + 1 Wend If (k - 1 <> i) Then Cells(k - 1, "D") = tim Cells(k - 1, "E") = Cells(k - 1, "C") - tim
ActiveSheet.ListObjects("Òàáëèöà1").Range.AutoFilter Field:=3, Criteria1:=RGB(49, 255, 61), Operator:=xlFilterCellColor Cells(i + 1, "E").Select End Sub
doober, У Вас еще удобнее вариант! Выбор папки - очень удобно! Вы уж извините за навязчивость, а можно еще мой макрос встроить, что я записывал для окончательного подсчета времени по второй кнопке или сразу вместе с Вашим? Теперь он не работает, хотя колонки по счету совпадают. Может переменные совпали? Начальный отсчет для переменной " i " я делал с второй строки, все равно скрипт останавливается на условии поиска значения WorkIdle.
Там много наверное лишнего у меня, не обращайте внимание, я только начал изучать. У меня в итоге на каждый день получалась вот такая табличка: , без фильтра по цвету: Rолонка "D" с окончанием времени не нужна, всё время есть в колонке "C", проверял с отчетами созданными машиной в нормальные дни, всё сходиться с точностью до д.секунд.
Sub Âðåìÿ_ÐÊ() Dim i As Integer Dim s As String Dim id As String Dim tim As Date Dim k As Integer k = 0 i = 1 While (Cells(i, "B") <> "") If (Cells(i, "B") = "WorkIdle") Then s = Cells(i, "B") id = Cells(i, "A") tim = Cells(i, "C") k = i While ((Cells(k, "B") = "WorkIdle") Or (Cells(k, "B") = "Queue") Or (Cells(k, "B") = "Collimation")) And (id = Cells(k, "A")) k = k + 1 Wend If (k - 1 <> i) Then Cells(k - 1, "D") = tim Cells(k - 1, "E") = Cells(k - 1, "C") - tim
Ок! У меня там определенный расчет есть. = таблица с именем "Macchina" в xml -игнорируется, поэтому такие строчки где есть "Macchina" нужно удалить. Нужно также удалить все строки где попадаются пустые ячейки в колонке "CutWorkId" или "WorkPhase" или "StartTime". Это сбой или приостановка работы. Работа всегда начинается с маркера "WorkIdle". Время активации маркера есть время начала работы что мне нужно подсчитать. Вот краткий макрос для определения времени:
Dim i As Integer Dim s As String Dim id As String Dim tim As Date Dim k As Integer k = 0 i = 1 While (Cells(i, "B") <> "") If (Cells(i, "B") = "WorkIdle") Then s = Cells(i, "B") id = Cells(i, "A") tim = Cells(i, "C") k = i While ((Cells(k, "B") = "WorkIdle") Or (Cells(k, "B") = "Queue") Or (Cells(k, "B") = "Collimation")) And (id = Cells(k, "A")) k = k + 1 Wend If (k - 1 <> i) Then Cells(k - 1, "D") = tim Cells(k - 1, "E") = Cells(k - 1, "C") - tim End If i = k - 1 End If i = i + 1 Wend
Условие поиска такое: While ((Cells(k, "B") = "WorkIdle") Or (Cells(k, "B") = "Queue") Or (Cells(k, "B") = "Collimation")) And (id = Cells(k, "A")) k = k + 1 цикл перебирается вниз до тех пор пока не станет ложным это условие, это значит переменная "k минус 1" будет означать номер строки окончания работы, потом идет вычитание конца работы и начала с записью и так далее до конца вниз.
Так я вычислил время, использую нормальный отчет сформированный автоматически, смотрел какое время там и искал что из чего вычесть, что бы получилось также. Файл автоматического расчёта времени (интересует только последняя колонка"Cut time elapsed" прилагаю, можете сверить если любопытно.
Ок! У меня там определенный расчет есть. = таблица с именем "Macchina" в xml -игнорируется, поэтому такие строчки где есть "Macchina" нужно удалить. Нужно также удалить все строки где попадаются пустые ячейки в колонке "CutWorkId" или "WorkPhase" или "StartTime". Это сбой или приостановка работы. Работа всегда начинается с маркера "WorkIdle". Время активации маркера есть время начала работы что мне нужно подсчитать. Вот краткий макрос для определения времени:
Dim i As Integer Dim s As String Dim id As String Dim tim As Date Dim k As Integer k = 0 i = 1 While (Cells(i, "B") <> "") If (Cells(i, "B") = "WorkIdle") Then s = Cells(i, "B") id = Cells(i, "A") tim = Cells(i, "C") k = i While ((Cells(k, "B") = "WorkIdle") Or (Cells(k, "B") = "Queue") Or (Cells(k, "B") = "Collimation")) And (id = Cells(k, "A")) k = k + 1 Wend If (k - 1 <> i) Then Cells(k - 1, "D") = tim Cells(k - 1, "E") = Cells(k - 1, "C") - tim End If i = k - 1 End If i = i + 1 Wend
Условие поиска такое: While ((Cells(k, "B") = "WorkIdle") Or (Cells(k, "B") = "Queue") Or (Cells(k, "B") = "Collimation")) And (id = Cells(k, "A")) k = k + 1 цикл перебирается вниз до тех пор пока не станет ложным это условие, это значит переменная "k минус 1" будет означать номер строки окончания работы, потом идет вычитание конца работы и начала с записью и так далее до конца вниз.
Так я вычислил время, использую нормальный отчет сформированный автоматически, смотрел какое время там и искал что из чего вычесть, что бы получилось также. Файл автоматического расчёта времени (интересует только последняя колонка"Cut time elapsed" прилагаю, можете сверить если любопытно.antonovakatyapetya
Я бы сделал немного по другому, но не лезу в ваш код. Так , как Вы в начале пути , хочу дать совет. Используйте полный путь к ячейкам, указывая родителей. У ячейки всегда есть лист
Я бы сделал немного по другому, но не лезу в ваш код. Так , как Вы в начале пути , хочу дать совет. Используйте полный путь к ячейкам, указывая родителей. У ячейки всегда есть листdoober
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
