I can still do a pivot report against the 'saved' data. So it must be out there in a file...? I read the function GETPIVOTDATA. Based on the description, It sounds like it would work to rebuild the source data. However the examples given don't see to match up with that. I have tried playing with the command, but I really don't know what I am doing and have had no luck
Does anyone know of a way to retrieve that stored data that the pivot reports are reporting against?
I have a vba macro written in an excel worksheet (WS1) that reads another excel file (WS2) and retrieves data and writes it into the worksheet with the macro (WS1). It works beauifully except for one little problem. When I send it to a co-worker the numbers part of the data returns null values. I am connecting to (WS2) using ADO connection strings for the spreadsheet and recordset. The only difference I see is that in references of WS1, it shows Microsoft ActiveX Data Objects 2.7 library, it shows 2.8 library on my machine. Also it shows recordset 2.8 library on my coworkers machine, as well as mine.
I have a report that was created for 2005 that contains two worksheets: a "source data" worksheet and a " pivot table" worksheet. I cleared out the 2005 data in the "source data" worksheet and replaced it with 2006 data...after this I refreshed the Pivot Table and everything seemed fine. When looking at the file size I noticed that it was almost twice its original size....upon further investigation I found that the Pivot Table was internally holding onto the old source data (the "Show" functionality of the rows/columns in the table lists the 2005 row/column headers as well as the 2006 headers....even though no data from 2005 is shown in the Pivot Table).
Does anyone know how to purge the old data from the internal Pivot Table memory?
I hope this is enough information....let me know if you need more.
We have an excel template with numerous VBA and Macros embedded within it, saved on a central drive. I am able to access this file ok, however, when I save it when it is reopened by either myself, or a colleague, we get the "File error: data may have been lost" message. Everyone else within my organisation can open the template, edit, and save and subsequently reopen the spreadsheet without any problems at all.
One thing I have noticed, which to me seems strange is that the files I save that have the "error" message are slightly smaller in size than the ones my colleagues save (350kb vs 417kb)
My IT department have tried unintalling Excel and reinstalling, and also rebuilding my profile, all to no avail. A trainer has sat with me to check I am not using the spreadsheet incorrectly (or differently to anyone else, and this is not the case).
I know, I know... should've backed it up... there exists no other copy of the file in question newer than it's initial creation two weeks ago (been working on it solidly since).
The data does appear to be there but, rather mixed up... does anyone know of any methods/tools/software that might be able to salvage as much of the data (needs to be in it's original structure as it's a cross match excersice) as humanly possible?
I have created some Excel files using Excel 2000 on Windows Server 2003. The Excel version is given as 9.0.6926 SP-3. When I open them using Excel 2000 in Windows 2000 after a few sheet deletions I get the error message "File Error: Data May Have Been Lost" when opening the files. The Excel version is given as 9.0.8948 SP-3 in Windows 2000 (why is it different to the version number in Server 2003? Could this be part of the problem?) It crashes on this line
If Sheets(3). Name <> "template" Then
with the error "Run time error 32809 Application defined or object defined error".
The template sheet is very hidden. When I try and unhide it I get the 32809 error again.
I have a column line chart to which I add data monthly and then have to manually update the "source data" to reflect the added data on chart. This is a rolling graph, which mean that I have to remove data for one month(from last year) and then include the new month's data. Is there any way on automating this process...like a macro or something, so once I add the data excel automatically removes one month of old data and make changes to include fresh data. Eg Currently chart is based on data from A2:F2 and I add new data to cell G2. I need something which automatically update the source data to cell B2:G2.
I'm working with a list of different manufacturer part numbers, that must be represented in TEXT format only! The file with manufacturer part numbers exported from ERP system to TXT/CSV formats. When I open it in Excel , manually in "Text Import Wizard" I define Column Data Format as Text.
Examples: 1. part number 3214-4-5 turns to 05/04/3214 2. part number 0005487 turns to 5487 3. part number 223878615654 turns to 2.23879E+11 4. part number 4303.240600 turns to 4303.2406
Is anyway to do it automatically from VBA? I try to record macro , but it does not contain "text format".
I have a simple reporting sheet where the data for orders place is in one sheet and on the other sheet is an imput box for 'date' and it filters through and presents a table of data and two graphs for the date chosen.
I give a daily report (contents of this sheet) but I also need to leave this sheet available for anyone to open and change to another date.
I want to copy the repor sheet into a new one and email but I want the graphs to remain, not go blank when anyone changes the original sheet.
I have a bar chart that was made from table 1 which has the value in it. I have another table, table 2 that has the name corresponding to the value in table 1. I want to color data points in a bar chart with a different color for each name I have in table 2. I also want to give a data label in the data points with the value I have in table 2 instead of the original data label from table 1. Is it possible to do it?
I have one source spreadsheet, where are columns NAME, DATE. I read these data by ADO to other spreadsheet, where I can change/delete data and then run macro for update data in source spreadsheet. The problem: In source spreadsheet is column "NAME" and column "DATE", with values e.g. "Joseph"; 1.1.1980. I read this data to other spreadsheet, then I delete in it value 1.1.1980. When I run macro Update, it messages error.
Sub UpdateItem ... .Fields.Item(1).value = activecell 'activecell value = "Joseph" If Not isempty(activecell.offset(0,1)) Then .Fields.Item(2).value = activecell.offset(0,1).value Else .Fields.Item(2).value = "" 'I tried Empty and 0 too but when I read data again then, it displays 0.1.1900, nothing works End If ... End Sub
It seems that in source spreadsheet has data in column "Date" format Date and when I try to update data in format String ("") in source spreadsheet by Update macro, it messages error. When I used
.Fields.Item(2).Value = Empty ' or .Fields.Item(2).Value = 0
after rereading data it displays 0.1.1900 What I want to get is that if the cell with date (in other spreadsheet) is empty, the cell in column Date in source spreadsheet after updating will be blank (contains no values).
Is there a way to use Data Validation that will pull data from a source list and also be able to type in additional data or just new data in same cell?
I used to get data from external excel, access files and today tried to put together query to get data from CSV file. While using same source file, I have creted several queries to get data out based on criteria. For some strange reason, same structure queries that did run with excel or access as data source, does not want to work the same way with CSV files.
Here is example of non working query SQL: SELECT Vendors.Vendor, Vendors.Name, Vendors.`Name 2` FROM `Q:FinanceRapporterPSAPRaw`Vendors.csv Vendors WHERE (Vendors.Name Like '%' & ? & '%')
If I enter "London" as a criteria input, it will not work. However, if I enter "ondon" it will find me all values records whre name contains London... Seems like I need to skip first symbol when entering criteria. This is, of course, not something I would like to do.
I have a combobox in which I need data loaded from a column of an excel sheet.
As of now, I am using the below Last = Sheet9.Cells(Rows.Count, 1).End(xlUp).Row With ComboBox1 For Row = 2 To Last .AddItem Sheet9.Cells(Row, 1) Next Row End With Is there any other way to load data?
I have tried putting in the Row Source in the Properties of ListBox1, (A10:A13) but the range is in another worksheet called Codes. So when I run the Form the list box shows the range A10:A13 in the current sheet. I have named the data list SP but when I punch that in it will not accept it in the Row Source. How can I lock in the source data in the list box so it only shows the named range SP.
I want to design a spreadsheet report, but instead of importing the data from an external source, i would like the data to come from another sheet within the same workbook. The goal is that the data sheet will contain only raw data with no formatting, and the report sheet will contain all the formatting.
If there a way to change the souce data so that it changes based on a variable? For example, if a cell's value equals X then use one range and if it is Y then use a different. As far as I can tell you cannot use the "IF" argument in a chart Source field.
One option I came up with is to change the actual data on a worksheet and always have the source data point to that range in the worksheet. The problem is that the source data is not always the same length (Monthly may be 100 columns long while Daily is 1000 columns long - the chart looks starnge).
I try to find a solution (with or without macro) to assign different ranges named "Failure_C..." in a data validation source depending on the value of another data validation list (32 values called)
I started in the Data Validation List Source to fill in IF function but 32 inputs are not accepted and too much caracters in the source.
I was wondering if anyone knows how to change the souce data for a Pivot Table? Eg if I have the Pivot table looking at Columns A & B lines 1 - 20 and I want the Pivot to also include Column C and lines 21 - 50, how would I do this? for a chart I can just right click and select the "Source Data" option but it seems that this is not available for Pivot Tables.
Everything else in my macro is using R1C1 format for ease of automation. However, I cannot get this to resolve. I am setting the source data for a line chart.
However, if I use A1 format it works just fine.
I am using R1C1 for automation purposes. A1 format will not easily work. I have the interpolated statement in there because the range contains some empty cells. I want excel to fill the line in the chart using interpolation. All the data in the columns(N through R or 14 through 18 in R1C1 format) is contiguous. I have not tried Union, but it seems I should just use this range like I do in the A1 format.
I have used a userform found on this forum (I cannot find the link) that displays row data from a range within a listbox. Unfortunately though you cannot determine the column width. As such I wondered if it was possible to show the data within other listboxes. So for example in listbox1 you can see 5 columns accross (from a range) however one of them is very wide.
Looking at Listbox1 you can only see the firsy 20 characters approximately in each column. Is it possible therefore that once this row of data is selected that the contents of the columns is duplicated in other listboxes on the same userform that can be expanded to accommodate larger text?
I'm trying to create a workbook for tracking costs and change orders for multiple subcontractors. My main question is about combo boxes. I would like to create a master list of subcontractors on The masterlists tab of the attached workbook. I would like to use this masterlist as the data source for a combo box I'm going to use to replace the subcontractor txtbox on the SubCoEntryFrm. I figured getting away from a user typing something in would benefit me if I wanted to use that subcontractor name field to analyze data.
I am having trouble with a number of workbooks when using vlookup (using the wizard rather than entering the formula directly)
First I select the cell with lookup value, then try to select the table array on the source sheet but the cursor changes to the hand symbol and I can't click on any cells. I have had this problem with many different workbooks. The only solution I have found is to copy the whole sheet, paste-special-values into a new sheet and use that as source.
