What I'd like to do is produce an HTML/CSS/JS version of the following. The gridlines and other aspects are not important. It's more of a question how to do the background databars. ...
Joel often talks about using MS Excel for lightweight project management, but I'm curious about actual implementations of this idea. I've seen some templates that seem to clone MS Project via macros, which would be overkill for a lightweight project. Anyone have any useful templates? ...
Not sure how many Excel VBA gurus, besides myself ;-D, that hang around stackoverflow but here's an interesting question. Goal: Efficiently show/hide rows based on the data in the row. Create a helper column that determines whether or not a row should be hidden. Have the formula in the helper column return an error or a number. Hide ...
I have a chart with a series that denotes a large set (1000's) of discrete measurements. Some of these are bad measurements and I want to colour the line for the series based on another set of data that describes how accurate the measurements are. Bad measurements should be red and good measurements green and the in between on some kind ...
I have an Excel macro that deletes a sheet, copies another sheet and renames it to the same name of the deleted sheet. This works fine when run from Excel, but when I run it by calling the macro from Python I get the following error message: ' Run-time error '1004': Cannot rename a sheet to the same name as another sheet, a referenced o...
Is there anything we can do either in code (ASP/JavaScript) or in Excel so that the comma separated values end up in separate columns in Excel? ...
Hi, I have some content that i have to render as a excel file in browser. I was able to render a grid content to excel with the below code. Response.Clear(); Response.AddHeader("content-disposition", "attachment;filename=FileName.xls"); Response.Charset = ""; Response.ContentType = "application/vnd.xls"; System.IO.StringWrite...
Here's the problem: I have to modify an existing Excel spreadsheet using .NET. The spreadsheet is hugely complex, and I just have to add data in some predefined areas. I'm investigating my options, and Excel Automation/InterOp is out of the question, as I'm implementing an ASP.NET website, and Excel probably isn't installed on the ser...
When I create a graph after using range.copy and range.paste it leaves the paste range selected, and then when I create a graph a few lines later, it uses the selection as the first series in the plot. I can delete the series, but is there a more elegant way to do this? I tried Set selection = nothing but it won't let me set select...
I have some special cells in my Excel workbooks which are managed by my Excel Add-in. I want to prevent users from changing content of those cells, but I also want to know, what value users wanted to enter to those cells. On the SheetChange event I can check what users entered to my special cells, but how do I determine the PREVIOUS valu...
Hi, I have SSRS Reports created and rendering in Excel format. I used a custom color(mentioned in excel spec R23,G123,B87) in SSRS. But it shows a different color with the same combination in SSRS. And once I render the report to Excel it shows the color different though I used the same color combination. Why is the color difference i...
How can I create a table that has its first row and first column both locked, as in Excel, when you activate 'freeze panes'? I need the table to both scroll horizontally and vertically (a lot of solutions for this exist, but only allow vertical scrolling). So, when you scroll down in the table, the first row will stay put, since it wil...
In Excel VBA (2003), I've noticed that any Public or Friend Sub method in either a module or ThisWorkbook that doesn't have any arguments will show up as a Macro that can be run by the user. I.e. when the user goes to Tools --> Macro --> Macros... (or Alt+F8) the method will show up in the list that can be run. For sanity's sake (organ...
Given a column index, how can you get an Excel column name? The problem is tricker than it sounds because the columns don't wrap over like normal digits would. Even the Microsoft Support Example doesn't scale beyond ZZZ. Disclaimer: This is some code I had done a while back, and it came across my desktop again today. I thought it w...
Hi I am trying to do some maths with my VBA excel (prime factorization) and I am hitting the limit of the long data type (runtime error 6 Overflow). Is there any way to get around this and still stay within VBA? (I am aware that the obvious one would be to use another more appropriate programming language) Thanks for help in advance! ...
Hi All, I have data in excel sheet that has two columns and 10 rows. Some of the rows for First column has blank value. Excel shows it as (blank) on putting aut filter on colunm I am querying the excel sheet from C# 2.0 to find out if first column contains a blank. select * from [Sheet1$] where [Myname] = \"\""; The above query is ...
If I put a DateTime value into an Excel cell using Range.set_value through .Net COM Interop, and then retrieve the value of that same cell using Range.get_value, the Millisecond part of the value is not returned, though everything else is correct. Is this a bug? What is the workaround? I'm guessing that using the Value2 property instea...
Hi, I’m writing pl/sql procedure that exports data from Oracle to Excel. I need data formatting so I can’t use CSV. I’ve already tried with XML but it generates too large files when I want to export e.g. 70000 rows with 50 columns (almost 300 MB!!!). That’s why I decided to use HTML tags to generate XLS file – it is smaller than XML a...
Hi, I need to access an excel spreadsheet and insert the data from the spreadsheet into a SQL Database. However the Primary Keys are mixed, most are numeric and some are alpha-numeric. The problem I have is that when the numeric and alpha-numeric Keys are in the same spreadsheet the alpha-numeric cells return blank values, whereas all ...
Here is what I'm doing : Create a workbook in memory (book = new HSSFWorkbook(), ...) Save it to disk (book.write(...)) Open in Excel (ok) Create another workbook in Excel, which links to the first one (=PoiWorkbook?xls!A1) Close Excel Then everytime I open the second workbook again, all the links are #N/A, unless I also open the POI...