Hi: I am trying to implement a UDF using Excel COM Add-in(2003) with Visual Studio 2005 in C#. I would like to implement the UDF using async programming. The UDF is a slow operation as its results are fetched from a server. As an illustration(not a real world example),the following UDF works fine without any issue: public double mul(...
Hi, I'm having a strange problem with Excel and MS Query: I'm using MS Query to run a T-SQL query against a Microsoft SQL Server 2000 and return the results to Excel. To do this, I open Excel, go to Data -> Import external data -> New database query, select my data source, paste the SQL script in MS Query and click File -> Return data ...
So, I need to find an Excel 2003 substitute for =SUMIFS, which is only 2007+ (apparently). The formula is used to generate this summary data table, from a list of revenue, where each revenue line has the field type (static, email or outreach) and the field fund (ABC, QRS and XYZ). type fund total count average static ABC $1...
I have a cell that takes the time value from another cell. I want to include an addition of this time as well as a dash '-' to format the time into a sort of schedule. Example: userinput cell: 5:00 AM Formated cell (how I would like it to look): 5:00 AM - 3:30 PM What would the function be to get something like this? ...
I am creating an Expense Reimbursement spreasheet that will allow users to input their expenditures and the spreadsheet will automatically add all of the columns. I have protected the spreadsheet so that they can only enter information in unprotected cells. The problem I am having is: how do I protect the sheet so that when someone pri...
Hello, I am using apache POI 3.6 to generate excel (2003) sheets. I want to insert a formula to a cell which calculates a sum of a certain cells on several sheets. I have sheets named a, b and c and want to calculate the sum the cells A1 I tried: cell.setCellFormula("a!A1+b!A1+c!A1"); POI does not produce any errors, but when ...
Hi I m importing Data from Excel to SQL Server 2005. It is Working fine. The problem is 1) In one full column of Excel i have some Raw information how to spilt those information and store in SQL fields. 2) There is Single quotes (') in many fields so how to convert those to as something like ('') before taking to SQL. ...
I'm using Auto_Open on a Excel Template (.xlt) to run a macro which imports some data to a sperate sheet, adds some forula and then creates a number of pivot tables and charts. The result of which is a finalised report which can then be saved as .xls. I have added the following IF statement to the top of the Auto_Open sub to check cell...
Hi, everyone! I'm writing .Net desktop application to create reports in Excel 2003, but I have only Excel 2007, and have no license of Excel 2003. This is my problem, because when I launch this app on client’s computer (Excel 2003), I have error (SystemNullReferenceException). I know reasons of this exception (different versions of E...
I had wanted to put a solution onto a thumb drive or dvd to be distributed as needed. I have a couple html pages with javascript to display photos and some other info. I also have an excel spreadsheet (2003) that I wanted to link to the html page. However when I try to use a querystring I get a warning from excel stating that it "cann...
I created a macro that doesn't allow values in both the B and C columns of an Excel sheet. I want to make an exception to that formula by allowing cells with the names DayShift or AfterShift. I created this: If ActiveCell.Name = "DayShift" Or ActiveCell.Name = "AfterShift" Then But it doesn't seem to work and just makes other cells e...
All I'm trying to do is figure out how to change the cell in one worksheet to that of another when the one in the other is double clicked. In my second worksheet, I have the following code set up: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) End Sub My question would be what's the code to chan...
I'm trying to set up Excel so that the cell's value that is selected in the first worksheet is set to the value of a cell that's double clicked in a different worksheet. So far my code looks like this: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim c As Range For Each c In Sheet1.Range("M11:M24...
Hi, I'm trying to embed mysql queries into excel and I've been mostly successful so far. I'm using the odbc and Import Data External.............From a database query. Problem is that when I return text columns they show in the query and in the msquery browser but they won't return to the sheet. How do I have text columns return do t...
Hello All, Win-XP / Excel 2003 / VBA .... I have the following piece of code to intercept all paste activities initiated by the user (main menu, context menu and control-V key) and send it to a Sub TrappedPaste() .... Application.CommandBars("Edit").Controls("Paste").OnAction = "TrappedPaste" Application.CommandBars("Edit").Controls("...
I would like to conditionally copy data from multiple worksheets into a single worksheet in a given workbook in order to consolidate data. The macro would look at column F in all the worksheets, and if a row in column F matches a given number, that row gets copeid. Any help would be great!! Terry ...
I have a bunch of Excel.Name objects under Workbook.Names collection which contain invalid references. In other words, when I evaluate the Excel.Name.RefersTo property, I get a value back beginning with "=#REF...". I tried to run the following code but it seems to have no effect in removing the names: var ranges = myWorkBook.Names; for...
I have an App_SheetChange() handler in an add-in that does some processing as modify their spreadsheets. I've already optimized the handler to only perform the processing if the user's update happens within the range that the processing is dependent on (only one column). But when users delete rows or insert rows, I don't need my routin...
Hi I have a function that is meant to run the ShowPages() command of a PivotTable and then save each sheet to a separate file. Here's how I wish I could do it: Sub Split() ThisWorkbook.Sheets("Data").PivotTables("Data").ShowPages PageField:="Codename" Dim newWb As Workbook For Each s In ThisWorkbook.Sheets If s.Na...
Hi i just want the formula to calculate average waiting time depending upon the time difference between the events and number of the events occured............... For example if a customer comes to me at 9:00 am and i issue him a token then i give a token to another customer at 9:07am and so on...... and thus at the end of the day i wa...