Pivot Table Refresh Macro

I found this code for creating a macro that refreshes a pivot table's
connection to a database that's in the same folder as the excel file. 
I want to be able to change the path so it will reference a database in
another location.  When I change the ActiveWorkbook.Path to the
database path "Q:\RLT\RLT.mdb", it says it's connecting to the
database, but the new data does not update the pivot table.  How should
I modify?

Sub RefreshPivot()
Dim myConnect As String
myConnect = "ODBC;"
myConnect = myConnect & _ 
"Driver={Microsoft Access Driver (*.mdb)};"
myConnect = myConnect & "DBQ="
myConnect = myConnect & ActiveWorkbook.Path
ActiveWorkbook.PivotCaches(1).Connection = myConnect
ActiveWorkbook.PivotCaches(1).Refresh
End Sub


---
Message posted from http://www.ExcelForum.com/

0
12/18/2003 12:30:56 AM
excel.misc 78881 articles. 5 followers. Follow

0 Replies
532 Views

Similar Articles

[PageSpeed] 31

Reply:

Similar Artilces:

Problem with _UNICODE macro
Hi all. I have a MFC programme. There are many funtions that like AfxMessageBox, CString.Format... At the original I compile it in MBCS mode so there is no problem but when I change to compile it in UNICODE mode I have to add "L" before any constant string. Is there any other way that I can compile my program but I don't have to add "L" before constant string? Please help me. Look up "Using Generic-Text Mappings" on MSDN -- Regards, Nish [VC++ MVP] http://www.voidnish.com http://blog.voidnish.com "Binh Nguyen Van" <binhnv@newcenturys...

CWebBrowser2 and Refresh
Hi All, I am using CWebBrowser2 control to get values from a website then plot them on a chart. When I use Navigate2(..) I can handle the OnDocumentCompleteExplorer(..) message to parse the html file and update my chart. However, I don't always get an updated page, can someone give me an example of how to use Refresh2(..) to make sure I always get a non-cached page? Also, is there a message sent when Refresh() or Refresh2() has finished? Or maybe there's another way I can achieve what I want? Cheers, Rob ...

Run macro via cell value
Is it possible to run a macro based on the value of a cell. Say for example A1 contains the word "run" can that word be the trigger to automatically run a macro? Pat Hi Pat A worksheet formula can not trig a macro. No way. But a worksheet has several events; macros that run when certain things happen. There is an event firing when you enter something into a cell, another when the sheet recalculates, ... see http://www.cpearson.com/excel/events.htm for more on this. HTH. Best wishes Harald "Pat" <glass_patrick@hotmail.com> skrev i melding news:OV4A5QBUFHA.58...

Compare mulitple fields in two tables
I have two identical tables. Information is input into each table by two seperate sources. I would like to have a query that compares the fields in each table and list those records with any field that does not match. The code below does not work, but I am trying to get something along these lines that does. I want it to compare 3 fields in two idential tables and list the CLNum if any of the fields do not match. SELECT tblCalAudit.ClNum FROM tblCalAudit INNER JOIN tblCalField ON (tblCalAudit.[LeakY/N] <> tblCalField.[LeakY/N]) OR (tblCalAudit.LossLeak <> tblCalField.Los...

modifiying macros
I understand how to record a macro. But if I want to modify it (to repeat throughout the sheet), how do I access the code to mofidy? Thanks, KBV Alt + F11, double click on Modules then double click Module1. Should see your recorded code there. (May be other modules...ie 2, 3 etc.) Or right click on the sheet tab and click View Code. HTH Regards, Howard "KBV" <KBV@discussions.microsoft.com> wrote in message news:3D68B8D1-DA6E-42C7-ACDC-92DDE6CD4EEC@microsoft.com... >I understand how to record a macro. But if I want to modify it (to repeat > throughout the sheet),...

Excel Macros 2003 into 2007
I have created a button (using the VB toolbox) in a 2003 version of excel. I then saved the workbook and opened it up in excel 2007. When I click on the button it runs through the macro behind it. However when I right click on the button there is no functionality and so I can't see the code behind it. I have other macros written in 2007 and I can access them but not the 2003. So how can I view my original macro? Is there any way to view the Visual Basic Tool Bar in Excel 2007? TIA -- Lyndsey Wood www.totalsolutionmapping.com If the button is from the Control ToolBox then you h...

Can't find macro in an Excel file to delete it
One of my Excel data files opens with the Macro Security Warning. I don't know of a Macro in this file. If I go to the Tools menu and open Macros, it does not show any macros. How do I delete this? Is there a macro hidden that I can't see? Try this, Ron: http://www.officearticles.com/excel/remove_vba_code_from_a_workbook_in_microsoft_excel.htm ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Ron" <Ron @discussions.microsoft.com> wrote in message news:460D615C-04A6-4165-B3B6-9A7F921FC3FF@microsoft.com... > One of my Excel data...

Auto-Refresh Sharepoint
I would like to know how this is normally accomplished out there in the real world. We're looking at putting some of our Excel Reports out on our Sharepoint Intranet - Naturally, we need to set up a DSN on the server. But up until now, we've had developers refreshing the data locally, on their own machines, and then sending the Excel file to the manager who needed it. Doing it this way, we only had to use Windows Authentication. First - is this commonly done? Second - how do you setup the authentication? Any input would be appreciated. ...

Add current record to table
I'm using Access 2007. I'd like to use a command button on a form to populate the current record's information to another table. I thought using an append query (which would be activated by the command button) would work but I'm not getting the results I want. How do I select the current record on the form so only this record is appended to the table? Table1 is the table behind my form. And Table2 is the table I'd like the append query to write to. Both tables have auto number primary keys. Thanks for your help. Tina Maybe this link will give you...

getting writable table objects of a document
Hi, I want to change some properties of all the tables in a document, but I don't know how to get writable objects of tables from the document. mjlaali wrote: > Hi, > > I want to change some properties of all the tables in a document, but > I don't know how to get writable objects of tables from the document. Dim myTable As Table For Each myTable In ActiveDocument.Tables ' do something with myTable, for instance: myTable.Columns(1).Width = InchesToPoints(1.5) Next -- Regards, Jay Freedman Microsoft Word MVP ...

Data Table in Clustered Column Charts
I have created a pivot table with these data: Count of User Year Unit 2006 Grand Total A 10 10 B 8 8 C 7 7 Grand Total 25 25 After that I created a Clustered Column Chart, and I am able to create a data table with the values as shown from the table. I would like to include another set of data as follow: Unit percentage A 1.27% B 10.39% C 70.00% Is there any way that I can include these data (which is in the same excel screen) in the same graph? ...

Setting up macro betwen two sheets
In sheet 1 I’ve many columns. Out of that I’ve a column with market info in column A and unique product ID in column B. Like that I’ve 100s of rows… I also have another sheet where I’ve many columns. Out of which I have same unique product IDs (total number could vary by more or less) in say column C with a different column with associated PO# and SO# respectively in column D and E. I like a macro to pick the unique product ID on sheet 1 and go to sheet 2 and scan thru the column C to find the same unique ID and take the corresponding PO (from col D) and SO # (from col E) an...

Can I read a range of scenario (input) values from a table?
When creating several scenarios (to ultimately get a scenario summary), it's laborious typing in a scenario name followed by a scenario value for each scenario. Is there any way of setting up a table of (names and) input values and just reading them into the scenario manager? ...

People/Entities....always add to people/entities table?
Hi all, I've been running into this recurring issue lately and I'm wondering if someone has any feedback/direction they can give. I find that there are situations where, in an app, I might want to attach someone's name to a particular record yet I know they will never need attaching to any other record again. I've set things up just like with all other names...they are added as 'entities' (to the entities table). However, it seems like this is going to bloat that table and also bloat my combo boxes based on entities (or, at least 'people' entities). D...

Merge two tables
Hi there I inherited information from a colleague who had created 2 tables instead of one table with all the information. How do I combine the information from the 2 separate tables into 1? Essentially the original 2 tables are two different halves of the same survey, Survey 1 and Survey 2. Thank you in advance. On Fri, 4 Dec 2009 20:01:01 -0800, forest8 <forest8@discussions.microsoft.com> wrote: >Hi there > >I inherited information from a colleague who had created 2 tables instead of >one table with all the information. > >How do I combine t...

Make table query 12-09-09
Can you set the datatype within a make table query. I want to add a new field which I want it to be yes/no but it comes out as number 0 in the table schema. I like it to be yes/no. Are you saying that the field that's created is a Numeric field, or that it's a Yes/No field, but it displays a number, not the words Yes or No? You have very little control over Make Table queries. That's why the recommended approach is usually to create the table first, and then append to it. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) ...

Macros to delete check boxes
I need to find a way to delete the check mark within a checkbox button so the customer can clear chcked items. I tried a macro but it will not uncheck the box. Do you have a solution. Thanks -- Gary Baker Gary Depends if you are using checkboxes from the forms tool box or the control one (ActiveX). This code will do both. The first line clears all the forms checkboxes, the for each...next loop goes through the collection setting all the values to false (un-checked) Sub Uncheckboxes() Dim wks As Worksheet Dim ckbx As OLEObject Set wks = Worksheets("Sheet1") With wks .che...

Linking Tables 01-14-08
I need some advice on basic table set-up / linking I have a student record table with a unique identifier (student ID) Contains student name and address I have an enrollment record table with the same unique identifier (student ID) Contains term and year attended Now I would like to create a third table to record transcript orders. The unique identifier would be student ID. However, I would also like the student name to appear in this table (it is stored in the student record table). How do I get the name to show up in this new table without duplicating the information? Create a q...

How do I set up a league table in Excel?
I want to set up a spreadsheet to chart NRL results, such that I can input the results of each game and it will sort teams by their current ranking round by round. Not sure if you received your answer, I assume you are looking for the sheet to automatically sort you data? "Steven Jones" wrote: > I want to set up a spreadsheet to chart NRL results, such that I can input > the results of each game and it will sort teams by their current ranking > round by round. > > . > ...

AutoExec Macro not passing OpenArgs
I have an AutoExec macro that uses RunCode to run the following code: Public Function StartDB() Dim CheckLink As Boolean CheckLink = True DoCmd.OpenForm "fStartUp", acNormal, , , , , CheckLink DoCmd.Close acForm, "fStartUp" End Function When I run the AutoExec macro by running the macro manually it works fine. However, when it runs at database start up the OpenArgs value does not get passed to the fStartUp form. It shows a Null value. I ran across one post in 1998 where the same thing was happening but I saw no resolution. Can anyone point me in the right...

Pivot table problem #5
I have a pivot table made from last year, I deleted all of the data and I added some new data. When I go to the dropdown to filter the data, the data from last year is still a choice along with the new data. I would like to delete this data from the choice on the filter, without having to create a new pivot table. I have refreshed the data, and I still have this problem. ...

macros
I'm new to macros and excel........ I have several excel spreadsheets that I need to filter daily. I would like to create a macro that can segment the system_ID with only the following numbers: (761,628,633,638,773,644,655,660,781,661,712,799,605). What I would like to happen is everytime I receive a spreadsheet filter by selecting the macro to group these numbers into a separate worksheet or something else that is visual. thanks! Util, If your system ID's are in column A, with a header in row 1, you can filter the sheet by using the macro below. HTH, Bernie MS Excel MVP Su...

Pivot Help.. Running Total
I have two pivot data fields: Time Amount Name I've grouped time by Year/Month I've changed my Amount field settings to Average, Running Total in 'Year'. This way I get a YTD average amount for each name. Now, here is my problem. I'd like to know how to set up a field that will calculate the % Difference From the YTD Average for the prior year. It's like a 'double' calculation. Is this possible? Is there a 'calculated field' set up I can use? I tried Calculated item.. but it doesn't work with Averages.. and it is important that I stick with a...

Multiple MS Access table sources for pivot table
Hi I would like my pivot table to draw data from several tables in an Access database. Although I have gone through the query wizard and added the fields from all the database tables, I still find that when Excel reads the data into the pivot table it stops after the first table and I only get part of the source data I am looking for. What am I missing in order to consolidate the data from several tables? with thanks. I don't think it is possible, you can use multiple excel sheets/tables (although a lot of the functionality gets lost) but not multiple access tables. -- Regards,...

last name May sorted as Month in pivot table
I am importing data into an excel 2002 spreadsheet. The data has a field called Last Name that is formatted as text. In a pivot table the last name of May shows up first. I have verified that excel thinks it is a date. I have unclicked auto formatting and clicked preserve formatting among other things. This field keeps reverting to general formatting. Any advise? Thanks in advance susanne In your source data, you can replace the name May with May<space>, and it should sort properly. To change the names, select the column, and choose Edit>Replace In the Find what box, t...