Compressing Large Pivot Table File
Jun 7, 2013
My pivot table file is currently 12.7 MB and I need to get it much smaller so I can email it and it still be functional with the data.
I have tried right clicking on the pivot table, going to PivotTable Options, clicking on the data tab, unchecking the 'save source data with file' box, and checking the 'refresh data when opening file' box. This shrunk the file perfectly but when i email the file the Pivot Table was unusable because there was no data to back up the function.
The Error messages I got were 'Cannot open PivotTable source file'..... and 'Pivot Table report was saved without the underlying data. Use the Refresh Data command to update the report'. When I click on Refresh Data I still get the same error message.
Any way I can make this File smaller and still functional.
View 4 Replies
ADVERTISEMENT
Feb 6, 2012
I have a table that I am trying to compress to capture the latest instance based on each ID.
For example, let us say this is my data set:
ID___ | Name | Level_ | Score | Date
ID-32 | John | Beginr | 56.00 | 27-Jan
ID-33 | Jill | Beginr | 32.00 | 28-Jan
ID-34 | Bill | Beginr | 78.00 | 28-Jan
ID-34 | Bill | Interm | 39.00 | 29-Jan *
ID-33 | Jill | Beginr | 62.00 | 31-Jan
ID-32 | John | Interm | 61.00 | 02-Feb
ID-33 | Jill | Interm | 67.00 | 03-Feb *
ID-35 | Mike | Beginr | 82.00 | 03-Feb
ID-35 | Mike | Interm | 85.00 | 04-Feb
ID-35 | Mike | Advncd | 89.00 | 05-Feb *
ID-32 | John | Advncd | 24.00 | 05-Feb
ID-32 | John | Advncd | 52.00 | 08-Feb *
From this data set I wish to extract only the 4 entries with a "*" next to them. These entries give me the updated latest status for each student.
So the output table would look like:
ID___ | Name | Level_ | Score | Date
ID-32 | John | Advncd | 52.00 | 08-Feb
ID-33 | Jill | Interm | 67.00 | 03-Feb
ID-34 | Bill | Interm | 39.00 | 29-Jan
ID-35 | Mike | Advncd | 89.00 | 05-Feb
View 7 Replies
View Related
Apr 7, 2007
if there was a way I can put it in to some kind of pivot table, because the hardest part for people when they read his list is it's so large it's hard to find data easily.
So this is how he formatted his data... I was wondering what would be the best way to get this list in to a possible pivot table. This is a consilidated example, there are plenty more columns, but this will give you an idea of my problem.
A2007 A2006 A2006 B2006 B2006 B2007
Feb Jan Feb Jan Feb Jan
630 Labor Cost 1000 7500 3000 4500 800 5000
624 Equipment Cost 900 50 40 300 20 1400
Now, the only thing I can think of is to make columns, but then I'd have to recopy all the task names (which there is about 700) for each of the different years (A= Actual, B= Budget, F= Forecast). Is there any other way that you can think of to do this with out making it so complicated? Any help or suggestions would be great. I really want some format that allows you to click the total and it goes to what makes up that total.
View 7 Replies
View Related
Jan 4, 2010
I'm trying to write a macro that will create a pivot table, and am getting an Error code 1004: Cannot Open Pivot Table Source File "Sheetname". My code is below. I've tried to note what each section does, and it all seems to work well except for the Pivot Table creation.
View 14 Replies
View Related
Oct 15, 2012
I'm trying to count how many production orders i have per week. However, there are duplicated production orders per week. I only want to count how many unique orders there are for each week. I only see the ability to "Count", which counts my duplicates as well so it over inflates my true quantity.
View 3 Replies
View Related
Aug 9, 2012
I want to reference a table (not a pivot table) from another Excel file. I want it to update automatically if the original table is changed in any way.
View 3 Replies
View Related
Aug 6, 2003
I've got a 80 Mb CSV file and would like to open and work with it. Too many lines (90000 or so).
Is there a way to split ( ) this file so I can open two files instead?
View 9 Replies
View Related
Oct 12, 2006
What is it with Pivot Tables and their gigantic file size increases. I have a file. Which has a table of data behind it of approximately 5000-6000 rows and ~ 100 columns of data. If I save the file without the Pivot Table it's filesize is Mb. If I save it with the Table and Chart its filesize increases to over 10 Mb. I am experimenting with dynamically creating the pivot table so that I can conserve storage space but if someone has some idea on why pivot tables consume so much space
View 7 Replies
View Related
Apr 5, 2012
I have the following code that will open each file in a folder and run code on it. I'm trying to write code that will create a pivot table on each of the opned files...but cannot get past opening the file...
So I had built the vba code into a specific workbook so I could just drop that workbook into any folder I wanted to run code on files to and depending on the name of the file it would run different type of code. The problem has something to do with using "activesheet" for the pivot table wizard. It just doesn't like it and gives me a application 1004 error.
any way I can modify the activesheet so it will make a pivot of the newly opened file? I think what it is trying to do is make a pivot of the blank workbook that the code resides in
Sub filelooper()
MyDir = ActiveWorkbook.Path ' current path
Dim MyFile As String
[Code].....
View 1 Replies
View Related
May 15, 2007
I want Pivot Table Drill-down result in New excel file. when I have a pivot table, When I double click any data cell, the result comes in the new sheet(Drill-Down) of the same file BUT I want the same result to come in a New Book (a new excel sheet) and not in the same file.
View 3 Replies
View Related
Jun 12, 2007
I have a workbook with a number of pivot tables as well as the source data for each pivot table. Some of the source data sheets needed to be deleted so now if I try to refresh the corresponding pivot table I get "Connot open pivot table source file '[Std Mthly Rpt.xls]Report_2'". I recreated the source file again and would like to know if I can link the pivot table to this new source file or do I need to create a new pivot table??
View 7 Replies
View Related
Feb 21, 2012
I am sourcing data for my pivot table from an csv file. The ultimate task I am trying to accomplish is to allow the user upon hitting a button to select the file that would feed the pivot table. The code below is what I got by linking the initial source file to the pivot table. As I can see the source file named "4 Port.csv" is listed in the SQL-like statement. How do I go about allowing the user to pick the file from the "open file" dialog?
Code:
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = Array(Array( _
"ODBC;DefaultDir=C:Documents and Settings2301Desktop;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;FIL=text;MaxBufferS" _
[Code].....
View 9 Replies
View Related
Mar 18, 2014
,i have file with 100 mb size.
a) i need to open the workbook and
b)Refresh all pivot tables in all sheet.
c)Also there are many formulas in the workbook that needs to be updated as well.
if i do it manualy i takes more time.......for opening and refreshing and i could see status bar running for excel calculation for updating all forumulas.
View 1 Replies
View Related
Nov 14, 2012
I am trying to adapt a macro to run on data that is consistently in the same format but the data does change, the macro needs to run over and over on new excel workbooks, dynamic range ? instead of a set source ?
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"weekly71012!R1C1:R9379C30", Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable1" _
, DefaultVersion:=xlPivotTableVersion14
I am sure this is the problem as i initially created the macro using weekly71012 excel file however i just want it to run on every file i pull it off?
View 2 Replies
View Related
Mar 19, 2013
On a worksheet, I created:
- a list of data
- a pivottable based on these data
When moving this worksheet this worksheet to another workbook, the pivot table can't refresh anymore. This throws an error message "Reference is not valid". To work around this problem I need to adapt the datasource. The same occurs if the list and the pivot table are on separate sheet, with the added strange behaviour that, when data an PT are split, it is not possible to move both sheet together.
This would not be a big issue if my problem had to be solved manually. The real problem is that I need to move the sheets from a C# program.
View 3 Replies
View Related
Mar 14, 2013
I have a worksheet with two pivot tables, one of which is visible to the user. Ideally, the user should be able to change the "Row Label" field settings of the visible pivot table and then press an "update button" that then adds the same field to the second pivot table.
Ideally, the ordering of the fields should also be made similar between the two tables, though this is of less priority.
I imagine it would be something in the style of:
"If number of Pivot1 active row label fields = X then
Pivot 2.AddRowLabelField = Pivot1.RowLabelField(X)
end if"
View 1 Replies
View Related
Jan 22, 2007
attached is a spreadsheet 6 people in my area use daily(ive copied and pasted the sheet in question to a new worksheet, as the file was too big). Ive been trying for about 3 days now to make a pivot table to summarise this data.
View 6 Replies
View Related
Jul 3, 2009
What i would like to know is, In the code im using to make a phone call from a userform the varibles are set in each and every sub. It all works well though could they be compressed into only being used once like an option variable or somethink like that.
i know that more that one variable can be decleared at the same time of the same type "dim x, Y, z as long.
View 7 Replies
View Related
Apr 29, 2006
I have data that develops 3 to 4 pivot table each day. I would like to know if there is a way to change the date on one of the pivot table and have the other pivot tables date change to match with the first pivot table. At this time I am going to all 3 or 4 pivot table to select the correct date. The date is in the page position of the pivot table. I have attached a small sample of the data and the pivot tables.
View 2 Replies
View Related
Mar 16, 2013
my colleague did something to one of our files and it is now 845918 kb. I transferred it to a flash drive and then to my hard drive but now I cant open it to see what is wrong. Is there a trick to opening this up? When it was opened on her computer it wouldn't let me add any tabs or move tabs. I think I unprotected it but that didn't change anything.
View 1 Replies
View Related
Oct 30, 2002
I have a database of 75000 rows in text format which has to be converted to excel, but the limitation in excel is only 65536. hence, I want to a macro that will copy the 1st 65536 rows into the 1st sheet in excel. The remaining rows should be copied into the second sheet. I know that it can be done with access. but i dont want to use access.
I have got a code to do this, but it takes much too long. if the code could be modified to make it faster,
Sub LargeFileImport()
'Dimension Variables
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
'Ask User for File's Name
FileName = InputBox("Please enter the Text File's name, e.g.
View 9 Replies
View Related
Feb 4, 2009
I have a 14.3 MB xlsb file that I am trying to open with no success as it just hangs. Anyone have any ideas on how to get the data out of there? I would like to be able to get the data to a pivot and copy the pivot to another file to try and reduce the load on one file. I tried to import to Access but was told I was using a file version before 2007, which is impossible since it is a xlsb file.
View 9 Replies
View Related
Feb 24, 2013
I've been having tremendous issues with my Excel file, mostly with it crashing or not responding. Would this be because my file has so much data in it? If so, is there anyway to make Excel process all the data without crashing so much?
View 6 Replies
View Related
Aug 1, 2008
I have a SUPER large CSV file that I cannot open in 2002 excel
Is there a way to write in VBA to import the csv but only include rows that contain "TESTIMPORT" in a cell?
View 9 Replies
View Related
May 4, 2009
I have a file that became too big due to phantom bloat, unused range saved by Excel and all that kind of reasons. Thank to previous posts on that board tackling that issue, I was able to find how to proceed to reduce the file back to its normal size.
But I did so in a beta file (test file). The real file has become so big (103MB!) that Excel cannot even open it anymore! The file contains archive info that we do not have anywhere else.
Is there anyway then to open the file or to reduce its size without opening it (through magics...)? I just honestly don't know how to retrieve that info before deleting that file.
View 9 Replies
View Related
Aug 25, 2009
I have a large text file, 75mb, that I would like to import into Excel but it goes well beyond the 65536 lines available.
Is there a process to get these files into excel? File splitter?
View 9 Replies
View Related
Jan 21, 2010
I want to use the attached Test.xls file below and do a lookup from the Data.csv (see zip file) where "Empl ID" (column A in Test.xls) matches "ID" (column A in Data.csv). I want to bring back "SupvID" (column B in Data.csv) into column D in Test.xls.
The problem I have is that in real life Data.csv is a very large file and contains more than Excel's maximum number of rows. I want to automate this using VBA to lookup the values without opening the csv file.
I have looked at a number of forums for answers and I can see where using an ADO connection will probably help accomplish this, but I can't seem to get it to work on these specific files.
View 4 Replies
View Related
Jul 20, 2006
I have a file that is 40,000K...is there anyway to decrease this file size without having to delete data or break up the spreadsheet?
I couldn't think of anything but wanted to double check.
View 3 Replies
View Related
Sep 20, 2006
I have a large text file (greater than 64k lines) which I need to import into excel. I only need anything imported from the text file that is for dates I specify. So I would like to run a line by line query and only import the lines I need.
The dates are always in position 43-50 (dd-mm-yy) in the text file itself, so the code would have to look at that section, see if the date is within the range specified and only import if it is (and then move onto the next line).
View 4 Replies
View Related
Jan 9, 2008
I have some spreadsheets one worksheet little data and it saved as it a 5MB file when it should only save as something like 500Kb, what would cause this?
View 3 Replies
View Related
