1

Topic: Creation to the pivot table in Excel from basis Access

Good afternoon! It is required to create the pivot table in  from the several tables an ex-fur-tree allocated in different files. For this purpose I connect these tables with , then through sql request I unite them. Further I open an ex-fur-tree and I connect to it the data from request  and I create the pivot table. Thus I unite the data from several tables an ex-fur-tree and I provide possibility of their update at change of the initial table.
However, at attempt to connect the data from  request the error that there is no storage takes off. In the manager of tasks the ex-fur-tree during this moment consumes 1,7  operative storage.  the request connects eight bases, each of which weighs about 130 Mb.
Whether I do all correctly? Whether there is a possibility somehow to avoid shortage of storage? Can eat other methods to unite labels an ex-fur-tree in a uniform report, thus saving possibility of its fast update?

2

Re: Creation to the pivot table in Excel from basis Access

F;
There are 8 separate files (for example   March  May   ), it is necessary for me that in the ninth file there was their general report. And that at modification of any of files (for example in May) the general report was updated. I just also search for possibility it to make though as . In an ideal certainly without .

3

Re: Creation to the pivot table in Excel from basis Access

kudim;
Opened an ex-fur-tree, read an ex-fur-tree a file in the table, closed an ex-fur-tree, and so all 8 times. Then create request and so forth in , open an ex-fur-tree and work already with one only a file. So storages it should be spent less, and work easier, the table can be formatted as it is necessary.

4

Re: Creation to the pivot table in Excel from basis Access

alecko;
How to understand "read an ex-fur-tree a file in the table"?)

5

Re: Creation to the pivot table in Excel from basis Access

kudim, created the table - the general for all files, with indexes, signs, then

Dim objExel As Excel. Application, objsheet As Excel. Worksheet, objbook As Excel. Workbook
Set objExel = New Excel. Application ' a simple way - when the library is connected
Set objbook = objExel. Workbooks. Open (patname) ' a file
Set objsheet = objbook. Worksheets (1) ' the necessary sheet
' then create  the necessary table
Dim rst As Recordset
Dim SQL $
SQL = "tbl" ' the table
Set rst = CurrentDb. OpenRecordset (SQL, dbOpenDynaset) ' DAO
' after that we define data reading boundaries it is possible through UsedRange, it is possible to take what that a column (the general case - in tables happens the latent garbage)
Dim Lastrow&, NomStolb&, i&, Nach&, NomColumnPoisk&
NomStolb=3 ' for example such column
Lastrow = objsheet. Cells (objsheet. Rows. Count, NomStolb).End (xlUp).Row ' we search the maximum line in this column, to it we will read out
' went it is read out at sight and if it is pleasant it is showered in 
Nach=1 ' from this line we begin reading
for i=Nach to Lastrow
' it is passable on lines of our file an ex-fur-tree and it is showered in 
Type
NomColumnPoisk=5 ' for example
If objsheet. Cells (i, NomColumnPoisk)> 0 then
rst.addnew
rst (0) =objsheet. Cells (i, NomColumnPoisk)
.update ' DAO :)
end if
Next

Considered the necessary sheet, then other sheets, finished with one file then as and there it is possible to throw and remaining.
It it is possible to do all it is hidden, quickly enough.

6

Re: Creation to the pivot table in Excel from basis Access

kudim;
Access it is not necessary.
In nesting an example.
Data files:
- 1.xlsx
- 2.xlsx
- 2.xlsx
File application:
- Result.xlsm
If cannot open, here the code:
[spoiler]

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;" AND _
"User ID=Admin;" AND _
"Data Source = '" AND ThisWorkbook. FullName AND "';" AND _
"Mode=Read;" AND _
"Extended Properties ="" Excel 12.0 Macro; "";"
strSQL = _
"SELECT * FROM [List1$]" and _
"IN '" and ThisWorkbook. Path and "\Kniga1.xlsx '" and _
"[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Extended Properties ='HDR=YES; ']" AND _
"UNION ALL" AND _
"SELECT * FROM [List1$]" and _
"IN '" and ThisWorkbook. Path and "\Kniga2.xlsx '" and _
"[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Extended Properties ='HDR=YES; ']" AND _
"UNION ALL" AND _
"SELECT * FROM [List1$]" and _
"IN '" and ThisWorkbook. Path and "\Kniga3.xlsx '" and _
"[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"), "SvodTab _" and sht. Name)
' ptbl. HasAutoFormat = True ' it adjusts columns after reorganization till the necessary width
ptbl. AddDataField ptbl. PivotFields ("Quantity"), "amount", xlSum
' we Place a field "id" in area of lines of the pivot table
With ptbl. PivotFields ("id")
.Orientation = xlRowField
.Position = 1
.Subtotals = Array (False, False, False, False, False, False, False, False, False, False, False, False) ' not to deduce totals across the field
End With
' we Place a field "Title" in area of filters of the pivot table
With ptbl. PivotFields ("Title")
.Orientation = xlPageField
.Position = 1
End With
rst. Close: Set rst = Nothing
cn. Close: Set cn = Nothing
End Sub

[/spoiler]

7

Re: Creation to the pivot table in Excel from basis Access

kudim wrote:

Good afternoon! It is required to create the pivot table in  from the several tables an ex-fur-tree allocated in different files. For this purpose I connect these tables with , then through sql request I unite them. Further I open an ex-fur-tree and I connect to it the data from request  and I create the pivot table. Thus I unite the data from several tables an ex-fur-tree and I provide possibility of their update at change of the initial table.
However, at attempt to connect the data from  request the error that there is no storage takes off. In the manager of tasks the ex-fur-tree during this moment consumes 1,7  operative storage.  the request connects eight bases, each of which weighs about 130 Mb.
Whether I do all correctly? Whether there is a possibility somehow to avoid shortage of storage? Can eat other methods to unite labels an ex-fur-tree in a uniform report, thus saving possibility of its fast update?

Power Pivot!!!

8

Re: Creation to the pivot table in Excel from basis Access

Vakshul Sergey;
Thanks! Your example works with my basis, however with its help it is possible to connect only two files, to an amount of lines in everyone nearby 30 and weight about 40 Mb. At attempt of adding of the third and more files, arise an error "fields Are defined too many"

9

Re: Creation to the pivot table in Excel from basis Access

kudim, kind put;
I simply showed you a direction as it to make without Access.
Your files I did not see, I do not know, how at you the data on sheets is allocated.
If you did not change sampling of all fields SELECT * FROM [List1$] to certain fields which are necessary to you (them it is necessary to enumerate) the problem in it can. Probably at you a miscellaneous kol-in columns in files.
It is a little 30 thousand lines.
I in three test files increased number of lines to 100000 in everyone - errors are not present.

10

Re: Creation to the pivot table in Excel from basis Access

Vakshul Sergey;
Understood, I will understand. Thanks once again!

11

Re: Creation to the pivot table in Excel from basis Access

alecko;
I am badly familiar with a method recordset and consequently as I can not understand your code.
Explain, please, if can.

Set objbook = objExel. Workbooks. Open (patname)

- here I specify a way to a file where the basis lies?

SQL = "tbl" ' the table
Set rst = CurrentDb. OpenRecordset (SQL, dbOpenDynaset) ' DAO

- that this part of the code means? What should I inscribe instead of "tbl"?
I very long tried to investigate))

12

Re: Creation to the pivot table in Excel from basis Access

kudim wrote:

Set objbook = objExel. Workbooks. Open (patname)

- here we specify a way to a file which needs to be read

SQL = "tbl" ' the table
Set rst = CurrentDb. OpenRecordset (SQL, dbOpenDynaset) ' DAO

- instead of "tbl" the table title in Akse where the data is showered is inscribed