Setting Pivot Rawsource...

[pedie]

Hi, maybe kinda weird but let me try...Can vba set Pivot Rawsource to Access Database? that when the database is updated it reflects in Pivot in Excel?


Thanks in Advance!

 

[Derek Brown]

I am sorry but I don't understand the term "Rawsource".
However, in Excel 2007/2010 you can link an Access table or query to a worksheet as an Excel table, pivot table or pivot table and pivot chart. You can then set the 'Connection' to refresh on opening the workbook or, at any time, by right mouse-clicking a cell in the table and selecting 'Refresh'.
(You would need to use Data | From Access)
Oh, and that Access table could itself be a table linked to another source (Excel workbook, text file, another database etc.)

 

[pedie]

Hi Derek, I am referring to 'Pivot table source data' to be access and then pivot be in excel.
From the way you explained i think it is very much possible...

How, whay step exactly do i follow...Please help me through this process...


Thanks alot for giving me a start!

 

[Derek Brown]

I am using Excel 2010 at home (2007 at work) but the process is the same.
Open Excel and select cell A1 on a blank worksheet.
On the ribbon's DATA tab, click 'From Access' and navigate to your database.
Select the required table or query.
(I prefer to use queries as I can filter the columns to those required at that point and reduce the size of the workbook)
Click OK.
In the 'Import Data' dialog window, select 'PivotTable Report' and check that the destination is correct (e.g. existing sheet, cell $A$1).
Click 'Properties'.
On the 'Usage' TAB, you may want to uncheck 'Enable bakground refresh' so you will get the hourglass while the refresh takes place (you will then know when it has finished after opening the workbook).
Tick the 'Refresh data when opening the file' if this is what you want instead of doing a manual refresh.
On the 'Definitions' TAB, look for the words "Mode=Share Deny Write" and change this to "Mode=Read" if you do not want to risk locking the database.
If a password is relevant you may want to tick "Save Password". I believe that you would be prompted for the password when first used in a session - you may have to enter the password again after rebooting the PC.
Click OK.
Now build your pivot table as you would normally do so.
If you want to later look at, or change, the properties use Data | Connections | Properties (not Data | Properties).
You can right mouse-click inside the pivot table and select refresh at any time.

 

[pedie]

Derek, you solved a BIG...problem..thank you very very much!!!
Now i dont have to worry much, because sometimes excel becomes really weird when file size goes to 10 mb etc

Thank you again!

 

[Derek Brown]

Don't I know it - I still have three 38Mb+ report workbooks to convert to use my Data Warehouse that feeds data, via Access tables and queries, from an IBM iSeries database, Excel workbooks and other Access databases. It will be a relief when I get that done!
Pleased to have been able to help - but update the post if you encounter a problem with it.

 

[pedie]

Derek, just one more inquiry...
As far as i think....the more size the Access database is the pivot excel workbook too will increase it size correct?

And is there a way...currently the database is protected...so when i tried i manually entered the password..is there a way to enter the password in the connec but be invisible to users who uses this pivot/workbook?

Thanks again
Pedie

 

[Derek Brown]

My perception is that Microsoft Access databases have always suffered from 'bloating' and need to be compacted & repaired from time to time to reduce the size (after creating a backup copy first of course).
However, in the year or more since I created the Data Warehouse I have not noticed any impact on the size of Excel workbooks with links to a database - other than the size increase expected by the addition of new data. However, I don't often link data as a pivot table. I can only suggest that you monitor it and see.

The password is a problem and I don't have that issue because of the way that I created the Data Warehouse (see my post #6 at http://www.mrexcel.com/forum/showthread.php?t=504537 for a more detailed description). The workbooks link to the database in the Informational Access Layer and require no password (normal permissions control access to the network drive) - so the password is only used in the Data Access Layer and therefore stored on the PC to which only I (and one other) have regular access. For our requirements, data will always be "as at yesterday" with the processes running overnight. There is no link back to the original data via the 'filtered' database data in the Informational Access Layer.
It is possible to create a DSN (Data Source Name) for the connection but that will not solve your problem as stored passwords could still be seen and the DSN would need to be created on each PC using the workbook.
It may be worth seeking advice from your IT department?

 

[pedie]

Derek, thanks again for useful information.
I really do appriciate your help.

This question is leading me to one more thing....hmmm

Suppose i have annual data in database but want pivot to show me monthly data only how would i control that from excel?
Please tell me the possible way.

Thanks again!

Regards
Pedie

 

[Derek Brown]

I would probably consider three options. In order of my personal preference:
1. If the data is for this month, last month or a month that can be identified 'automatically' in the selection criteria, I would use a query in Access to filter the data before it reaches the Excel workbook.
2. For 'variable' month selection by the user I would, instead of a linked pivot table as already discussed, create a workbook table using VBA utilising ADODB code and a select statement that gets the month (or date range) from user interaction. The created table would then be used in a pivot table etc. Now the worksheet containing the downloaded data could be 'very hidden' from the user and the code module password protected so that the database password would not easily be seen. However, it must be noted that Excel password protection can be 'broken' by anyone determined enough. In addition to an understanding of Access you would need to be able to create SQL - this could be done by first creating the query in Access and modifying the SQL resulting from that.
3. Last option would be to use MSQuery. Here I could not help you very much as I have never used it (always being my last option). You could try starting a new post to ask for help or start by looking at
http://office.microsoft.com/en-us/excel-help/customize-a-parameter-query-HP005199548.aspx