I am using VSTO with Excel 2007 to generate PivotTables and PivotCharts dynamically. I am having a problem when I need to have a PivotField in more than one column. To accomplish this I create a PivotTable in Excel and serialize its properties into an XML document, which I then use to rebuild the PivotTable. Ie: as a Value and as a Co...
I have monthly sales figures stored in separate sheets. I would like to create a plot of sales for multiple products per month. Each product would be represented in a different colored line on the same chart with each month running along the x axis. What is the best way to create a single line chart that pulls from the same relative c...
How do you convert a numerical number to an Excel column name in C# without using automation getting the value directly from Excel. Excel 2007 has a possible range of 1 to 16384, which is the number of columns that it supports. The resulting values should be in the form of excel column names, e.g. A, AA, AAA etc. ...
I've been asked to find a way to send an alert to a blackberry when certain conditions are met on an Excel 2007 spreadsheet. The alert can be an SMS (preferred) or an email. The cell values are changing throughout the day from a DDE feed. What are the typical solutions that people use to solve this? ...
SQL Server 2000 doesn't seem to support the xml based .xlsx file types. Besides converting the file to a 97-2003 file type, how else can I achieve this? ...
I have a requirement to export the results of a SQL query to excel. I am currently exporting it into 2007 format, but everything I have found will only run in a x86 enviroment. The web site where the export is to take place is running on a x64 version of IIS. Changing IIS to run x86 is not an option. My current solution is to save the e...
Dear all, can you please tell me which add-in you are using to generate real/integer values in Excel 2007 (running on Vista SP1)? I have tried a couple of them and particularly Random Generator from AbleBits.com but there are some problems with entering lowest and highest values there. I guess this is due to Office 2007 & Vista configu...
I had an application for returning closest matches to certain values in a large cluster of values( as in my earlier question) and I chose a VBA solution. While in the course of using the said application, I observed that the results for the value of 0.5 were not correct. I had been using the VBA Round funtion which I found to be retur...
When I try to create a Excel 2007 Workbook project, in Visual Studio 2008, I get the following errormessage: Project cannot be created because the "Excel Visual Studio Design-Time Adaptor Add-in" is not working correctly. Excel might have disabled the add-in or made it inactive, or all add-ins might be disabled in your Trust Center s...
I'm using a series of ToolStrips in an Excel 2007 Actions Pane. I'm programatically adding buttons to each ToolStrip and then adding each ToolStrip to the actions pane. So far so good; my ToolStrips appear, with their associated ToolStripButtons. The problem comes with displaying the entire contents of the ToolStrip. I have ToolStrip....
The following VBA code works great in Excel 2003, but results in a "stack overflow error" in Excel 2007. The code is required to either unlock or lock certain cells based on a drop-down menu selection. I need to be able to run the code in both Excel 2003 and 2007. Please help. Private Sub Worksheet_Change(ByVal Target As Range) If [E2...
Excel 2007 I have a row of cells with variation of numbers and letters (which all mean something.. not random.) It's basically a timesheet. If they take a sick day they put in S, if they take a partial sick day they put in PS. The problem is they also put in the hours they did work too. They put it in this format: (number)/PS. No...
I have a table "A" with 2 columns "Foo" and "Bar". I have a formula with the structured reference A[Foo]. When I fill this formula horizontally I want the reference to stay A[Foo] but now, in the second column, the reference turns to A[Bar]. Is there a way to make this structured reference absolute? It'd be shocking that this isn't sup...
I have a table with 3 columns. I want to write a formula that, given a structured reference, returns the index of the column. This will help me write VLookup formulas using the structured reference. So, for example, for the table MyTable with columns A, B, C I'd like to be able to write: =GetIndex(MyTable[C]) and have it return 3. Righ...
Every time I create a new excel sheet, I have to go in and change it's cell reference mode to the familiar A1, B1, etc. I can't seem to find a way to permanently set it to A1 style. Is there a macro I can write or a way to use templates or something, so that I don't have to keep changing the R1C1 setting? ...
Hi Fellows, I found the Excel 2007 Binary format (with extension .xlsb) perfectly suitable for my needs, since it's fast to load and very compact. I deliver a bunch of reports in Excel that carries a lot of data, and those reports are actually being loaded with a IS package. So I assumed the conversion to this very format, read docu...
I'm trying to write a custom function that will let me retrieve a cell from the first row in a range that meets x number of criteria. I imagine this would be very similar to the way SUMIFS works, just simpler in that it doesn't continue processing after the first match. Does anyone know code to reproduce the SUMIFS (excel 07) function ...
I made a test file, and ran through the excel file just fine, but whe nI insert one with a lot of data, I get the following error: The Microsoft Office Access database engine could not find the object 'Sheet1$' Which seems incredibly obvious, but 'Sheet1' is surely there, just like in my test file - I have checked about 30 times. The...
Hi. I'm trying to do conditional formatting so that the cell color will change if the value is different from the value in the cell left of it (each column is a month, in each row are the expenses on certain object. I want to monitor easily changes in prices over months.) I can do it per cell and format-drag it, but I would like a gene...
I have an automation add-in written in c# that exposes some functions to be used as formulas in Excel. The formulas can be successfully used from an Excel sheet but they do not appear in Excel2007's formula autocomplete lists. i.e. If I want to use the function AddNums(x,y) within an excel sheet then I must know what the function is c...