Есть большой массив данных, который не влазит на лист, - masiv(i,7) т.е. 7 столбцов и очень много строк... как из него сделать кэш для сводной у меня есть мысль
Код
Dim objPivotCache As PivotCache
Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal)
' А вот как передать masiv(i,7) в objPivotCache???
' Строим сводную на лист1
With wsPivot
objPivotCache.CreatePivotTable TableDestination:=sheets("Лист1").Range("a1")
End With
Может кто знает как это сделать??? без выгрузки в промежуточные файлы типа (txt) ?
Можно, по идее, только не через массив, а через PivotCache.Recordset задать собственную таблицу Recordset, как результат некоторой выборки. Только сводная в этом случае не будет обновляться. Как пример для одной из создаваемых таблиц
Скрытый текст
Код
Private Function GetConnection() As Object
Dim pConn As Object
Dim sConn As String
sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Data Source=" & ThisWorkbook.FullName
sConn = sConn & ";Extended Properties=""Excel 12.0;HDR=YES"";"
Set pConn = CreateObject("ADODB.Connection")
pConn.Open sConn
Set GetConnection = pConn
End Function
Public Sub CreatePivot()
Dim pConn As Connection
Dim pRSet As Object
Dim pCache As PivotCache
Set pConn = GetConnection
Set pRSet = CreateObject("ADODB.Recordset")
'Естественно, подключение и запрос могут быть и не привязаны к книге
pRSet.Open "Select * From [Articles$]", pConn
Set pCache = ThisWorkbook.PivotCaches.Create(xlExternal)
Set pCache.Recordset = pRSet
ThisWorkbook.Worksheets.Add
'True для чтения данных сразу
pCache.CreatePivotTable Range("A3"), "MyPivot2", True
End Sub
Спасибо, как запрос передать в PivotCache.Recordset я уже нашел , даже нашел как запрос передать в массив, а вот как массив в тот же PivotCache.Recordset :?:
Приношу свои извинения за отсутствие деталей и сумбурность. Мне казалось логичным, что следующий шаг понятен - переписать данные массива в свой Recordset.
Скрытый текст
Код
Public Sub ArrayToRecordset()
Dim vArray(0 To 1000, 0 To 2) As Long
Dim i As Long, k As Long, pRSet As Object
Dim pCache As PivotCache
'создаём массив целых чисел
For i = 0 To 1000
vArray(i, 0) = CLng(1 + 10 * Math.Rnd)
vArray(i, 1) = CLng(1 + 100 * Math.Rnd)
vArray(i, 2) = CLng(1 + 1000 * Math.Rnd)
Next
'создаём Recordset
Set pRSet = CreateObject("ADODB.Recordset")
For k = 0 To 2
pRSet.Fields.Append "F" & CStr(k), 3
Next
pRSet.CursorLocation = 3
pRSet.Open
'переписываем массив в Recordset
For i = 0 To 1000
pRSet.AddNew
For k = 0 To 2
pRSet(k).Value = vArray(i, k)
Next k
Next
pRSet.MoveFirst
'Подключаем Recordset, как источник данных сводной
ThisWorkbook.Worksheets.Add
Set pCache = ThisWorkbook.PivotCaches.Create(xlExternal)
Set pCache.Recordset = pRSet
pCache.CreatePivotTable Range("A3"), "PT" & CLng(Timer), True
pRSet.Close
End Sub
значит текст длиной 20 символов. Правильно я понимаю? т.е. если у нас есть таблица то нужно для каждого поля определить тип данных и прописать его в свойства поля... т.к. если поставить этот формат на числа - сводная не поймет что это число... Еще раз спасибо Вам. Очень многому научился.
Да всё правильно. Recordset - аналог таблицы базы данных поэтому для каждого поля нужно определить тип. В моём примере массив состоял из одного целочисленного типа, поэтому и для всех полей был указан он, иначе, если "миссив" Variant, то нужно задать для каждого столбца свой тип.
а как подсунуть этот кэш уже существующей сводной?
А кэш он ... разный бывает (5 типов). И не думаю, что можно поменять разнотипные. Впрочем, можете по экспериментировать с методами сводной таблицы ChangePivotCache, ChangeConnection
Dima S пишет: а как подсунуть этот кэш уже существующей сводной?
Цитата
anvg пишет: можете по экспериментировать с методами сводной таблицы ChangePivotCache, ChangeConnection
Подскажите плиз как это реализовать, как это должно выглядеть У меня есть сводная созданная на основе массива(320т.строк), чтобы добавить новые данные удаляю сводную и создаю новую с новыми данными В сводной сгруппированы поля по условию, после удаления вся эта наработка исчезает (( Если правильно понимаю необходимо удалить кэш и залить новый при этом и наработка не пропадет и новые цифры будут!?? P.S. думал создать сводную а потом группировать макросом но там как-то совсем не понятно ((
У меня есть сводная созданная на основе массива(320т.строк),
А что подразумевается под массивом? Если это некоторая таблица листа Excel, то просто в источнике данных поменяйте диапазон. Если это что-то, полученное, как обсуждалось здесь, преобразованием некоторого массива в Recordset и подсовыванием в качестве кэша, то по другому по-видимому никак (по крайней мере на момент завершения обсуждения у меня ничего не вышло). А главное в чём смысл такого варианта? Не проще ли хранить такие большие данные в какой-нибудь базе данных и уже по ним строить сводную? Некоторый тип сводных вполне можно получать запросом на группировку.
У меня Excel2003, так что сводную делаю на основе двумерного массива. С базами данных я не сталкивался У нас данные выгружаются в xls, а так все храниться в dbf файлах Это получается надо изучать базы данных (какую выбрать , как туда заливать и тд) А мож установить 2007 Excel и использовать xlsx файл как хранилище, написать макрос, заливать туда новые данные!??
Вам тогда проще скачать и установить Microsoft OLE DB Provider for Visual FoxPro (удобнее чем существующий Jet.OLEDB.4 тем, что позволяет задавать codepage для dbf-файлов) для выполнения запросов к таблицам dbf, создать подключение с запросом и уже затем это подключение использовать в качестве источника для сводной таблицы. После этого нужно будет только обновлять сводную без всяких "танцев с бубном". Стоку подключения можно взять Visual FoxPro connection strings (для Free table directory). Успехов.
Добрый всем вечер! Процедура ArrayToRecordset() очень классная, только подскажите,пожалуйста, возможно ли чтобы данные массива добавлялись не в "Выберите поле для добавления в отчёт", а часть полей добавлялась непосредственно сразу в сводную? Возможно ли это ? и что необходимо дописать для этого? Цель: тоже обработка файла большого размера (300 000 строк) для записи его в "накопительный регистр" после нахождения нужных слов регулярными выражениями
SvetaS_love написал: а хотелось бы, сразу в структуру таблицы
примеры заполнения полей Сводной в VBA - здесь - Как поставить заполненый Recordset в источник сводной таблицы и подключайте способности google - хорошие примеры кодов по Сводным - на globaliconnect, contextures (линки в той же ветке в #9) успехов
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)