Pivot table data source #2

How can I edit the source (mdb file) for a pivot table in 
Excel?

We have several sources of data stored on a local hard 
drive and need to be able to move them to the server for 
all to use.  We also develop in one location and then move 
to a production environment.  I will need to edit the 
source for this also.

I was able to find something on the internet about a .oqy 
file for editing the source of an olap.  I assume I will 
be looking for something similar to this.  Where would the 
query file be stored by default?  I'm using Excel local to 
my computer, but the files are on the server.

Thank you in advance.
0
8/4/2003 4:48:11 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
354 Views

Similar Articles

[PageSpeed] 3

Here's one way. Run the following in your development environment,
having changed the code to use the correct pivot table. It will print in
the Immediate window the connection string of the pivot cache
corresponding to the pivot table you specify (PivotTable1 on
Sheet1in this example).

Public Sub ResetPivotTableConnection()
  Dim pc As PivotCache
  Dim pt As PivotTable
  Dim str As String
  Set pt = Sheet1.PivotTables("PivotTable1")
  Set pc = ThisWorkbook.PivotCaches(pt.CacheIndex)
  Debug.Print pc.Connection
End Sub

Now replace the Debug.Print statement with code to reset the
connection string. Use the string in the Immediate window as
your template. The only thing you need to change is the
database path specified in the DBQ= parameter. Most of the
parameters do not need to be specified because they will
be set to the defaults. For example on my PC
it would be something like:
  str = "ODBC;DSN=MS Access Database;"
  str = str & "DBQ=C:\Test copy of db1.mdb;"
  pc.Connection = str

Regards,
Rob Rutherford

"Melissa" <littlebutterflyz@hotmail.com> wrote in message news:094c01c35aa8$30fb3180$a601280a@phx.gbl...
> How can I edit the source (mdb file) for a pivot table in
> Excel?
>
> We have several sources of data stored on a local hard
> drive and need to be able to move them to the server for
> all to use.  We also develop in one location and then move
> to a production environment.  I will need to edit the
> source for this also.
>
> I was able to find something on the internet about a .oqy
> file for editing the source of an olap.  I assume I will
> be looking for something similar to this.  Where would the
> query file be stored by default?  I'm using Excel local to
> my computer, but the files are on the server.
>
> Thank you in advance.


0
rob4110 (3)
8/4/2003 7:35:04 PM
Reply:

Similar Artilces:

routing issue #2
we have two routers, one is attached to an ISA server which is used mainly for the internet one is attached to the exchange server which is used solely for email the exchange server is sending mail throo the ISA router server - how can i make it use the exchange router ? - the IP of the ISA router is showing in the email headers mark Hi, I presume the default gateway of the Exchange server is the ISA router? Therefore it will always route through it. This is all theory, as I haven't got the time to test it at the moment: Add another IP to your Exchange server, which DFG is the r...

Items options table name???
I am trying to find the name of the table that holds the "item option" settings. It is located under configuration-->options tab. This is where you check or uncheck configuration settings of the pos software and the folders are called Customer options, General options, Item option, POS options, and so on. Thanks Dave Dave - look in dbo.configuration; there's a couple of entries for "options", but I don't know off the top of my head the specific options tracked. I have not seen a separate table that tracks "options". Hope this helps... "D...

Sorting pivot table by specific field (column)
Hey guys (and girls), Anyone know how to sort a pivot table by a specific field (not the total sum of the fields)? For instance, if I have 5 years of data 2002-6 and 20 countries. If I make a pivot table of these I get 21 rows (the countries + total) and 6 columns for years (5 years of data + total). If I use the Field Settings --> Advanced --> Sort by field the Pivot table (PT) will be sorted by the sum of the different fields. What if I don't want this, but rater want to sort it by for instance year 2003. How do I do that? Anyone have a nice and clever solution? Much appreciated...

delete data between subtotals
I would like to delete all data between my subtotals without the subtotals changing. I tried to collapse the whole sheet and to copy the then visible data (with paste values only). this did not work. is there any other way to get rid of data between subtotals? appreciate your help. please see attachement. Attachment filename: illustration_1.xls Download attachment: http://www.excelforum.com/attachment.php?postid=446537 --- Message posted from http://www.ExcelForum.com/ Hi see Peo's response in your original thread -- Regards Frank Kabel Frankfurt, Germany >...

probably a simple table problem
Hi there. Ok, I have a table with 5 columns which I keep the data in. On another sheet I want a table which arranges the data from that tabl in ascending order, and throws out the values which have a 0 in it. Here's an example Barn - 1 - 0 - 1 - 2 farm - 0 - 2 - 1 - 0 firm - 2 - 1 - 0 - 0 A drop-down list picks if you arrange the data depending on column 2 3, 4 or 5. Column 1 is never listed as a choice (of course, as it' just the name for the thing). Let's say we pick column 1, then the table I want puts firm at the top then barn, and doesn't show farm. How can I do th...

Is there a file where the Linked Table Manager information is stor
I am hoping that there is a script within Access where the Linked Table Manager link information is stored. I have a number of files, over 300 that I need to move and I am hoping that I do not have to restore the link one at a time. I am hoping I can open and edit a script and make it all happen easily. Thanks Dave There is the hidden MysObjects system table. However I don't think that you are allowed to update it manually, and if you do and mess it up, you'll probably trash the database. Therefore make a backup first. Here's a little query to show the lin...

=?iso-8859-1?Q?=22microsoft_office_document_imaging=22_documents=3F?= #2
Is there a program available for Mac that can read "microsoft office document imaging" documents? I have a number of email attachements sent to me with "filename.mdi" that I need to open Can they be opened on a Mac running OS X? ...

Cascading Combo for Chart Source
I have found some examples of how to use a combo to set the data source of an excel chart (see http://peltiertech.com/Excel/Charts/ChartByControl.html), using a combo to set an offset. In this case I'd have a combo box of counties which return number which is used as the offset. The relevant row is then used as the source for the chart. COUNTY WEEK01 WEEK02 WEEK03 WEEK04 Staffordshire 1.1 1.2 1.4 2.1 Derbysire 2.1 3.3 4.1 2.2 .... However, I have another spreadsheet that has the monitoring stations within counties. There are hundreds so it wouldn'...

Table Cell
I have created a form using a table. I want to lock or block cells that should not be changed. How do you do this? ...

How to show a count of Unique IDs in a Pivot Table
Is there a way to use Pivot tables in a way that shows a count of Unique IDs (over time). The problem I am having in using the Pivot table to show trended data, is that the "Count" option produces a count of each record that has any information, when oftentimes I simply need a count of unique IDs. So the below Column would have a count of 3 Names, when I prefer for it to produce a count of 1 Name. Any recommendations would be appreciated..... Name --------- David David David Excel 2007 PivotTable Count Unique IDs http://www.mediafire.com/file/dgmmaznjyy2/06_21_09.xlsx Thanks ve...

Multi Table Inser Query
Hello ... im new to Access so i need some expert help. im creating an application for my organization where i have this one form in which i collect all the required data and need to save it in table but the problem it this form is connected to to different tables. how can i save the data via query on one click to two different tables ? If you have several tables, each with essentially the same fields, and you need to connect up your form to different tables at different times, you have not built a database. That kind of thing might make sense in a spreadsheet, where you create a sheet ...

External data query using MAX/SUM
I'm trying to create an external data link using Microsoft Query. Ho do I write SQL in Microsoft query that will return a max date and su an amount field for the max date only? Current SQL is as follows: SELECT DISTINCT CB_PORTFOLIO_DMN.PORTFOLIO_NAME Max(CB_POOL_PORTFOLIO.POST_DATE), Sum(CB_POOL_PORTFOLIO.EXPOSURE_AMT) Count(CB_POOL_PORTFOLIO.NBR_AVG_MATY_YRS) FROM CB06U.CB_POOL_PORTFOLIO CB_POOL_PORTFOLIO, CB06U.CB_PORTFOLIO_DM CB_PORTFOLIO_DMN WHERE CB_PORTFOLIO_DMN.PORTFOLIO_ID = CB_POOL_PORTFOLIO.PORTFOLIO_ID GROUP BY CB_PORTFOLIO_DMN.PORTFOLIO_NAME This returns the sum of the exp...

jumping data
hi i have a form which shows customer details, and this has a subform where you can enter the date and notes about the customer. the subform is in a datasheet view. so everytime you want to add some notes about the customer, you enter the date and write the notes. there are many customers on the database, and we make quite a few notes for each customer. Now when i go into the customer and put in a note, then come out and go back into it, the data has moved up a few columns and goes into the wrong place, rather than stay in the same place where it was entered. also when...

how to insert chart for imported data in a report?
plz reply soon if you know Please use the large white space under the subject to enter a detailed question. It also helps to read some past threads that might pertain to your question (whatever it is). -- Duane Hookom Microsoft Access MVP "varsha from pilibhit" wrote: > plz reply soon if you know ...

Outlook client for CRM V3
We have a client who has users who want to make changes to the 'my contacts' local data group, so they sync their Outlook contacts with CRM. They all use the desktop client. But the save button is greyed out. They have a CRM role assigned with the Outlook sync permission and they have local admin rights. I've also checked they have write permissions to the C:\Documents and Settings\User.Name\Application Data\Microsoft\MSCRM folder and it's subfolders. What else could be disabling the save option? Becuase My Contacts is a default group, I don't think you can make c...

Moving Data Automaticaly
Can you help I have a table (part shown below) the Date and Day moves automaticaly to left at midnight - is it possible to get the rest of the data to move as well 25-Nov 26-Nov 27-Nov 28-Nov Sun Mon Tue Wed 07:00 07:00 TBA on off on off on off on off 1 1 1 1 1 1 1 1 1 1 1 1 1 1 -- JohnM Please explain meaning of "date and day MOVES to left" Are you using conditionally formatting to highlight the cell with the current date? best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bli...

Test linked form for data
Hi Groupies How can I test to see if a linked form, that might not be visible, has data? My main form has a yes/no box that enables a button when it is set to yes. The button opens a linked form. When the user moves to the next record, I would like the main forms BeforeUpdate event to make sure that the yes/no box is not set at No while there is data in the linked form for that record. I have tried something like: If Me.Form.frmVehicles.RecordsetClone.RecordCount > 0 And Me.CompanyVehicle = False Then Cancel = True MsgBox "Please check vehicles", vbOKOnly Me.f...

Data label above columns??
Hi, I have some stacked column charts and I want the data labels to appear just above each column. Is that possible? I'm using excel 2003 version and the only options i can see for the label positions are "inside end", "center" and "inside base" - so at the moment I'm moving each data label manually and this is very time concuming! Thanks in advance for any advice you offer :) K I'm afraid it's not an option due to the confusion it would cause. If the label is above the stack it refers to, it could be sitting on top of another stack, th...

Date format problem #2
Dear All, Please suggest me the solution of my problem, as i m handling data for 60,000 in no.Some body in data entry has enter the date in a cell in gernal format e.g 20.03.2003 tht is 20th march 2003, i wnt to convert in the date fomat,like 3/20/03.. i tried ..but i failed to tht please suggest some alternative solution for tht..becoz it is not possible to do it mannually waiting for the response Thanks NISHANT Nishant Try using Data/Text to Columns. and assign the information as a date. Andy. "Nishant" <nishant.khare@wipro.com> wrote in message news:090201c37ce0$1b...

Pivot table sorting #2
Hello, I have a pivot table report for partnumber and values. This report is based on a database containing details like partnumber source import flag Description total value... etc. In my report i would like to sort the table in descending order of total value. I have tried advanced option by double clicking the total value field and changing the autosorting option to descending(total). But i am not getting the required result. My version of excel is Microsoft office excel 2003. Pivot table report is given below Source (All) Imp (All) Sub (All) Description (All) ...

convert time into decimal #2
=IF(E44="","X",E40-E44) the formula above where E40 is the time now, and E44 is the time of 2 hours ago. I want to return 2 instead of 2:00 PM. How do I do this? I do this but it does not work (E40-E44)*24. ...

comapring 2 seperate workbook
HELP!!! Is there any way you can compare 2 workbooks. for example, would be looking for a match (of values) from single columb o workbook 'a' to values of single columb from workbook 'b'. When eve there is a match between these two, maybe I can write it on ne workbook?(automatically). Or if there is any other tricks, please le me know. (I am sick of comparing it maually and when there is a match I am cutting and pasting to new workbook) -- Message posted from http://www.ExcelForum.com Hi as a starting point have a look at: http://www.cpearson.com/excel/duplicat.htm#Ex...

CRM 1.2 SBS 2003 Can not install exchange email router
Logged on as domain admin throughout. Installed CRM successfully, followed install instructions, did not install to local host installed web to a hostheader of "companyweb" port 80. during Exchange email router setup I get an error when specifying CRM server and web - "one or more os the MS CRM servers you listed is invalid or you do not have administrator priveleges on that server...." server name: sbssrb01 web http://companyweb If I go into IIS can not open CRM web get - http Error 403 - Forbidden: Access is denied. All services are on 1 server "sbssrv01&qu...

Importing Calender data from MS Project
Hi, Has anyone has any experience exporting their calender data from MS Project into the MS Outlook calender, can this be done ?? Thanks Pete ...

arrow keys #2
To move from cell to cell, I used my arrow and enter keys. They no longer work in Excel. I have to click on each cell to get there. Do you know how to fix it? Thanks. What happens when you use the arrow key? Check if your scroll lock is on or if you have something under tools>options>transition -- Regards, Peo Sjoblom "Shirley Smith" <anonymous@discussions.microsoft.com> wrote in message news:079201c3af8e$fa3acc00$a001280a@phx.gbl... > To move from cell to cell, I used my arrow and enter > keys. They no longer work in Excel. I have to click on > eac...