В целом работает нормально, но в некоторых случаях начинает чудить. Например, за указанную в формуле дату (28/08/2023) котировка должна быть 13.05 (возвращается именно с точкой) и эта котировка преобразуется в 13/05/2023 еще до того, как я могу что-то предпринять (т.е. замена точки на запятую в результате, возвращаемом функцией ФИЛЬТР.XML() ничего не дает, так как к этому времени там уже дата). Также пробовал конструкцию ПОДСТАВИТЬ(ВЕБСЛУЖБА();".";","), но тогда другая проблема - коверкается строка <?xml version="1.0" encoding="UTF-8"?> и дальше не работает ФИЛЬТР.XML.
Можно, конечно, заменить точку на запятую, потом version="1,0" на version="1.0" - но это как-то громоздко... Может кто-нибудь подскажет более изящное решение?
Столкнулся с непонятной для меня ситуацией (скорее всего из-за недостатка знаний в этом направлении). Суть задачи, которую пытаюсь решить: есть процедура, написанная на VBA, которая прекрасно функционирует - 70 т. строк обрабатываются примерно 10 сек. Однако, существует проблема - у пользователя, для которого программа предназначена, нет возможности включить макросы (когда включаешь - сразу начинает вылезать ошибка "Разрушительный сбой"). Поскольку эта ошибка, насколько я понимаю, в принципе сложно диагностируется и еще плюс то обстоятельство, что у меня нет доступа к этому компьютеру, попытался решить проблему альтернативным путем, а именно переписать код на VBS чтобы не задействовать макросы Excel. И не сказать чтобы вообще ничего не получилось, но быстродействие стало просто удручающим (~1400 сек. на том же файле).
Суть вопроса - может кто-то сталкивался с такой ситуацией и подскажет в каком направлении копать? Спасибо.
P.S. Не знаю, насколько принципиально, что именно написано в коде, но на всякий случай прикладываю оба варианта
Оригинальный код VBA
Код
Sub motiv()
Dim T: T = Timer
'Dim dic As New Dictionary
Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary")
Dim shRep As Worksheet, shResult As Worksheet
Dim r As Long, d As Long
Dim NK, Dt, TP, Manager
Dim tmpKey As Variant, tmpArr()
Set shRep = ActiveWorkbook.Sheets("Отчет")
'нужные столбцы (ищем в первом столбце)
Manager = Application.Match("Менеджер по сделке", shRep.Rows(1), 0)
TP = Application.Match("тип продукта", shRep.Rows(1), 0)
Dt = Application.Match("Дата и время операции", shRep.Rows(1), 0)
NK = Application.Match("НК", shRep.Rows(1), 0)
'перебираем все строки
For r = 2 To shRep.Cells(shRep.Rows.Count, NK).End(xlUp).Row
For d = 1 To 3 'из каждой записи делаем 3 новых записи (1,2,3 рабочих дня от даты операции - ожидаемое появление пифа)
'ключ (НК + тип + дата появляения ПИФа)
tmpKey = shRep.Cells(r, NK) & "_" & shRep.Cells(r, TP) & "_" & WorksheetFunction.WorkDay(shRep.Cells(r, Dt), d) 'можно добавить праздники, нужен список
If Not dic.Exists(tmpKey) Then 'если ключ не найден
'добавляем элемент с указанием менеджера (как массив, что бы потом можно было расширить)
dic.Add tmpKey, Array(shRep.Cells(r, Manager).Value)
Else
'если такой ключ уже есть
If IsError(Application.Match(shRep.Cells(r, Manager), dic(tmpKey), 0)) Then 'проверяем текущего менеджера в списке менеджеров в массиве
'и если такого менеджера там нет
tmpArr = dic(tmpKey) 'считываем элемент во временный массив
ReDim Preserve tmpArr(UBound(tmpArr) + 1) 'увеличиваем временный массив
tmpArr(UBound(tmpArr)) = shRep.Cells(r, Manager).Value 'добавляем текущего менеджера
dic(tmpKey) = tmpArr 'и переписываем временный массив обратно в словарь
End If
End If
Next d
Next r
Set shResult = ActiveWorkbook.Sheets.Add 'создаем новый лист
For Each tmpKey In dic 'перебираем словарь по ключам
If UBound(dic(tmpKey)) > 0 Then 'если элемен словаря - это массив с более чем одним элементом
r = shResult.Cells(shResult.Rows.Count, "A").End(xlUp).Row + 1 'первая незаполненная строка
With shResult.Range("A" & r & ":C" & r)
.Value = Split(tmpKey, "_") 'парсим ключ
.FormulaLocal = .FormulaLocal 'преобразуем НК и дату к числовому виду
End With
shResult.Cells(r, "C").NumberFormat = "m/d/yyyy" 'формат даты
'вставляем менеджеров из массива (диапазон вычисляется исходя из размерности массива)
shResult.Range(shResult.Range("D" & r), shResult.Range("D" & r).Offset(0, UBound(dic(tmpKey)))) = dic(tmpKey)
End If
Next tmpKey
MsgBox "Done: " & Round(Timer - T, 2) & " sec."
End Sub
Код, модифицированный на VBS
Код
Sub Motiv(sFileSelected)
xlUp = -4162
Dim T: T = Timer
Dim dic
Set dic = CreateObject("Scripting.Dictionary")
Dim ExApp, wb
Set ExApp = CreateObject("Excel.Application")
set wb = ExApp.Workbooks.Open(sFileSelected)
ExApp.Visible = True
Dim shRep, shResult, r, d
Dim NK, Dt, TP, Manager
Dim tmpKey, tmpArr
Set shRep = wb.Sheets("Отчет")
'нужные столбцы (ищем в первом столбце)
Manager = ExApp.Match("Менеджер по сделке", shRep.Rows(1), 0)
TP = ExApp.Match("тип продукта", shRep.Rows(1), 0)
Dt = ExApp.Match("Дата и время операции", shRep.Rows(1), 0)
NK = ExApp.Match("НК", shRep.Rows(1), 0)
'перебираем все строки
For r = 2 To shRep.Cells(shRep.Rows.Count, NK).End(xlUp).Row
For d = 1 To 3 'из каждой записи делаем 3 новых записи (1,2,3 рабочих дня от даты операции - ожидаемое появление пифа)
'ключ (НК + тип + дата появляения ПИФа)
tmpKey = shRep.Cells(r, NK) & "_" & shRep.Cells(r, TP) & "_" & ExApp.WorksheetFunction.WorkDay(shRep.Cells(r, Dt), d) 'можно добавить праздники, нужен список
If Not dic.Exists(tmpKey) Then 'если ключ не найден
'добавляем элемент с указанием менеджера (как массив, что бы потом можно было расширить)
dic.Add tmpKey, Array(shRep.Cells(r, Manager).Value)
Else
'если такой ключ уже есть
If ExApp.IsError(ExApp.Match(shRep.Cells(r, Manager), dic(tmpKey), 0)) Then 'проверяем текущего менеджера в списке менеджеров в массиве
'и если такого менеджера там нет
tmpArr = dic(tmpKey) 'считываем элемент во временный массив
ReDim Preserve tmpArr(UBound(tmpArr) + 1) 'увеличиваем временный массив
tmpArr(UBound(tmpArr)) = shRep.Cells(r, Manager).Value 'добавляем текущего менеджера
dic(tmpKey) = tmpArr 'и переписываем временный массив обратно в словарь
End If
End If
Next
Next
Set shResult = wb.Sheets.Add 'создаем новый лист
For Each tmpKey In dic 'перебираем словарь по ключам
If UBound(dic(tmpKey)) > 0 Then 'если элемен словаря - это массив с более чем одним элементом
r = shResult.Cells(shResult.Rows.Count, "A").End(xlUp).Row + 1 'первая незаполненная строка
With shResult.Range("A" & r & ":C" & r)
.Value = Split(tmpKey, "_") 'парсим ключ
.FormulaLocal = .FormulaLocal 'преобразуем НК и дату к числовому виду
End With
shResult.Cells(r, "C").NumberFormat = "m/d/yyyy" 'формат даты
'вставляем менеджеров из массива (диапазон вычисляется исходя из размерности массива)
shResult.Range(shResult.Range("D" & r), shResult.Range("D" & r).Offset(0, UBound(dic(tmpKey)))) = dic(tmpKey)
End If
Next
MsgBox "Done: " & Round(Timer - T, 2)
End Sub
Столкнулся с интересной проблемой - пытаюсь построить сводную таблицу из кэша, который в свою очередь создан на основе ADODB.Recordset:
Код
Dim rsReport As New ADODB.Recordset
rsReport.Open strSql, cnnMSSQL
Dim PT As PivotTable, PTCash As PivotCache
Set PTCash = ActiveSheet.Parent.PivotCaches.Create(SourceType:=xlExternal)
Set PTCash.Recordset = rsReport
Set PT = PTCash.CreatePivotTable(TableDestination:=ActiveSheet.Cells(1, "A"), TableName:="PT")
И всё прекрасно работает до тех пор, пока я не добавляю проверку на наличие в этом RecordSet записей при помощи конструкции
Код
If rsReport.EOF And rsReport.BOF Then Stop
После этого сводная таблица не строится с формулировкой "Недопустимые флаги метода доступа". Опытным путем выяснил, что любое обращение к этим свойствам приводит к такой же ошибке.
Столкнулся с неразрешимой (надеюсь пока) для меня задачей. Суть в следующем: есть таблица с исходными данными, в которой ставки записаны в виде 50,23 (при этом имеется ввиду 50,23%). На основании этой таблицы надо построить сводную, в которой поле значений - минимальная ставка, но уже в "нормальном виде", т.е. 0,5023. И вот тут для меня загадка - если я в сводную таблицу добавляю поле =Ставка/100 и вывожу минимум по этому полю, то в сводной все-равно выходит сумма...
Что я не так делаю? Проблема в том, что исходные данные трогать нельзя.
Суть задачи в следующем: есть таблица из трех столбцов - надо сгруппировать данные по первым двум столбцам, а значения из третьего столбца записать через разделитель (например, "/"). По сути, мне нужна операция обратная "Разделить столбец на Строки". Собственно, вопрос - реализуемо ли это в Power Query и если да, то как?
У меня есть в таблице два типа условного форматирования (цветовые шкалы и наборы значков). Когда я копирую эту таблицу и вставляю в PowerPoint в режиме "Сохранить исходное форматирование", то УФ цветовыми шкалами в PP сохраняется, а вот значки пропадают. Если вставлять таблицу в режиме "Внедрить" или "Рисунок", то значки видны, но такой тип вставки не очень подходит. Собственно вопрос - можно ли как-нибудь при вставке "Сохранить исходное форматирование" сделать так, чтобы значки УФ тоже копировались?
Заранее благодарен за ответы.
Изменено: webley - 08.09.2017 11:34:46(файл с примером)
Вопрос, наверное, банальный - но как правильно вставлять изображения? Например, если я просто копирую изображение в буфер и вставляю в окно "Текст сообщения" - оно вставляется и отображается корректно до тех пор, пока не нажмешь кнопку Отправить. После этого изображение начинает выглядеть так, как-будто его открыли при помощи блокнота. Я что-то не так делаю или так в принципе не работает? Ну и если вставлять через кнопку Изображение - там надо руками путь прописывать?
Никто не сталкивался с такой проблемой - при запуске вот этой функции:
Код
Public Function GetNewGuid() As String
GetNewGuid = Mid$(CreateObject("Scriptlet.TypeLib").GUID, 2, 36)
End Function
Вдруг ни с того ни с сего стала появляться ошибка Run-time error 70 permission denied. Империческим путем выяснил, что проблема характерна для 32-разрядной Windows, при этом версия и разрядность Office значения не имеет.
Есть какие-нибудь идеи, как можно исправить? Спасибо.
Столкнулся с неожиданной проблемой и понял, что без Вашей помощи мне не справиться...
Суть в следующем - есть некий отчет, который экспортирован в Excel из MS Reporting Services (не знаю, насколько это принципиально - для справки). Отчет имеет строгий вид, и определенную раскраску (текст, цвет ячеек и т.д.) Допустим, мне надо скопировать этот отчет в другую книгу - либо таблцу, либо лист целеком - и вот тут начинаются чудесные превращения: цвета становятся кислотными, даже близко не напоминающими исходные... в общем мрак! Однако, если эту новую таблицу (с искаженными цветами) скопировать обратно в исходный файл - все становится на свои места.
Собственно вопрос - что бы это могло быть (какая-то настройка?) и как это можно победить.
Я уже придумал некоторый способ, как с этим можно бороться - прогнать по всем ячейкам макросом что-то вроде ActiveCell.Interior.Color=ActiveCell.Interior.Color и тоже самое для шрифтов, границ ячеек и т.д. Это поможет, но способ представляется мне несколько колхозным и хотелось бы понять причину данного явления, а не просто бороться с последствиями....
Заранее всем благодарен
В прикрепленном файле картинка, как это выглядет ДО и После
Никто не сталкивался с такой проблемой - периодически возникает ошибка при копировании листа через VBA: "Метод Copy из класса Worksheet завершен не верно" Причем, что самое интересное, ошибка возникает именно периодически и исчезает через некоторое время сама собой и код отрабатывается без проблем. Проводил проверку - при возникновении этой ошибки открываю новую книгу Excel, включаю макрорекордер, копирую лист - это действие не записывается.
Нет ли у кого-нибудь идей что это такое и как с этим жить? Спасибо
Вопрос следующий - существует ли возможность визуально обозначить пустую ячейку черточкой (формат числа, условное форматирование или еще что-нибудь...)?
Вопрос в следующем: если я открываю книгу программными средствами (Workbooks.Open) существует ли у этой книги признак, по которому можно определить, что она открыта именно программно?
вопрос в следующем: у меня в макросе объявляется две переменных - массив и строка. После завершения работы макроса я бы хотел эти переменные очистить. Как очистить строку я сообразил, а вот с массивом проблема...
Sub test() Dim a() As String Dim st As String st = "sssd,sdatr,asd" a() = Split(st, ",") st = Empty End Sub
Может кто-нибудь подсказать, как это сделать? Спасибо
Возникла следующая проблема: есть файл в формате Excel 2007 с поддержкой макросов, защищенный паролем; в этом файле есть макрос, который должен выполнить запрос. Когда открываешь файл с диска, то все работает как надо, а вот если его отправить по почте и открыть из письма - возникает ошибка "Сбой подключения к драйверу ODBC Excel - Внешняя таблица не имеет предполагаемый формат"; при этом если пароль на файл не ставить - опять таки даже из почты работает без ошибок.
Собственно вопрос: возможно ли как-нибудь избежать этой ошибки? Ну, например, где-то в коде добавить пароль на файл...
Дополнительная информация: пароль на файл - "123" почтовая программа - Outlook 2007 файл из письма автоматически сохраняется в папку "C:\Documents and Settings\...\Local Settings\Temporary Internet Files\Content.Outlook\UQ15K1AH"
В 2003 excel можно было в меню создать кнопку, назначить ей макрос и либо выбрать рисунок из предложенных, либо нарисовать собственный. Вопрос в следующем: можно ли каким-нибудь образом нарисовать свою иконку для кнопки, распольженной на панели быстрого запуска в 2007 excel? Вопрос уже поднимался, но ответа я, к сожалению, не нашел...
Столкнулся со следующей проблемой: при построении круговой диаграммы по двум значениям если одно из значений равно нулю, то появляется два круга, вместо одного, причем на одном компьютере этот эффект наблюдается, а на другом нет (поэтому прикрепляю картинку). Может кто-то сталкивался с подобным и знает как с этим бороться?
Спасибо
PS. Excel 2007, стиль диаграммы 26, Разделение 20%
Суть вопроса: можно ли через VBA получить допустимые критерии автофильтра. Например, если в столбце А есть некий список: значение 1 значение 2 значение 3 то в Excel в фильтре можно выбирать из этих значений. Хотелось бы получить этот список из Range("$A$1:$C$7").AutoFilter. Никто с подобным не сталкивался?
Есть некая таблица, в которой примерно 2000 строк, причем строки разной высоты. Задача стоит следующая: высоту всех строк увеличить на некоторое значение (4). Если решать ее в лоб, например так
for r=1 to 2000 Rows®.RowHeight=Rows®.RowHeight+4 next r
Нужный результат достигается, но времени на это надо...
Собственно вопрос: можно ли как-нибудь ускорить этот процесс? Ну скажем, делать это не построчно, а для всех строк сразу: Rows("1:2000").RowHeight=Rows("1:2000").RowHeight+4 {не работает}
Или какой-нибудь другой вариант, как в Word: Абзац -> Интервал Перед (После)...
Суть проблемы в следующем: в коде изменяю формат ячейки на "#,##0;-#,##0;-". Eсли я запускаю макрос со своего компьютера, то в свойствах ячейки появляется формат "# ##0;-# ##0;-": я так понимаю, что на VBA разделитель разряда сотен от тысяч - запятая, а в Excel - пробел. А вот если этот же макрос запускается на другом копьютере (к сожалению доступа к нему у меня нет, могу посмотреть только результат), то в свойствах ячейки формат пишется так же, как и в VBA, т.е. "#,##0;-#,##0;-" и естественно, число отображается не так как я хочу.
Вопрос: может ли кто нибудь подсказать, в чем может быть дело и как с этим бороться
Как можно через VBA определить, какие значения можно выбрать в фильтре? Может быть сформулировано не совсем понятно, но думаю на прикрепленном примере будет ясно, что я имею ввиду.
Вопрос в следующем - если вставить на лист элемент ComboBox ActiveX, то он имеет какой-то непрезентабельный внешний вид (похоже на размытый рисунок плохого качества). Однако, когда этот элемент получает фокус, он становится четким до тех пор пока фокус на нем. Можно ли сделать так, чтобы элемент был четким постоянно независимо от того, есть ли на нем фокус? Спасибо
Столкнулся с такой проблемой: в книге автоматически создается новый лист и называется "Total". Для этого листа нужно добавить (тоже автоматически) обработку событий, но при этом в коллекции VBComponents он отображается, например, как "Лист42". Собственно вопрос - как используя имя листа "Total" можно обратиться к модулю этого листа через VBComponents?