Search for dates in excel
Hello, I have dates in an excel field of these formats, 01-jan-10, 02/02/05. I formated the cells to 03-mar-2010. I did a find such as 03-mar-2010 does not turn up any result. Any reason to why. Thanks Jean ...
excel-2007
VBA Macro to save an excel file to a different backup location
I am trying to create a Macro that either runs on close or on save to backup the file to a different location. At the moment the Macro I have used is: Private Sub Workbook_BeforeClose(Cancel As Boolean) 'Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 'Saves the current file to a backup folder and the...
Insert table into cell (excel)
I need one cell that will contains sum of all items from another table. Is it possible to place that table into cell so I can expand and collapse table when I needed from that one cell? ...
Exporting and reformatting data out of MS Excel
I have a huge Excel spreadsheet containing telephone calling rates to a number of different countries. The format of the columns is: Country, RateLocality, Prefixes, Rate, Wholesale e.g. Afganistan, Default, 93;930;931;9321;9322;9323;9324;9325;9326;9327;9328;9329;9331;9332;9333;9334;9335;9336;9337;9338;9339;9341;9342;9343;9344;9345;934...
Name Values in Excel Object model
I am using VSTO to create an Excel add-in. My plan is to persist objects inside of excel workbooks by serializing them to strings and assigning those strings to be the values of names in the workbook. However, when I call the API function to add the new name, I get a mysterious exception from the COM library. More precisely, I am call...
How can I create a new spreadsheet file using the OOXML SDK that contains multiple worksheets?
I downloaded the OOXML SDK. I need to create a simple Excel spreadsheet that has x worksheets, each with 4 columns and y rows of data. It's pretty simple, but I can't find an example of how to create sheets anywhere. ...
How can I display the MDX generated by Excel 2007 when using a pivot table?
Pretty much what the title says. Is it possible to show the MDX that is sent to OLAP source when using a pivot table in Excel 2007? ...
C# VSTO: Coloring pivottable cells
HI, I'm trying to make some code color a PivotTable. It works just fine coloring the cells it's supposed to, but if I refresh the table, all the colors disappear, as if the colors haven't been properly attached to the PivotTable. I have the following code (this is a snip from a larger code): myPivotTable.PivotSelect("'" + item["Name"]....
Excel Prorated SUMIF
I have a worksheet with 2 columns, one is a dollar amount, and the other is a day of the month (1 through 31) that the dollar amount is due by (the dollars are income streams). So, I use the following formula to SUM all the income streams due on or before a certain day: =SUMIF(C5:C14, "<="&$B$42,B5:B14) Column C is the due day B42 is...
Excel 2007 and Unicode
I have an israeli spreadsheet reading right to left. When I read the values (using VBA) it places a question mark (?) at the beginning and end of the text, in other words it wraps the text with the question mark (ie ?0123456?). If you type Range("A2").value or .value2 or .text the results are the same. Any idea on how to prevent this? ...
Read Excel 2007 file (.xlsx) in C#
My code was quite sufficient for reading xls file in C# (using Microsoft.Jet.Oledb.4.0) but when I tried to read Office 2007 (xlsx) file, it didn't work. I searched and found that it could be read using Microsoft.ACE.Oledb.12, I tried and it worked. Can anyone tell me the difference between the two ? and why xlsx file can not be read usi...
Loop through worksheets without using VBA
Hi All, I need to total certain cells based on a condition across all worksheets in the workbook. The pseudocode would be Foreach ws as Worksheet 'total =SUMIF (A2:A5, "search term", B2:B5) Next MsgBox (total) But I don't want to use a macro. Any ideas? ...
Excel2k7/VBA:getting the cell address when a form button is clicked in it
Hello All, I've multiple form buttons in multiple cells and when clicked any one of them, i've to perform a function. Now i need to know which cell has this clicked button so that i can take next steps. Thanks in advance ...
How to count no. of Cells in MS Excell depending on their Fill Colors
I want to count the no. of cells in a row or column based of their background colors, e.g. how many are Red background, how many are Blue etc in a range of cells ...
Excel 2007 MedianIfs()
I want to calculate some statistics. In order to calculate the average of certain values of a column, I use AverageIfs(). Now I want to calculate the median for the same values. But there is no MedianIfs() function. Is there a simple solution to calculate the median for values that hold certain conditions (2 conditions)? ...
getting statistics on columns in row format Excel
I have an excel spreadsheet with a lot of columns. each column contains data which i need to summarize. The desired output is to have the summary in this format... Column1 name| sum()| average| count Column2 name| sum()| average| count etc... pivot tables wont work for me, because they base their row labels on the values in the colum...
Excel 2007 - Conditional Currency format
Hi there, Is it possible to set the currency of a column (£ / € / $) dependent on the value of a cell? Eg, If i determine that the address im using in the workbook is America the workbooks currency will be set to dollars. This would save the time of having multiple spreadsheets for different currencies. Thanks in advance to anyone wh...
[change] Vlookup onto another workbook
Hello, here is my dilemma. I have two worksheets one that has the name of clients and one that i want to copy the names to depending on the city. For instance: associated to each column is last name, first name and city. i have hundreds of names associated to different cities and what i would like is from worksheet1.xls to copy all the...
Excel worksheet error
Hi, I am trying to create dynamic excel sheet, with the help of php using XML Spreadsheet. But when I try to open the dynamically created excel sheet I keep on getting error that says 'Unable to load worksheet, problem with worksheet settings'. When I try to look at the log file it had created it shows the below text, XML ERROR in Wor...
Export value from Excel worksheet to Access record
Hi there, I have an excel spreadsheet that contains the primarky-key/id value for a record in a table in an access database. I would like to export specific data from certain cells in the spreadsheet to certain fields in the corresponding record in the table. Is this possible, any help would be greatly appreciated. Many thanks Noe...