OLEDB Data Connection And PivotTable Report
May 8, 2014
I am connecting Excel to a database and there is a field called Data Created which is timestamp. Data comes out nicely in a Pivot Table
1. I would like to be able to group by month but the grouping fiction is grayed out
2. How can we convert the timestamp to only date format. I am able to select only Date for that column but it seems that it keeps the time, even though it only shows the date in the pivot table the bar above shows date and time.
View 1 Replies
ADVERTISEMENT
Aug 28, 2009
Since I'm making a PivotTable with my macro, when it runs I always get this...
‘The PivotTable report will not fit on the sheet. Do you want to show as much as possible?’
I always choose yes, because in the end the user isn't going to use all of the data, they will be narrowing it down. Anyway, this comes up a few times througout the macro. Is there something I can put in the code that, if pop up box, always choose yes?
View 2 Replies
View Related
Oct 2, 2008
I have a macro that takes the date from different excel sheets, consolidates the data and renders the pivot table and chart accordingly. It was working fine when the date range defined for the pivot table was static. Now I have made it dynamic since the data range changes each month depending on the number of days it has got. When I run the macro, it runs succesfully, generates the report and save & close the report, but after that I am getting this error. Errorneous
View 2 Replies
View Related
Aug 3, 2007
The code I'm using that results in the following error message: "Run-Time Error '1004':
The pivotTable field name is not valid. To create a pivottable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a pivottable field, you must type a new name for the field." Here's the code, I've seperated it into each sub hoping that will make it easier to read:
Option Explicit
Sub main_prog()
Call td_metrics_import
Call pt_td_metrics("Pivot_Page1", "PivotTable1", "PivotTable2")
Call pt_td_metrics("Pivot_Page2", "PivotTable3", "PivotTable4")
Call create_graph
End Sub...............
View 8 Replies
View Related
Jul 2, 2008
How do you create a macro to copy the information from my weekly reports to a monthly report and be able to update automatically. If you had 4 worksheets (for each week of the month) and 1 mastersheet for the whole month in a workbook. All titles are the same and If you needed to copy all the data that is in the columns, say, A through I, starting with row 4 to however many rows are in a given week. The reports can be made up of numicerial values, text and dates. Let me know if more information is needed or an example worksheet.
View 4 Replies
View Related
Oct 19, 2012
I created a slick little excel sheet with the data coming in automatically via Access query. It has been working fine for months. Now all of the sudden there are a bunch of cells with missing data. The weird part is it's not as if whole columns are missing data, more like 90% missing. When I go to Access and run the query all cells are populated as the should be. There have been no changes to the query at all during this time.
btw...I am running Office 2010
View 2 Replies
View Related
May 31, 2014
A web query I am trying to use to bring an online data table into my worksheet is broken. Now, instead of returning the data table nicely into my worksheet, it imports the code of the webpage, instead, and turns my worksheet into a mess.
The query used to work but there was recently an "upgrade" to the program that populates the web table and the query no longer works and just returns the code for the page, instead. I can see the html code for the table in all of the code it returns but I really need the table to import cleanly into excel.
I can't talk to the people who changed the web program.
View 3 Replies
View Related
Aug 4, 2014
I was messing around with data connections and importing stuff from a network in real time. Cool stuff. But everytime I open ANY excel worksheet on this computer, it tells me "file cannot be found" and refers to one of the first files I used for this. this happens no matter what workbook I am opening.
When I click data --> connections its empty.
View 2 Replies
View Related
Oct 10, 2008
I'm having problems with date parameter for a web connection. I can provide the parameter and type the parameter via message box ... but when I want to reference a cell the query errors out.
View 3 Replies
View Related
Nov 9, 2009
I've created a connection which draws information from an access table to excel. The issue is that any numbers (they are ratings from 1-10) that come from access are shown in excel 1 number higher than the correct data that is in access (i.e 10 is shown as 11, 9 as 10, etc). I've looked all over the forum as well as google and can't find any information on how to correct this.
View 9 Replies
View Related
Mar 29, 2007
I try to extract datas from a pivottable (so one data source) which could change.
The items of one field are changing (given by the user through Inputbox): "batch"
The items of the second field ("type plate") are at each time visible (the macro make them visible to have the complete information concerning the "batch")
So I want to extract values and labels from one particular pivottable (the one I display), selecting several batches. But the problem is that some of the items have no value, so disapear fron the table) even if they are visible. As I extract the value on each item (see code below) I get an error message saying "runtime error 1004 application-defined" when the macro read "valeur".
Dim pt1 As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim pj As PivotItem
Dim i As Double
Dim valeur As String
I tried to go through this problem using "on error goto solution" but it works only one time, and after same error message
How coud I avoid to calculate "valeur" with the Items (pi) not displayed ?
View 9 Replies
View Related
Jan 10, 2012
I have 2 excel files. One excel file (File A) is a basically a source file on a shared network that users input data into. The second excel file (File B) I created in hopes of basically using to analyze the data in File A. I used a data connection to access the data on File A while on File B. The issue I'm facing now is that when I refresh the connection, it locks File A from being opened until I close File B. Currently, I have the data connection to refresh only manually, so I'll open File B, refresh it, save, close, and then re-open without refreshing, so that way File A only stays locked for that short amount of time. Is there any way to make it such that when I refresh the connection, it doesn't lock File A?
View 1 Replies
View Related
Jun 2, 2008
I'm trying to change the source data of an existing PivotTable so that it is linked to a raw data source in another workbook. The workbook it pulls the data from changes every month so I made this flexible code that is meant to pull the raw data every month:
Sub ImportNewSource()
Dim Filt As String
Dim FilterIndex As Integer
Dim Title As String
Dim FilePath As Variant
Dim ThisPivot As PivotTable
Dim FileName As String
Dim ShtNum As Integer
Dim LastRow As Long
The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field.
When I do it manually, it works just fine. There aren't any changes to the PivotTable field or anything like that so I'm stumped. I've tested the range that has the source data and its selecting the proper range and when I do it manually it works fine.
View 9 Replies
View Related
Feb 18, 2009
I have about 10 pivot tables that only have one Pivot Field Item Selected (i.e., Jane Smith) with dates as column fields and a sum for data items. Everytime I refresh the pivot tables and there is a new person in the data tab (which is the data the pivot table is referenced to), the new name populates in the pivot table. I am sure I could include all of the names in one pivot table, but there is a long explanation why I only keep one name per pivot table in this particular workbook.
Attached is an example. As you can see, when you refresh the pivot table, the new name (Jason Smith) which I added to the data tab, will be included in the pivot table.
I am trying to figure out if there is way either with VBA or without VBA to prevent all of the pivot tables in the workbook to not select any new Pivot Field Items when I refresh the pivot tables.
View 9 Replies
View Related
May 30, 2007
I have an Excel workbook that has a data connection to a Sharepoint List. I have 4 columns showing the quaterly status of a project. I want to apply conditional formatting based on the value in the column. I have 5 possible choices so I have to use VBA code to accomplish this because of the limit of 3 option in Excel. I am using this
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Intersect(Target, Range("X:AA"))
If rng Is Nothing Then
Exit Sub
Else
Dim cl As Range
For Each cl In rng
Select Case cl.Text
Case "1"
cl.Interior.ColorIndex = 4
Case "2"
cl.Interior.ColorIndex = 6
Case "3"
cl.Interior.ColorIndex = 3
Case "4"...............................
View 3 Replies
View Related
Jun 18, 2008
I have a worksheet of data collected from on online list of names and addresses all in one column. I'd like to pivot the data so I can sort it, etc. Unfortunately, the entries are not the same length (meaning some have five rows some have six or seven, etc. I have created an only file so everyone can see the issue.
a href=[url]
View 4 Replies
View Related
Jul 24, 2013
If I run this vba it removes the external data connection to the pivot table:
ActiveWorkbook.Connections("EXAMPLE").Delete
When I attempt to drill down into the pivot table it states the data connection was removed.
Is there a way to remove the data connection but preserve the data in the pivot cache?
It's probably possible to store the data somewhere else in the document and link the pivot table up to it but that seems cumbersome.
View 4 Replies
View Related
Apr 14, 2011
if it's possible to break the connection between a pivot table and the data source whilst still maintaining the data in the table? I could try copy/paste special/paste values but thought there migth be a 'proper' way to preserve the data.
View 8 Replies
View Related
Nov 26, 2012
With Excel 2010 a yellow warning bar is shown when opening a file with data connection. I don't want to show this warning to other users who will be opening the file, especially when they won't be able to update anything since the original data is on my computer. The logical way would be to remove or disable the connections before sending the file out. But when removing the data connections through Connection window all the pivot tables are converted to static tables, which is not acceptable. Is there any other solution to this? Maybe use a macro?
PS: I know that these warnings can be turned off though Trust Center, but I am not interested in changing these settings for other users.
View 2 Replies
View Related
Dec 24, 2012
Can I create a PivotTable with two columns of data for the same Column header?
I have created a PivotTable in Excel 2003 with months for rows and cities for columns. I would like to have TWO columns of data for each city. The two data columns are: Average House Selling Price, and Number of Houses Sold. When I put both of these data fields into the PivotTable Wizard, they are listed below each other so that each Month occupies two rows, but each city occupies one column. I want the two data fields beside each other so that each month only occupies one row, but there are two data columns for each City.
View 2 Replies
View Related
May 29, 2013
Im using Excel 2010 and have a data connection that I want to use. I added it to a regular workbook and it updates perfectly as-designed. However, I need this feature to work in a shared workbook, but it doesn't.
View 2 Replies
View Related
Apr 8, 2014
I have a sheet that contains a Microsoft Query (SQL) data connection. In cell C3, I have the month name and I also have a year cell (C4), which contains just the year based on what the current month (=YEAR(TODAY())) is.
For this example let's use May (C3) and 2013 (C4)
C3 and C4 are used as parameters for the SQL query, so if I change the month to January and the Year to 2010, data is refreshed to bring in that data.
Works smashingly.
I then want to add 11 more sheets (12 total), so each sheet is a month. The value in cell C3 on each sheet will be the month name.
If I just copy this sheet 11 times, Excel adds 11 extra data connections; 1 for each sheet. As the query in each sheet needs to be exactly the same, I don't want this. I want the 12 sheets to use the same data connection, only with the specified parameters at the sheet level.
Otherwise, if I need to add or remove a column, or change a column name, rather than just doing it once, I'd have to do it 12 times.
This can't be right, surely, as that would be the most ridiculous design flaw I've ever seen in a piece of software. How can having the same query 12 times be a good thing?
I'm a bit concerned that googling 'excel data connections multiple sheets' always wants to be 'excel multiple data connections one sheet' - I want 'excel multiple sheets one data connection'
View 1 Replies
View Related
Oct 30, 2008
I am simply trying to have some code that would allow me to run relatively simple SQL Queries on Excel Data. This data however will be in a table format within Excel and I will only be needing to query on table at a time; meaning that I don't need relational database features or SQL Joins at all. At most, the SQL Statements will involve WHERE and SORT statements. So, my method is relatively simple:
1. Select an Excel Range (The table being Queried)
2. Provide an SQL Statement
3. Provide an Output range to print the results
After my recent research, I concluded that ODBC/ADODB was the best route. I am very new to ODBC Connections though. Basically, I just set up an ODBC Connection and DataSource using the Control Panel/Administrative Tools. After that, following some templates of others I constructed the following
Sub getData(SQL As String, outRange As Range)
Dim conn As Variant
Dim rs As Variant
Dim cs As String
Dim outCell As Range
Set outCell = outRange.Range("a1")
Dim row As Integer
Dim col As Integer
Set conn = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")
cs = "DRIVER=Microsoft Excel Driver (*.xls);UID = admin;UserCommitSync = Yes;Threads = 3;SafeTransactions = 0;ReadOnly = 1;PageTimeout = 5;MaxScanRows = 8;MaxBufferSize = 2048;FIL=excel 8.0;DriverId = 790;DefaultDir=F:2008Cell PhoneCall Details;DBQ=" & ThisWorkbook.Path & "" & ThisWorkbook.Name...............................
View 9 Replies
View Related
May 29, 2008
I am working pivot tables off of one large excel spreadsheet for my data. I have multiple pivot tables on each of 5 worksheets. The problem is this. Every other week I am sent an updated spreadsheet with the same column names each week which I then copy and paste it into the spreadsheet of the last one. When I refresh the pivot tables, I end up with duplicate field sets. For example, on column in the original data sheet is gender. On the first sheet I went through and renamed the "M"'s and "F"s to be "Male" and "Female" so that it is better looking when printed. Now when i get the new information and copy and paste it to rplace the old, and refresh the pivot tables, I end up with "Male" "Female" AND "M" "F". Now the "Male" and "Female" fields are empty and the "M" and "F" fields have the information. Is this a result of me renaming the fields?
View 5 Replies
View Related
Jul 9, 2014
I have an excel table that connects to an external sharepoint site.
When I refresh (via external data connection), the "start date" and "end date" column formats change. The only way I can correct this is to copy those columns to notepad, and then paste them back into the spreadsheet.
I have several calculated cells based upon these date fields. Once refreshed, the data is incorrect until I copy and paste those dates as stated above.
This removes the functionality of simply "refreshing" the data resulting in a manual process.
View 2 Replies
View Related
Sep 6, 2012
I've about 10 pivot tables in Excel that are populated via an External Query link to Access. In each case I have pre-written views with all the columns I need so that the query can just pick them up. Nine of the ten queries work perfectly, but one doesn't - reporting "Too Few Parameters - Expected 1" when I try and click on the "Return Data to Excel" in the import wizard.
I can see the column headers in Excel to select; so I'm assuming that it can parse the SQL but no results are being returned. Just to confirm though - results are returned (>8000 of them) in Access.
SQL code below:
Code:
SELECT z.pathdurationhours AS PathHours, Count(z.pathdurationhours) AS Paths,
Count(z.pathdurationhours)/(select count(*) from (select [activity date],
[conversion ID], count(*) from tbl_dfa_p2cdata group by [activity date], [conversion ID]) ) AS PCT
FROM (SELECT a.[activity date], a.[conversion ID], val( max(a.[interaction time])-min(a.[interaction time]) )
*24 AS PathDurationHours FROM tbl_dfa_P2CData AS a WHERE a.[interaction time]
"" GROUP BY a.[activity date], a.[conversion ID]) AS z
GROUP BY z.pathdurationhours;
View 2 Replies
View Related
Oct 13, 2013
I'm trying to build a workbook (2007) with multiple worksheets where each worksheet corresponds to a different query/dataset pulling data from the one database.
I would have expected that you could define the one data connection object and then specify the SQL that each worksheet table will invoke. The apparent structure of having the query coupled to the connection object is confounding me. I'm therefore trying to set up a separate connection for each of the worksheets and Excel is getting tied up in knots.
View 2 Replies
View Related
Dec 20, 2012
I open a spreadsheet or workbook that has links to other workbooks I am notified by the message bar that content has been blocked. I click Options > Enable this content > OK. Even if I don't make any changes I'm prompted to save changes when I close the file. If I hit Yes or if I hit No, I will still be prompted to enable content in the message bar the next time I open the same file.
I have changed the External Content settings in the Trust Center to "Enable all data connections (not recommended)" and "Enable automatic update for all workbook links (not recommended)" but I still get the messages.
My Message Bar is set to "Show the message bar in all applications when content is blocked" because I read on Microsoft KB that the other option of "never show information about blocked content" will still block the content, just not tell you about it, which doesn't fix my issue.
The files I'm opening and the files linked to and from them are all on the same shared drive on a file server in the office. Is there a way to tell Office '07 that our file drive is a trusted source by default?
View 2 Replies
View Related
Sep 20, 2013
How to convert "Excel table" to a range without loosing data connection to access?
View 2 Replies
View Related
Feb 10, 2012
[URL]....
I have 4 workbooks, all of them contain data that is managed by other teams at my company. I need to take all of this data, organize, and concatenate it into one sheet for myself, that I will then reference in other workbooks to various OTHER departments in our company that need the data for certain projects. I have the organization laid out in a lot of very complex formulas so that most of this is automated, and not manual.
I need a solution that can pull data from the 4 workbooks without locking them for use, to allow me to keep my document open all the time, and they can update theirs at their leisure. Everything works fine, until I hit the refresh button. Once this is hit, all of the linked documents are locked and cannot be opened, even in "Read Only" mode. Once I close the master document, the files are free to be opened. I know a workaround for now is that i can not "refresh" the document, but rather close and re open it for changes.
Some steps I have already tried :
1. Create an intermediary file that no one uses, and can be locked all day without a problem. This doesn't work for me since the intermediary file needs to be open to refresh, which locks the original source doc, leaving me in the same place as before.
2.Modify the connection string to display "Mode=Read;" instead of "Mode=Share Deny Read"
3.Create a new connection with "Read" only selected in the Advanced Tab
View 1 Replies
View Related
