Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft Access Новый топик    Ответить
 Создание сводной таблице в Excel из базы Access  [new]
kudim
Member

Откуда:
Сообщений: 20
Добрый день! Требуется создать сводную таблицу в экселе из нескольких таблиц эксель, расположенных в разных файлах. Для этого я связываю эти таблицы с аксессом, потом через sql запрос объединяю их. Далее открываю эксель и подключаю к нему данные из запроса аксесс и создаю сводную таблицу. Таким образом я объединяю данные из нескольких таблиц эксель и обеспечиваю возможность их обновления при изменении исходной таблицы.

Однако, при попытке подключить данные из аксесс запроса вылетает ошибка, что не хватает памяти. В диспетчере задач эксель в этот момент потребляет 1,7 гб оперативной памяти. Эксесс запрос связывает восемь баз, каждая из которых весит около 130 мб.

Все ли я делаю правильно? Есть ли возможность как-то избежать нехватки памяти? Может есть другие способы объединить таблички эксель в единую сводку, при этом сохранив возможность ее быстрого обновления?
27 сен 17, 16:38    [20827245]     Ответить | Цитировать Сообщить модератору
 Re: Создание сводной таблице в Excel из базы Access  [new]
F
Guest
В самом Экселе нельзя сделать сводную без участия Аксеса?
27 сен 17, 16:53    [20827297]     Ответить | Цитировать Сообщить модератору
 Re: Создание сводной таблице в Excel из базы Access  [new]
kudim
Member

Откуда:
Сообщений: 20
F,
Есть 8 отдельных файлов (например янв февр март апр май июн июл авг), мне нужно чтобы в девятом файле была их общая сводка. И чтобы при внесении изменений в любой из файлов ( например в май ) общая сводка обновлялась. Я как раз и ищу возможность это сделать хоть как нибудь. В идеале конечно без аксесса.
27 сен 17, 16:57    [20827314]     Ответить | Цитировать Сообщить модератору
 Re: Создание сводной таблице в Excel из базы Access  [new]
F
Guest
Так и сделайте в Экселе через внешние источники данных.
27 сен 17, 17:00    [20827326]     Ответить | Цитировать Сообщить модератору
 Re: Создание сводной таблице в Excel из базы Access  [new]
alecko
Member

Откуда: Башкирия
Сообщений: 123
kudim,
открыли эксель, прочитали эксель файл в таблицу, закрыли эксель, и так все 8 раз. затем создаете запрос и пр. в аксе, открываете эксель и работаете уже с одним только файлом. так памяти расходоваться должно поменьше, да и работать попроще, таблицу можно отформатировать как надо.
27 сен 17, 17:01    [20827335]     Ответить | Цитировать Сообщить модератору
 Re: Создание сводной таблице в Excel из базы Access  [new]
kudim
Member

Откуда:
Сообщений: 20
alecko,
как понять "прочитали эксель файл в таблицу"?)
27 сен 17, 17:08    [20827375]     Ответить | Цитировать Сообщить модератору
 Re: Создание сводной таблице в Excel из базы Access  [new]
alecko
Member

Откуда: Башкирия
Сообщений: 123
kudim, создали таблицу- общую для всех файлов, с индексами, признаками, затем
Dim objExel As Excel.Application, objsheet As Excel.Worksheet, objbook As Excel.Workbook
Set objExel = New Excel.Application ' простой путь - когда библиотека подключена
      Set objbook = objExel.Workbooks.Open(patname)' файл
      Set objsheet = objbook.Worksheets(1)'  нужный лист
' затем создаете рекордсет нужной таблицы
Dim rst As Recordset
Dim SQL$
SQL="tbl" ' таблица
Set rst = CurrentDb.OpenRecordset(SQL, dbOpenDynaset) ' DAO
' после этого определяем границы считывания данных можно через UsedRange, можно взять какой то столбец (общий случай - в таблицах бывает скрытый мусор)
Dim Lastrow&, NomStolb&, i&, Nach&, NomColumnPoisk&
NomStolb=3 ' например такой столбец
Lastrow = objsheet.Cells(objsheet.Rows.Count, NomStolb).End(xlUp).Row' ищем максимальную строку в этом столбце, до неё будем считывать
' поехали считываем с листа и если понравится закидываем в рекордсет
Nach=1 ' с этой строки начинаем считывание
for i=Nach to Lastrow 
' проходим по строкам нашего файла эксель и закидываем в рекордсет
типа
NomColumnPoisk=5 ' например
If objsheet.Cells(i, NomColumnPoisk)>0 then
rst.addnew
     rst(0)=objsheet.Cells(i, NomColumnPoisk)
.update ' DAO :)
end if
Next

считали нужный лист, затем другие листы, закончили с одним файлом, затем так же и туда же можно закинуть и остальные.
все это можно делать скрыто, достаточно быстро.
27 сен 17, 22:14    [20827800]     Ответить | Цитировать Сообщить модератору
 Re: Создание сводной таблице в Excel из базы Access  [new]
Вакшуль Сергей
Member

Откуда: Харьков
Сообщений: 378
kudim,

Access не нужен.
Во вложении пример.

Файлы данных:
- Книга1.xlsx
- Книга2.xlsx
- Книга2.xlsx

Файл приложение:
- Result.xlsm

Если не сможете открыть, то вот код:
+
Option Explicit

Public Sub CreatePivotTable()
    Dim rst As ADODB.Recordset
    Dim cn As ADODB.Connection
    Dim strConnection As String
    Dim strSQL As String
    Dim ptbl As PivotTable
    Dim pch As PivotCache
    Dim sht As Worksheet
    
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                    "User ID=Admin;" & _
                    "Data Source='" & ThisWorkbook.FullName & "';" & _
                    "Mode=Read;" & _
                    "Extended Properties=""Excel 12.0 Macro;"";"
                    
    strSQL = _
            "SELECT * FROM [Лист1$] " & _
            "IN '" & ThisWorkbook.Path & "\Книга1.xlsx' " & _
            "[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Extended Properties='HDR=YES;'] " & _
            "UNION ALL " & _
            "SELECT * FROM [Лист1$] " & _
            "IN '" & ThisWorkbook.Path & "\Книга2.xlsx' " & _
            "[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Extended Properties='HDR=YES;'] " & _
            "UNION ALL " & _
            "SELECT * FROM [Лист1$] " & _
            "IN '" & ThisWorkbook.Path & "\Книга3.xlsx' " & _
            "[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Extended Properties='HDR=YES;']"
        

    Set cn = New ADODB.Connection
    cn.Open strConnection
    Set rst = cn.Execute(strSQL)
    
    Set sht = ThisWorkbook.Worksheets.Add
    
    Set pch = ThisWorkbook.PivotCaches.Create(xlExternal)
    Set pch.Recordset = rst
    Set ptbl = pch.CreatePivotTable(sht.Range("A1"), "СводТаб_" & sht.Name)
'    ptbl.HasAutoFormat = True 'Это подгонит столбцы после перестройки до нужной ширины
    
    ptbl.AddDataField ptbl.PivotFields("Quantity"), "Количество", xlSum
    
    'Поместим поле "id" в область строк сводной таблицы
    With ptbl.PivotFields("id")
        .Orientation = xlRowField
        .Position = 1
        .Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)  'Не выводить итоги по полю
    End With

    'Поместим поле "Title" в область фильтров сводной таблицы
    With ptbl.PivotFields("Title")
        .Orientation = xlPageField
        .Position = 1
    End With

    rst.Close: Set rst = Nothing
    cn.Close: Set cn = Nothing
    
End Sub


К сообщению приложен файл (Св_таблица.7z - 26Kb) cкачать
27 сен 17, 22:48    [20827841]     Ответить | Цитировать Сообщить модератору
 Re: Создание сводной таблице в Excel из базы Access  [new]
Focha
Member

Откуда: Москва
Сообщений: 284
kudim
Добрый день! Требуется создать сводную таблицу в экселе из нескольких таблиц эксель, расположенных в разных файлах. Для этого я связываю эти таблицы с аксессом, потом через sql запрос объединяю их. Далее открываю эксель и подключаю к нему данные из запроса аксесс и создаю сводную таблицу. Таким образом я объединяю данные из нескольких таблиц эксель и обеспечиваю возможность их обновления при изменении исходной таблицы.

Однако, при попытке подключить данные из аксесс запроса вылетает ошибка, что не хватает памяти. В диспетчере задач эксель в этот момент потребляет 1,7 гб оперативной памяти. Эксесс запрос связывает восемь баз, каждая из которых весит около 130 мб.

Все ли я делаю правильно? Есть ли возможность как-то избежать нехватки памяти? Может есть другие способы объединить таблички эксель в единую сводку, при этом сохранив возможность ее быстрого обновления?



Power Pivot!!!
28 сен 17, 09:50    [20828277]     Ответить | Цитировать Сообщить модератору
 Re: Создание сводной таблице в Excel из базы Access  [new]
kudim
Member

Откуда:
Сообщений: 20
Вакшуль Сергей,

Спасибо! Ваш пример работает с моей базой, однако с его помощью удается соединить только два файла, с количеством строк в каждом около 30тыс и весом около 40 мб. При попытке добавления третьего и более файлов, возникает ошибка " Определено слишком много полей"
28 сен 17, 12:02    [20828734]     Ответить | Цитировать Сообщить модератору
 Re: Создание сводной таблице в Excel из базы Access  [new]
Вакшуль Сергей
Member

Откуда: Харьков
Сообщений: 378
kudim, доброе день,

Я вам просто показал направление, как это сделать без Access.
Ваших файлов я не видел, не знаю, как у вас расположены данные на листах.
Если вы не изменили выборку всех полей SELECT * FROM [Лист1$] на определенные поля, которые нужны вам(их нужно перечислить), то может проблема в этом. Возможно у вас разное кол-во столбцов в файлах.
30 тысяч строк это немного.
Я в трех тестовых файлах увеличил число строк до 100000 в каждом - ошибок нет.
28 сен 17, 12:34    [20828818]     Ответить | Цитировать Сообщить модератору
 Re: Создание сводной таблице в Excel из базы Access  [new]
kudim
Member

Откуда:
Сообщений: 20
Вакшуль Сергей,
Понял, буду разбираться. Спасибо еще раз!
28 сен 17, 12:53    [20828842]     Ответить | Цитировать Сообщить модератору
 Re: Создание сводной таблице в Excel из базы Access  [new]
kudim
Member

Откуда:
Сообщений: 20
alecko,
я плохо знаком с методом recordset и поэтому ни как не могу разобраться в вашем коде.
Разъясните, пожалуйста, если можете.
 Set objbook = objExel.Workbooks.Open(patname)
- здесь я указываю путь к файлу, где лежит база?
SQL="tbl" ' таблица
Set rst = CurrentDb.OpenRecordset(SQL, dbOpenDynaset) ' DAO
- что означает эта часть кода? что я должен вписать вместо "tbl"?

Я очень долго пытался вникнуть))
28 сен 17, 15:56    [20829408]     Ответить | Цитировать Сообщить модератору
 Re: Создание сводной таблице в Excel из базы Access  [new]
alecko
Member

Откуда: Башкирия
Сообщений: 123
kudim
 Set objbook = objExel.Workbooks.Open(patname)
- здесь указываем путь к файлу, который нужно прочитать
SQL="tbl" ' таблица
Set rst = CurrentDb.OpenRecordset(SQL, dbOpenDynaset) ' DAO
-вместо "tbl" вписывается название таблицы в Аксе, куда закидываются данные
28 сен 17, 17:53    [20829885]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft Access Ответить