I do an import data from a query I created in MS Query. Lets say the columns are in order A,B,C,D,E,F in the Query Editor. Once the I save the query, return data to Excel, the imported data has a new column order A,B,C,F,D,E -- note the F column was moved where D was. Any ideas on how to solve this issue? Thanks guys. Assume variabl...
I have an excel worksheet with various names and I want to create an object that references that name. The code I am working with uses the cell address, but that is very cumbersome, and I would rather use the names. My code looks like this right now: myWorksheet= GetWorkSheet(myWorkbook); Excel.Range range = myWorksheet.get_Range(cell1...
With the following function: =FREQUENCY(C2:C724,D2:D37) The second parameter is the BIN What I don't understand is why Excel would increment the BIN for the rest of your values. The BIN does not change! It stays the same bin. Yet when I paste the formula for all my values it does this: =FREQUENCY(C2:C724,D2:D37) =FREQUENCY(C2:C724,D...
I have code that saves an excel file to a specific location and filename that cannot be changed by the user. It works fine when the file doesn't exist and saves silently. If the file does already exist and the user selects to overwrite it then it works fine as well. The problem occurs when it already exists and the user cancels the sa...
How do I pass multidimensional arrays or nested arrays into an Excel UDF which allow me to reference other ranges? I have a UDF defined as "ARY" which does what Array() does in VBA but in a worksheet function. This allows me to have a worksheet formula like =TEST1(ARY(ARY("A", "B"), "C")) or =TEST1(ARY(ARY(A1, B1), C1) However, I get...
Hi, I have an excel spreadsheet with 15 columns, one of which is EmailAddress and then 100,000+ records.. In my data i know that there are many duplicate email addresses. Can someone tell me how can i extract a distinct list where each record is represented only once by emailaddress? Alternately, if i import the data into SQL, how ca...
i am using the following code to convert an excel set of data on a worksheet to a C# dataTable: var connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + _filename + ";Extended Properties=\"Excel 12.0;HDR=YES\";"); var adapter = new OleDbDataAdapter("SELECT * FROM [owssvr$]", connection); c...
Hi, I have something of the following : Sheet 1 (Name Range : Code) Column A -------- Column B School ------------ 1 College ----------- 2 University--------- 3 Sheet 2 Cell A1 = (Search for "College" in Name Range Code and get the Value of "2" as code. ) So value of 2 should be present in Cell A1. Please provide excel VBA ...
I have a column as follows : Column A---------------Column B 100 ----------------- 500 200 ----------------- 600 AA ----------------- ABCD BB ----------------- DEFG CC ----------------- FF DD ----------------- GG EE -----------------II 300 ----------------- 700 400 ----------------- 800 I want the Cursor to be present at cell location...
hi, i have 3 datatables .i need to write these three datatable data in an excel sheet. as we know that under one excel sheet i can have (more than 1 sheet). in a same way i need to write my 3 datatable data into one excel(where sheet1 will conatin dattable1,sheet2 will conatin dattable2,sheet3 will conatin dattable3) this is the...
I am creating a report in Table format. This report is displayed on the browser. I want to provide a download report in excel format link so that the report can be available in excel file also. How can I do this ...
Hi all, Again I'm wrestling with Excel 2007! I have some data I'm using to generate a Pivot Table and I'd like to divide all of the data by 1000... I know I could go in and change the original data by hand but I'd much rather have a formula in the Pivot table itself that does this for me. Any thoughts? ps: You guys are awesome! ...
Hi, I have a large CSV file full of organisations names and addresses. I need to hand over a clean csv file to our developers who need to build a database around them. The problem is that the street names and numbers are in the same cell, and they need to be separate, so that we can filter organisations by street names. So, I would lik...
I have a VBA application that creates an instance of a COM object and then continuously polls the objects DataReady property to see if there is new data. When new data is available it sticks the data into a spread sheet. The problem is this macro (vba sub routine) continually runs and this slows down excel to a crawl and makes the comput...
Single line of code is causing my application to stop working - I think that the problem is with the apostrophes (maybe wrong escape): .Formula = "=IF(AND(" 'Criterion " & i & "'!" & cellAdress & ">=1;"'Criterion " & i & "'!" & cellAdress & "<=4);"'Criterion " & i & "'!" & cellAdress & ";0)" When I try to enter the "' sequence VBA aut...
I have not had much experience with VBA, but I sometimes use it in work. Recently, I have encountered a problem that shouldn't happen, and that neither my boss nor myself can figure out. Basically, the issue is that the Application property DisplayAlerts is set to True by default, and can't be changed for some reason. Possibly related, ...
Hi, I am facing performance issues while reading/writing data from/to MS-Excel cells. I am using MS Excel 11.0 object library for automation with VB.NET. Currently it takes too much time to read and write from/to Excel files. (10 mins to read 1000 rows :( ). It seems the cell-by-cell reading and writing approach is not that effiecient....
I am using an array formula (in Excel 2003) to count the number of strings meeting a specific condition. I am using a column of strings instead of a table of cells to store my data because of file size limitations. The formula is below: {=SUM(IF((VALUE(MID(INDIRECT(CONCATENATE(D1,"test")),6,1))*VALUE(MID(INDIRECT(CONCATENATE(D1,"test"...
I'm using VS 2010 to develop an Excel 2007 COM Add-In. Because it's a VS 2010 Office Project, it targets the .NET 4.0 Client Framework. I've added a new Ribbon (XML) item called MyRibbon, so default filenames of ThisAddIn.cs, MyRibbon.cs, and MyRibbon.xml. Everything builds fine. It publishes with a .vsto extension. When I install the a...
hi, I have the following excel formulae in different columns: = IF( OR(D2 <1000, D2 = 1000),"5","") = IF( AND(OR(D2 <10000, D2 = 10000 ), D2 >1000),"4","") = IF(AND(OR(D2 > 10000, D2 = 100000), D2 < 100000), "3","") = IF(AND(OR(D2 > 100000,D2 = 100000), D2 < 1000000),"2","") = IF(OR(D2 > 1000000,D4 = 1000000),"1","") so total 5 ...