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...
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...
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 ...
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 ...
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 ...
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...
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 ...
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. ...
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...
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. 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 ...
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...
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:...
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...
After cleaning a folder full of HTML files with TIDY, how can the tables content be extracted for further processing? ...
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...
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 the last row that contains data in a specific column and on a specific sheet? ...
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...
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...