excel

How do I create a status dialog box in Excel

I have created a database report generator in Excel. I am trying to create a dialog box that displays status information as the program runs. When I generate the report, although the dialog box appears, I cannot refresh/update the information it displays. Most of the time, the dialog box only partially appears. I have tried using the .r...

Options for PivotTables in Excel

I need to design a small project for generating excel reports in .NET, which will be sent to users to use. The excel reports will contain PivotTables. I don't have much experience with them, but I can think of three implementation alternatives: Set a query for it, populate it, send it disconnected. This way the user will be able to g...

Hiding data points in Excel line charts

It is obviously possible to hide individual data points in an Excel line chart. Select a data point. Right click -> Format Data Point... Select Patterns Tab Set Line to None How do you accomplish the same thing in VBA? Intuition tells me there should be a property on the Point object (Chart.SeriesCollection().Points()) which deals ...

Exposing .net methods as Excel functions?

I have a set of calculation methods sitting in a .Net DLL. I would like to make those methods available to Excel (2003+) users so they can use them in their spreadsheets. For example, my .net method: public double CalculateSomethingReallyComplex(double a, double b) {...} I would like enable them to call this method just by typing a ...

Top ten ordering in Excel based on complex team rules

I have an excel spreadsheet in a format similar to the following... | NAME | CLUB | STATUS | SCORE | | Fred | a | Gent | 145 | | Bert | a | Gent | 150 | | Harry | a | Gent | 195 | | Jim | a | Gent | 150 | | Clare | a | Lady | 99 | | Simon | a | Junior | 130 | | John | b | Junior | 130 ...

Writing into excel file with OLEDB

Does anyone know how to write to an excel file (.xls) via OLEDB in C#? I'm doing the following: OleDbCommand dbCmd = new OleDbCommand("CREATE TABLE [test$] (...)", connection); dbCmd.CommandTimeout = mTimeout; results = dbCmd.ExecuteNonQuery(); But I get an OleDbException thrown with message: "Cannot modify the design of...

Excel like server side control for ASP.NET

We have a requirement to increase the functionality of a grid we are using to edit on our webapp, and our manager keeps citing Excel as the perfect example for a data grid :/ He still doesn't really get that a Spreadsheet like control doesn't exist out of the box, but I thought I'd do a bit of searching nonetheless. I've found a couple ...

How do I save each sheet in an Excel workbook to separate CSV files with a macro?

Title says it all. I have an excel with multiple sheets. And I was looking for a macro that will save each sheet to a separate CSV (comma separated file). Excel will not allow you to save all sheets to different CSV files. ...

Is it possible to write ActiveX Controls in C# that will run in Excel?

I have been searching on the web for some example code on how we can write a custom ActiveX Control for use in Excel using .NET but so far I have found old articles suggesting that it is not supported. The application we are building uses Excel as a report writer so we which to add some custom controls to the worksheets to provide a ri...

Tool for deciphering Spreadsheets?

I recently had a multipage Excel spreadsheet/workbook dumped in my lap. Are there any tools to aid in deciphering spreadsheets? At the moment I click in a little cell to see what's there, then I click in some other little cell that it references, then I click in 3 other cells that it uses and fairly quickly I'm lost in a maze of twisty l...

How can you cascade filter the attributes of more dimensions in a SSAS cube, viewed in Excel 2007

How can you cascade filter the attributes of more dimensions in a SSAS cube, viewed in Excel 2007. For example, if we have a cube Sales with the dimension Time and dimension Client, once the dimension Time is filtered to show only the sales from a particular date, if "Client.ClientName" is chosen as a filter in the filter area, how can ...

Function Overloading in Excel VBA

I'm using Excel VBA to a write a UDF. I would like to overload my own UDF with a couple of different versions so that different arguments will call different functions. As VBA doesn't seem to support this, could anyone suggest a good, non-messy way of achieving the same goal? Should I be using Optional arguments or is there a better wa...

How do you place a textbox object over a specific Cell when automating Excel?

We are automating Excel using VB.Net, and trying to place multiple lines of text on an Excel worksheet that we can set to not print. Between these we would have printable reports. We can do this if we add textbox objects, and set the print object setting to false. (If you have another way, please direct me) The code to add a textbox is:...

How do you prevent printing dialog when using Excel PrintOut method

When I use the PrintOut method to print a Worksheet object to a printer, the "Printing" dialog (showing filename, destination printer, pages printed and a Cancel button) is displayed even though I have set DisplayAlerts = False. The code below works in an Excel macro but the same thing happens if I use this code in a VB or VB.Net applic...

What's the best way to extract table content from a group of HTML files?

After cleaning a folder full of HTML files with TIDY, how can the tables content be extracted for further processing? ...

Setting Excel Number Format via xlcFormatNumber in an xll

I'm trying to set the number format of a cell but the call to xlcFormatNumber fails leaving the cell number format as "General". I can successfully set the value of the cell using xlSet. XLOPER xRet; XLOPER xRef; //try to set the format of cell A1 xRef.xltype = xltypeSRef; xRef.val.sref.count = 1; xRef.val.sref.ref.rwFirst = 0; xRef.v...

How can I refresh all the pivot tables in my excel workbook with a macro?

I have a workbook with 20 different pivot tables. Is there any easy way to find all the pivot tables and refresh them in VB? ...

How can I find last row that contains data in the excel sheet with a macro?

How can I find the last row that contains data in a specific column and on a specific sheet? ...

What is the best way to process all versions of MS Excel spreadsheets with php on a non-Windows machine.

I am importing data from MS Excel spreadsheets into a php/mySQL application. Several different parties are supplying the spreadsheets and they are in formats ranging from Excel 4.0 to Excel 2007. The trouble is finding a technique to read ALL versions. More info: I am currently using php-ExcelReader. A script in a language other th...

How to iterate through all the cells in Excel VBA or VSTO 2005

I need to simply go through all the cells in a Excel Spreadsheet and check the values in the cells. The cells may contain text, numbers or be blank. I am not very familiar / comfortable working with the concept of 'Range'. Therefore, any sample codes would be greatly appreciated. (I did try to google it, but the code snippets I found...