Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Выбрать дату в календареВыбрать дату в календаре

Страницы: 1
Не срабатывает переключение между рабочими книгами
 
У меня Эксель - 2013.
Да имеено так. После того как нажал ок. во втором ИнпутБоксе. Срабатывает макрос. И я остаюсь на листе доноре. Иногда на время работы макроса окно листа реципиенат становится активным, но потом все равно возвращает на лист-донор.

Есть еще один глюк. В рамках одной рабочей книги переключение листов срабатывает. Но Глючно.
То есть открывает нужный лист, но при попытке что-то внести в ячееку - ничего не происходит. Она остается пуста и ошибки не выдает. Но если переключится на изначьный лист то увидим что знаечние ввелось в него :) в ячеку стаким же адресом. Помогает переключение мышкой по вкладкам листов :)
Не срабатывает переключение между рабочими книгами
 
Цитата
Nordheim написал:
вы уверены что при активном окне InputBox, можно переключаться между книгами, у меня  вышло только между листами.
Да, я переключаюсь и между книгами и между листами.
Между листами все работает как я и писал в П/С. А вот между книгами - нет.
Не срабатывает переключение между рабочими книгами
 
Вложил файл с примером
Изменено: Troglodit - 3 Окт 2017 14:49:10
Не срабатывает переключение между рабочими книгами
 
Добрый, может кто уже сталкивался с подобной проблемой.
Есть макрос который переносит данные из другой книги/листа на активыный лист.
При этом используются диалоговые окна InputBox. Может это важно.

Как это работает.
1.
Код
Set MyWorkbook= ActiveWorkbook : Set MySheet = ActiveSheet
2. На активно листе в InputBox юзер вводит адрес ачейки-получателя
3. Юзер переключается на другой лист в другой книге и в InputBox юзер вводит адрес ачейки-донора
4. После закрытия диалогового окна начинается перенос данных. При этом активной остается книга-донор на которую руками переключился юзер.
5. После переноса данных стоит команда
Код
MyWorkbook.Activate
MySheet.Activate

Ошибку не выдает, но и на изначальный лист с которого юзер начал работу, не переключает.
Вариации с
Код
MyWorkbook.Select или Application.Window(MyWorkbook.Name).Activate

не помогли

П/С в пределах одной рабочей книги переключение листов работает.
Работает также если макросом по запросу юзера другая рабочая книга (если нужный файл не открыт) сначала открывается. Потом юзер в ней выбирает ячейку. Затем макрос отрабатывает и переключает на изначальный лист.
Изменено: Troglodit - 3 Окт 2017 11:18:52
Как перезаписать PivotCache у существующей PivotTable из recordset ?
 

Спасибо, но любая попытка обратится к RecordSet Сводной выдает ошибку

Решил проблему пока таким образом. Через перезаливку recordset в PivotCache и обновление PivotTable

Код
If ActiveWorkbook.PivotCaches.Count = 0 Then
    Dim objPivC As Excel.PivotCache
    Dim objPivT As Excel.PivotTable
    
    Set objPivC = ActiveWorkbook.PivotCaches.Create(xlExternal, , 4)
    Set objPivC.Recordset = rs
End If
Set ActiveWorkbook.PivotCaches(1).Recordset = rs
shSvod1.PivotTables("Pivot_table1").PivotCache.Refresh

Изменено: Troglodit - 2 Окт 2017 12:42:07
Как перезаписать PivotCache у существующей PivotTable из recordset ?
 
Доброго дня, столкнулся с такой проблемой. Просьба помочь.
Есть ADODB.Recordset созданный с помощью  SQL запроса.

Есть сводная таблица (Pivot_Table1) созданная из этого Recordset  и в последсвии руками настроеная (фильтра, области значений, колонки, строки).
Но нужно переодически обновлять ее источник данных. Для это заново формирую Recordset  и пытаюсь его присвоить Pivot_Table1.
Но выдает ошибку
Код
Set shSvod1.PivotTables("pivot_table1").PivotCache.Recordset = rs
Изменено: Troglodit - 2 Окт 2017 12:01:06
Зависает SQL-запрос содержащий вложенный зависимый запрос
 
Excel 2013.

Доброго дня!
Столкнулся с такой проблемой.

Есть условно две таблицы, которые нужно импортировать из Access и объединить:

T1 с полями KOD, Name, Date, G1, G2, GX...  (около 200 000 записей)
T2 с полями KOD, sGroup, DateIn (около 8 000 записей)

Их нужно объеденить чтобы добавить в T1 поле Group
Объединение по полям KOD но в Т2 это поле не уникально, есть вариации записей по полям DateIn и искомому Group.
Поэтому должно соблюдаться еще одно условие Date = MAX(диапазон DateIn, где DateIn <=Date)

С импортом в Эксель проблем нет, проблемы с объединением (что в Access, что в Эксле)

Написал такой вот SQL.
Код
SELECT T1.*, 
     (SELECT TOP 1 sGroup FROM T2 WHERE T1.KOD=T2.KOD ORDER BY DateIn DESC) as GroupName
FROM T1

Вложенный запрос сам по себе работает, но как тольок его вставляю во внешний - запрос начинает отработку и зависает.
В Excell - сразу, в Access при попытке получить все резульаты запроса (а так оказывает видимые строки на экране, общее кол-во записей не выводит).

Может кто знает как модифицировать запрос, чтобы он заработал ?
Например можно ли заменить вложенный запрос LEFT JOIN ? Ума не приложу как это сделать. Отчегото кажется что JOIN не должен зависать.
Изменено: Troglodit - 19 Сен 2017 13:30:59
Как получить данные из PivotCash ?, Глобально хочу получить список уникальных данных по имени поля Сводной таблицы
 
Почему то конструкция типа этого не работает.
Код
Dim pc as PivotCache
Dim rs as Recordset 'пробовал и ADODB.Recordset

pc = shSvod1.PivotTables("СводнаяТаблица1").PivotCache 'Ошибок нет
Set rs = pc.Recordset 'Ошибок нет
rs.MoveFirst 'Ошибка что объект не найден
Изменено: Troglodit - 18 Сен 2017 10:45:14
OLAP. Как получить список уникальных значений в поле и и информацию о том какие из них скрыты фильтром ?, Нужно передать в ListBox уникальные значения поля и отметить какие из них видимы.
 

В общем заработал только такой вариант. Если кто сможет предложить более красивый и оптимальный - буду благодарен.

Код
Sub RefreshListBoxDate()
 Dim pt As PivotTable
 Dim pf As PivotField
 Dim iListV() As Variant
 Dim n, m, lV, lLB As Integer
 Dim s1, s2 As String Set pt = Worksheets("Свод_1").PivotTables("СводнаяТаблица2")
 Set pf = pt.PivotFields("[tbBasePLBS].[Дата].[Дата]")
 ListBox3.List = ModuleTools.Extract_Unique("tbBasePLBS", "Дата") 'Заполняем список уникальными значениями поля
 iListV = pf.VisibleItemsList lV = UBound(iListV)
 lLB = ListBox3.ListCount'Перебираем совпадения и отмечаем в списке отображаемые данные
For n = 1 To lLB
For m = 1 To lV
s1 = ModuleTools.ExtractValue(iListV(m)) 'извлекаем значение из строки фильтра и присваиваем переменной s1
s2 = ListBox3.List(n - 1)
If s1 = s2 Then ListBox3.Selected(n - 1) = True 'если значения в фильтре и списке совпали то отмечаем
Next m
Next n
End Sub

Код
Function ExtractValue(iVal As Variant) As String
Dim iStarPos, iLenght As Integer
If iVal = Empty Then
    ExtractValue = ""
Else
    iStarPos = InStrRev(iVal, "[") + 1
    iLenght = Len(iVal) - iStarPos
    ExtractValue = Mid(iVal, iStarPos, iLenght)
End If
End Function
Код
Function Extract_Unique(TableName, ColumnName As Variant) As Variant
    Dim vItem, avArr, li As Long
    Dim NameRange As String
    Dim myRange As Range
    
    ReDim avArr(1 To Rows.Count)
    
    NameRange = TableName & "[" & ColumnName & "]" 'формируем имя диапазона искомого столбца в заданой таблице
    Set myRange = Range(NameRange) 'присваиваем переменной искомый диапазон
         
    
    With New Collection
        On Error Resume Next
        For Each vItem In myRange.Value
            'Cells(Rows.Count, 1).End(xlUp) – определяет последнюю заполненную ячейку в столбце А
            .Add vItem, CStr(vItem)
            If Err = 0 Then
                li = li + 1: avArr(li) = vItem
            Else: Err.Clear
            End If
        Next
    End With
    ReDim Preserve avArr(1 To li)
    If li Then Extract_Unique = avArr
End Function
Изменено: Troglodit - 8 Авг 2017 17:23:47
OLAP. Как получить список уникальных значений в поле и и информацию о том какие из них скрыты фильтром ?, Нужно передать в ListBox уникальные значения поля и отметить какие из них видимы.
 
Сделал файл примера. (оригинальный файл сильно большой, даже при удалении данных).
И, парадокс, получил новый результат:

Теперь PivotItems содержит только видимые элементы.
Ничего не понимаю. Почему в моей таблице в PivotItems есть все уникальные элементы поля, но у них свойство Visible считывается как True у всех независимо от того отображаются они в сводной таблице или нет.

В примере коллекция PivotItems теперь содержит только отображаемые элементы. Скрытых нет.
Изменено: Troglodit - 8 Авг 2017 12:37:31
OLAP. Как получить список уникальных значений в поле и и информацию о том какие из них скрыты фильтром ?, Нужно передать в ListBox уникальные значения поля и отметить какие из них видимы.
 

Доброго дня всем.
Столкнулся с такой проблемой. Хочу использовать в отдельной форме ListBox  как фильтр для сводной таблицы OLAP.
Как заполнить ListBox  уникальными значениями по полю - решил. Осталась задача отметить какие из значений поля отображаются, а какие - нет. Это нужно при первичной инициализации формы чтобы синхронизировать ListBox  с установками сводной таблицы.

Написал такой код, но он не работает правильно. Почему-то все получаемые значения PivotItems.Visible = True. Других способов получить статус отображения элемента не нашел.


Код
Sub RefreshListBoxDate()
 Dim pt As PivotTable
 Dim pf As PivotField
 Dim pi As PivotItems
 Dim s As StringSet pt = Worksheets("Свод_1").PivotTables("СводнаяТаблица2")
Set pf = pt.PivotFields("[tbBasePLBS].[Дата].[Дата]")
Set pi = pf.PivotItemsListBox3.List = Array()For i = 1 To pi.Count
s = pi(i).Value
MsgBox CStr(i) & ": " & s & ": Visible is " & CStr(pi(i).Visible)
ListBox3.AddItem
ListBox3.List(i - 1, 0) = ExtractValue(pi(i).Value)
ListBox3.Selected(i - 1) = pi(i).Visible ' Не работает - все значения True, хотя это не так
Next i
End Sub

Function ExtractValue(iVal As String) As String
   Dim iStarPos, iLenght As Integer
   
   iStarPos = InStrRev(iVal, "[") + 1
   iLenght = Len(iVal) - iStarPos
   ExtractValue = Mid(iVal, iStarPos, iLenght)
End Function

Изменено: Troglodit - 8 Авг 2017 11:20:00
Получить значение из PivotItem сводной таблицы без имени таблицы и имени поля, Значение Value возвращает строку типа [Имя таблицы].[Имя поля]&[Значение]
 
Написал функцию. Может кому пригодится. Тема закрыта
Код
Function ExtractValue(iVal As String) As String
Dim iStarPos, iLenght As Integer
iStarPos = InStrRev(iVal, "[") + 1
iLenght = Len(iVal) - iStarPos
ExtractValue = Mid(iVal, iStarPos, iLenght)
End Function
Изменено: Troglodit - 8 Авг 2017 11:08:50
Получить значение из PivotItem сводной таблицы без имени таблицы и имени поля, Значение Value возвращает строку типа [Имя таблицы].[Имя поля]&[Значение]
 
Спасибо. Понял, прийдется писать функцию извлечения значения.
Я честно говоря не знаю как отличить таблицу OLAP от обычной. И в чем особенности работы с ними через VBA.

Но это так, подзадача.

Может сможете еще подсказать по главному вопросу ? А то пока никто не смог мне ответить.
См. по ссылке.
http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=1&TID=94471&TI...
Изменено: Troglodit - 8 Авг 2017 10:24:34
Получить значение из PivotItem сводной таблицы без имени таблицы и имени поля, Значение Value возвращает строку типа [Имя таблицы].[Имя поля]&[Значение]
 
Добрый день, подскажите пожалуйста.

У меня есть сводная таблица.
Попытка получить значение из поля дает не значение а строку типа [Имя таблицы].[Имя поля]&[Значение]

Мой код
Код
Sub RefreshListBoxDate()
 Dim pt As PivotTable
 Dim pf As PivotField
 Dim pi As PivotItemsSet pt = Worksheets("Свод_1").PivotTables("СводнаяТаблица2")
Set pf = pt.PivotFields("[tbBasePLBS].[Дата].[Дата]")
Set pi = pf.PivotItems
ListBox3.List = Array()
For i = 1 To pi.Count
MsgBox CStr(i) & ": " & pi(i).Value & ": Visible is " & CStr(pi(i).Visible)
ListBox3.AddItem
ListBox3.List(i - 1, 0) = pi(i).Value 'Тут я поулчаю не чистое значение а еще имя таблицы и поля, что не нужно.
ListBox3.Selected(i - 1) = pi(i).Visible
Next i
End Sub
Изменено: Troglodit - 8 Авг 2017 13:34:46
Отметить в ListBox отфильтрованные значения из сводной таблицы
 
И еще, добрые люди.

Можете подсказать почему я не могу обратится к полю просто по имени в PivotFields написав ... PivotFields("Дата") (почему-то выдает ошибку, что объект не найден)
А нужно писать имя поля в PivotFields в таком виде ? (так пишет макрорекордер)

.PivotTables("СводнаяТаблица2").PivotFields("[tbBasePLBS].[Дата].[Дата]")
Изменено: Troglodit - 7 Авг 2017 12:01:49
Отметить в ListBox отфильтрованные значения из сводной таблицы
 

Добрый день!
Просьба помочь.
Я сделал форму с ListBox для удобной фильтрации данных в сводной таблице.
В ListBox можно отмечать значения и по нажатии ОК сводная таблица фильтруется согласно выбранных значений.

Но я не знаю как передать в ListBox значения уже установленных фильтров в сводной таблице.

Так как получается некрасиво. Таблица при первом открытии файла уже отфильтрована согласно предыдущих установок, а моя форма при первичном открытии получается девственно чистая, что не есть хорошо.


Как передать список уникальных значений в ListBox уже разобрался. А как отметить те которые уже отфильтрованы - не знаю.

Попробовал такой код

Код
Private Sub UserForm_Initialize()
Call RefreshListBoxDate
End Sub 

Код
Sub RefreshListBoxDate()
ListBox3.List = ModuleFilter.Extract_Unique("tbBasePLBS", "Дата")
With Worksheets("Свод_1").PivotTables("СводнаяТаблица2").PivotFields("[tbBasePLBS].[Дата].[Дата]")
For i = 1 To .PivotItems.Count
'MsgBox .PivotItems(i).Name & " Visible is " & CStr(.PivotItems(i).Visible)
ListBox3.Selected(i - 1) = .PivotItems(i).Visible
Next i
End With
End Sub

Но почему-то в PivotItems свойство Visible оказалось у всех True
Изменено: Troglodit - 7 Авг 2017 11:58:13
Ошибка при попытке передать в .PivotFields одномерный массив значений фильтра
 

Все получилось. Действительно в значения фильтра нужно заполнять по определенным правилам, указывая и поля к которым он относится.
Готовый рабочий результат таков:

Код
Sub btFilter_Click()
Dim iList()
Dim x As Integer
x = 0UserFormFilter1.Show
ReDim iList(UserFormFilter1.ListBox1.ListCount)For i = 0 To UserFormFilter1.ListBox1.ListCount - 1 Step 1
    
    If UserFormFilter1.ListBox1.Selected(i) Then
        iList(x) = "[tbBasePLBS].[Тип_отчетности].&[" & UserFormFilter1.ListBox1.List(i, 0) & "]"
        'MsgBox iList(x)
        x = x + 1
    End If
Next i
ReDim Preserve iList(x - 1)

ActiveSheet.PivotTables("СводнаяТаблица2").PivotFields( _
       "[tbBasePLBS].[Тип_отчетности].[Тип_отчетности]").VisibleItemsList = iList

End Sub
Изменено: Troglodit - 31 Июл 2017 08:50:37
Ошибка при попытке передать в .PivotFields одномерный массив значений фильтра
 
Попробую щас сделать по аналогии с таким вот найденным кодом. Потом отпишусь.
Код
Sub Make_VisibleItems_Array()    Dim vVisibleList() As Variant, vPCodes As Variant
    Dim i As Long    

    vPCodes = Split("AB11,AB12,M2,M3,M4,M5,M6,M7,M8,M9,N1,N2,N3,N4,AB13", ",")     

    ReDim vVisibleList(LBound(vPCodes) To UBound(vPCodes))
    For i = LBound(vPCodes) To UBound(vPCodes)
        vVisibleList(i) = "[Postal District].[Postal District].&[" & vPCodes(i) & "]"
    Next i

    ActiveSheet.PivotTables("PivotTable6").PivotFields( _
        "[Postal District].[Postal District].[Postal District]") _
            .VisibleItemsList = vVisibleList
End Sub
Ошибка при попытке передать в .PivotFields одномерный массив значений фильтра
 
Наверное нет, так как  сводная таблица сформирована на отдельном листе из таблицы из этой же книги.
Команда установки фильтра записана макрорекордем. В ней я изменил значение фильтра с Array ("....", "...", ..") на переменную iList
Наверно я неправильно формирую в массиве значения фильтра.
Изменено: Troglodit - 31 Июл 2017 09:37:17
Ошибка при попытке передать в .PivotFields одномерный массив значений фильтра
 
Пожалуйста помогите.
У меня стоит задача установить фильтр в сводной таблице исходя из выбранных нескольких значений в ListBox.

Написал такой код:
Код
Sub btFilter_Click()
Dim iList()
Dim x As Integer
x = 0

UserFormFilter1.Show
ReDim iList(UserFormFilter1.ListBox1.ListCount)

For i = 0 To UserFormFilter1.ListBox1.ListCount - 1 Step 1
    
    If UserFormFilter1.ListBox1.Selected(i) Then
        iList(x) = UserFormFilter1.ListBox1.List(i, 0)
        'MsgBox iList(x)
        x = x + 1
    End If
Next i

ReDim Preserve iList(x - 1)
ActiveSheet.PivotTables("СводнаяТаблица2").PivotFields( _
        "[tbBasePLBS].[Тип_отчетности].[Тип_отчетности]").VisibleItemsList = iList

End Sub

На команде присвоения фильтру сводной таблицы значений массива выбранных пунктов выдает ошибку 1004
Не могу разобраться что я делаю не так.
Как правильно определить имя, если значения имен одинаковые?, Нужна функция для определения названия.
 
Если не нужен макрос, то такие вещи удобно делать через сводную таблицу и из нее переносить данные в свою форму руками.
Вот небольшой пример.
В данном случае поэтапно пофильтрвовать свод по каждой фуре отдельно и скопировать результат в форму вверху
Изменено: Troglodit - 28 Июл 2017 09:22:49
Не работает макрос для добавления общих итого в сводную таблицу
 
В общем я понял. Из-за переноса значений в область строк нужно было итог по строкам делать, а не столбцам. Хотя макрорекордер почему-то записал столбцы :)
Переписал код, все работает. Спасибо за внимание!
Код
With ActiveSheet.PivotTables("СводнаяТаблица2")
'.ColumnGrand = Not .ColumnGrand 'Неправильно
.RowGrand = Not .RowGrand 'Правильно
End With

Цитата
webley написал: вы случайно ColumnGrand и RowGrand не путаете?
Путаю.
Изменено: Troglodit - 26 Июл 2017 18:12:20
Не работает макрос для добавления общих итого в сводную таблицу
 
Так в примере ж работет. Но только когда руками в свойствах сводной таблицы ставлю галочки.
А ежели макросом - то результат нет :(
Изменено: Troglodit - 26 Июл 2017 18:01:39
Не работает макрос для добавления общих итого в сводную таблицу
 
Спасибо за ответ.
Да я уже по разному пробовал записать макрос, выложил последний вариант. Без результата.
Наверно у меня что то с настройками Екселя, но не знаю что, раз у Вас работает, а у меня нет.
Вкладываю пример. Он у меня не работает. Может сможете подсказть ?
Не работает макрос для добавления общих итого в сводную таблицу
 

Доброго времени суток.
С толкнулся с такой проблемой.

У меня Excel 2013.

Я создал кнопку которая должна включать/выключать отображение общих итогов строк в сводной таблице.
Подключил к ней доработаный макрос созданный макрорекордером.

Код
Sub swColumnGrand()
Dim Check As Boolean
Check = ActiveSheet.PivotTables("СводнаяТаблица2").ColumnGrand
'MsgBox (Check)
If Check Then
    ActiveSheet.PivotTables("СводнаяТаблица2").ColumnGrand = False 'Эта строка была создана макрорекордером когда руками включал/выключал итоги
        Else:
    ActiveSheet.PivotTables("СводнаяТаблица2").ColumnGrand = True
End If End Sub

В итоге при запуске макроса с таблицей ничего не происходит. Ошибку также не выдает.
Подскажите пожалуйста что я делаю не так ?
Изменено: Troglodit - 26 Июл 2017 17:51:03
Страницы: 1
Наверх