ActiveX combobox objects in Excel do not behave well when their ListFillRange refers to a formula-based Named Range (Defined Name). I think I have encountered other errors and possibly even Excel crashes thanks to this, but right now all that happens is the combobox_change() event is triggered anytime ANY cell in the workbook is changed...
I use "WinHttp.WinHttpRequest.5.1" to send HTTP POST requests from VBA in Excel. But I could not manage to do it for HTTPS, as I received an SSL certificate error. What VBA code would you use to negotiate an SSL connection to a website from VBA in Excel ? ...
I have a client who is incapable of formatting spreadsheets properly. I've asked them 10 times over to follow a sensible guideline (As in FORMAT THE COLUMNS WITH THE CORRECT DATATYPE) but in their lack of intelligence or professionalism or both, they just can't /won't do it. I have an ODBC connection set up for the spreadsheets and pull...
I'm working on a protected Excel spreadsheet and ran into a problem with cells that I've programmatically locked that are still selectable. I can't click on them directly to select them, but if I select the cell to the lower right of a locked cell and then shift-click the one to the upper left, all 9 cells (including the 'locked' one) a...
Hi, I need some help in doing a logic that would convert a numeric value to corresponding MS Excel header value. For example: 1 = "A" 2 = "B" 3 = "C" 4 = "D" 5 = "E" ......... 25 = "Y" 26 = "Z" 27 = "AA" 28 = "AB" 29 = "AC" 30 = "AD" ......... Would appreciate some .NET codes (C# or VB) for this. Thanks. ...
Hi, I am using a web application to read the excel file and uploading it in the database. I have deployed the application in the server.I am using windows service 2003 as the server.I am able to read the excel file only if i set the set the security settings to "network service" for the microsoft excel application in the control p...
I have an XY Scatter chart in Excel with 400+ points in four data series. I would like to add text to each data point by modifying the Chart tips (which defaults to showing the series name and the point x,y value). Is this possible? Things I've tried so far: There is a tool here which adds permanent text labels. This is great for s...
I'm using ODBC, OLE is available if needed to dump approx 3 different types of data to different excel sheets. But to use insert statements in excel, the table has to be created first I believe, especially if the sheet doesn't exist yet. I used to use sql server 2000's import/export wizard that automatically generated the create table s...
I need to combine rows from two Excel worksheets into one long row, based on an ID column in each of the worksheets. Effectively I want to perform the equivilent of a SQL JOIN on two worksheets to produce a combined worksheet. I can get a Range object for a row in the first worksheet, and then use the ID column to get a Range object for...
Hi all, I have a need to export the contents in a ComponentArt Grid into a file, preferably excel in csv format. I was wondering if anyone had any ideas how to best approach this task. At the moment we have the grid being populated with data, and using client templating some minor manipulation is being performed before it is displayed...
I have an excel spreadsheet that has about 18k rows and three columns. I want to use this as a key in an Access database. So the three columns are [IDKeyNumber] [Name] [Category] The problem is in certain places the IDnumber is duplicated because the category has two category codes (typeA & typeB) All I really need to know how ...
I'm trying to evaluate various techniques and libraries in an attempt to find a way to read Excel spreadsheets when running as a service on an NT server. So basically I'm looking for any methods/libraries that people know of that I can use. I need to be able to read .xls .xlsx .xlsm and .xlsb files. If anyone knows of anything else tha...
I have a range in excel with dates and values EDIT:image added I want to select the MAX value from the HIGH column for each YEAR (2009 would return 404, 2008 would return 390) I've done something similar but it's using SUMIF, not MAX. Any excel people in here that can help me out? ...
Hi guys, I'm looking for a program that can read the weight on a USB scale, namely the Pelouze 10lb USB Portable Scale. I thought it would have a virtual COM port driver but instead, it uses HID drivers. I've been searching for a whole month for a program that can help me transfer the reading of the scale to Microsoft Excel. Can someone...
I have created a C# application whose inputs are some large Excel files. In my code, I open it, process and then close it. The whole process takes some 15-20 minutes. During this time when I try to open some other Excel files(1) externally, anyone of the input Excel files(2) (which is currently being processed) is also getting opened al...
I'm working in VS2005 and I need the C# syntax write to write collection values in an Excel sheet. I initialize the collection like this: Reports oReports =new Reports();//oReports is a collection Assuming that this collection has values, how would I write them to Excel? ...
Hi all, I use C# to automate an excel file. I was able to get the workbook and the sheets it contains. If for example I have in sheet1 two cols and 5 rows. I wanted o get the range for the occupied cells as A1:B5. I tried the following code but it did not give the correct result. the columns # and row # were much bigger and the cells we...
I'm using MS Excel to get data from a MySQL database through ODBC. I successfully get data using an SQL query. But now I want that query to be parameterized. So I wonder If it is possible to use a cell value (a spreadsheet cell) as a parameter for such a query. For example, for this query: select name from user where id=1 I'd like to ...
I have written a few Excel 2003/2007 add-ins using VSTO, and I usually end up referencing .NET dll's in the VSTO project (usually code I reuse across projects). I ran into the following issue. While the calls to the dll work perfectly in debug mode or on the development machine, when I deploy the add-in through a msi installer, the dll i...
In asp.net, it is quite easy to convert a datatable to an excel file. How do I do the same for datatables in winforms? For Example: in my asp.net code, here is my function to convert the datatable to excel: Public Shared Sub DataTableToExcel(ByVal dt As DataTable, ByVal FileName As String HttpContext.Current.Response.Clear() Http...