I often use MS Excel's Get External Data to create simple reports - running queries against databases and displaying nicely in Excel. Excel's great features like filtering and pivot tables and familiar interface for users make it quite good for this. However, one limitation with Microsoft Query is you can't add parameters to queries that...
I have two columns, **COL-A** **COL-B** 1 419 393 2 2199 1513 3 3 3 4 23 20 5 3 3 4 2964 2181 I want to have conditional formatting using color scales between each A1 B1, A2 B2, ... Currently when compared, 419 is g...
I have an excell fill with these data in it : Name1 1 2 3 Name2 1 2 3 4 Name3 1 2 ...... I want to have Names in a cell in front of each row below it like this : 1 Name1 2 Name1 3 Name1 1 Name2 2 Name2 3 Name2 4 Name2 1 Name3 2 Name3 .... is there ...
One worksheet doesn't show the first 15 rows. I tried the followings, neither worked, even in full-screen mode step 1: F5 (Ctrl+G) to define & select 1:15, set row height to 20 and "unhide row", they are still hiding; Step 2: Put cursor in A16, then up.. till A1 (invisible), select entire row (Ctrl +Shift + ->), hold on shift key, pres...
I have an excel file that is a SEO report of a website. But the tool that generated the report included everything under the sun and most of it is junk. I want to filter out the important info such that I keep only records in the first worksheet that correspond to a record in a second worksheet. Basically I want to keep all the records ...
I have two columns of integers between 1 and 16 in an excel file. I'd like to count the number of pairs of integers in these columns. There are 256 cases and I'd like to have a column which tells me how many pairs exist for each case. For instance, I have a table like below: 1 2 1 1 1 3 1 4 1 1 1 8 1 1 16 16 1 2 ... And I'd li...
Hi, I've created an ODBC connection to an Excel 2007 Workbook using ACEODBC.dll, which is supposed to work for both .xls and .xlsx files. However when I try to open a connection using JDBC I get a SQLException: [Microsoft][ODBC Excel Driver] External table is not in the expected format. The Workbook is also password protected and encryp...
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...
I've got a XLL Addin and I'm trying to run it under Excel 2007 XP without VBA installed. My addin is well registered (OPEN key as /R "C:\Program Files (x86)\MyAddin\myAddin.xll" in HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Options). When debuging, I see that DLLMain is called... but not xlAutoOpen (neither others xlSomething...
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...
Is there a way to see if an Excel Workbook, say DataSheet.xls, is open (in use) or not? I would like to close that Workbook if it is opened. ...
Hi All I want to add same value/text in every cell in a column of ms-excel any idea??? edit::: actually i have data like follows [email protected] [email protected] [email protected] [email protected], [email protected] [email protected] [email protected] [email protected]...
Hello I am totally new to the world of macros but have done some VBscript on QTP before. I am looking to create a macro which would hide certain columns depending on the users selection from a drop down. I am unsure of the syntax and how to identify the columns I wish to hide and how to identify the cell with the dropdown. Here's roug...
I am writing an Excel File using Apache POI. I want to write in it all the data of myResultSet which has the fieldnames(columns) stored in the String[] fieldnames. I have 70000 rows and 27 columns My Code: String xlsFilename = "myXLSX.xlsx"; org.apache.poi.ss.usermodel.Workbook myWorkbook = new XSSFWorkbook(); org.apache.poi.ss.user...
Hi, I'm trying to delete all charts that a linked to a specific PivotTable. My code is really simple, but I can't figure out how to test if the found chart's datasource is the given PivotTable's data. public void DeleteCharts(Worksheet mySheet, PivotTable myPT) { // Delete all charts foreach (ChartObject item in (C...
Hey all i am trying to figure out how to go about calculating this formula from an Excel 2007 fx to javascript: =(B12*'ROI endo only'!B5)/(1-(1+Data!B12)^(-Data!B13)) WHERE B12 = 0.49% B5 = 99,500 B13 = 48 So far i have this as my javascript code: var theCalc = (0.49 * 99500) / (1 - (1 + 0.49)^(-48)); alert(theCalc); ...
Hey again everyone. Yet again i am having some problems with trying to get the match correct on this Excel Spreadsheet to JavaScript conversion. Here is the excel formula: =IF(IF($B$7=TRUE,$B$28/$B$10,$B$28/$B$5)>1,1,IF($B$7=TRUE,$B$28/$B$10,$B$28/$B$5)) WHERE B7 = TRUE B28 = 76800 B10 = 892015 B5 = 999500 And this is my Jav...
Recently, I saw an application (Excel 2007 , C# communication). In that application, the custom menus that was created from VSTO does not appear if one opens the excel normally. In the RUN WINDOW of Windows OS (XP), if we issue certain command (e.g. excel /appnamediagnostics) then only the menu gets visible in the excel. I hope I am ...
i am trying to search for [Panels] like "*,*" within a worksheet and unfortunately instead of retrieving exactly that string it retrieves anything like: [Panels] like "*blah,*" [Panels] like "*blah,123123*" i need it to be EXACTLY [Panels] like "*,*" and i suppose that perhaps i would to do this in VBA instead of manually? if this i...
How to transfer more than 65536 rows from Access to excel 2007. There is no limitaion on the part of Excel 2007 as the new version supports much more than 65536 rows in Excel. Limitation is coming on the part of Clipboard. It is not allowing to export or to Copy more than 65536 rows from Access. Thanks ...