How can I write an Excel macro to select a group of cells?
I'm looking for a macro which can be run to select a consistent range of cells so that I can easily copy them to another spreadsheet. The range would be F3:BJ3. Thanks for your help. nlh ...
excel
How go I get a reference to a cell in an array knowing only its value?
I have a column array with the following values in my sheet: 11, 15, 5, 7, 2. I want to get a reference to the cell which contains the value 15. How would I go about doing this? Thanks ...
access table data to excel
Hi I have a problem I have got stuck on. I want to export my Access table to an Excel file, currently, I am doing that using DoCmd.TransferSpreadsheet but I want some kind of formatting to be done on the exported data. Can I format that data I am sending to Excel or do I have to write a macro in Excel that will format that data after ...
How do I do ConnectionPoint in Delphi?
I'm writing a DLL which talks to Excel via its IDispatch interface. From VBA I pass in a Variant containing Application.Caller from which I draw the IDispatch pointer via .pDispVal. What I'd like to know is how to query the interface via that IDispatch pointer. I want to set up a connection point container, and from there find a connec...
Case sensitive validation in Excel from Named List
I'm using VBA in Excel 2003 to apply validation to apply validation to a given range of cells from a named list. The user can then select from a dropdown list of values. Edit: Here's how I'm setting the validation, given a named range called 'MyLookupList' With validatedRange.Validation .Delete .Add Type...
SQL Server to Excel 2007 - New Lines
I'm trying to retrieve data from an SQL Server 2000 server, and place into Excel. Which sounds simple I know. I'm currently Copying, and Pasting into Excel, from Management Studio The problem is one of the columns is an address, and it’s not retaining the newlines. These new lines have to stay in the same cell in excel, I.E cannot take ...
Excel 2002 Add-In not loading when the application opens
Good afternoon, I created an Excel .xla addin for Excel 2002. This weird behavior I am seeing from it happens only on my machine, but not on my coworkers. I would like to understand why. The add-in has a UDF function that gets called from many, many spreadsheets. My Add-in is set to open when Excel opens. When I open a blank sprea...
SQL import wizard drops leading zero
I've read all the posts about prefixing the numbers with ', and setting IMEX=1 in the connection string; nothing seems to do the trick for me. Here's the setup: Excel column with mixed data - 99% numbers (some start with 0) 1% text. PROGRAMATICALLY mporting into SQL Server 2005 table / column type - varchar(255). Import works fine lo...
Surface a .NET method as a UDF in Excel 2007, using a VSTO 2008 Add-in
We have an existing add-in that we publish to users via click once. We would now like to use this as a vehicle to publish some of our existing C# methods directly into Excel so that the users can call them as a UDF. For example - I have an assembly called MyAssembly, that has a class called MyClass with a public method called MyMethod. ...
Import from Excel to MySQL database using SQuirrel
I have an Excel spreadsheet with a few thousand entries in it. I want to import the table into a MySQL 4 database (that's what I'm given). I am using SQuirrel for GUI access to the database, which is being hosted remotely. Is there a way to load the columns from the spreadsheet (which I can name according to the column names in the dat...
Zip Code distance in excel
I am looking for a way to get a function in excel to retrieve the distance form one zip to another. Lets say i have a list of 100,000 possible recruits and I want to sort by nearest. Id have the conference zip code and then 100,000 recruit zip codes. I'm looking to be able to just write a function =ZipCodeDistance(a2,d1) and then sort by...
Importing Excel into SS2000; Error on Field; DTS
I'm trying to import an excel file in to a SQL Server 2000 database using DTS. This is nothing fancy, just a straight import. Where I work, we do this 1000 times a day. This procedure usually works without an issue but something must have changed in the file. I'm getting the below error: I've checked to ensure that the column "Assi...
Can I make a structured reference absolute in excel 07?
I have a table "A" with 2 columns "Foo" and "Bar". I have a formula with the structured reference A[Foo]. When I fill this formula horizontally I want the reference to stay A[Foo] but now, in the second column, the reference turns to A[Bar]. Is there a way to make this structured reference absolute? It'd be shocking that this isn't sup...
How do get the index of a table's column by using a structured reference in excel?
I have a table with 3 columns. I want to write a formula that, given a structured reference, returns the index of the column. This will help me write VLookup formulas using the structured reference. So, for example, for the table MyTable with columns A, B, C I'd like to be able to write: =GetIndex(MyTable[C]) and have it return 3. Righ...
How do I set the background color of Excel cells using VBA?
As part of a VBA program, I have to set the background colors of certain cells to green, yellow or red, based on their values (basically a health monitor where green is okay, yellow is borderline and red is dangerous). I know how to set the values of those cells, but how do I set the background color. ...
Using VB to automate IE "save target as"
I'm trying to use an excel VB macro to download excel files from a membership password-protected site. I am using the "InternetExplorer" object to open a browser window, log-in and browse to the correct page, then scanning for the links I want in the page. Using the Workbooks.Open(URLstring) doesn't work because Excel isn't logged. Inste...
Where to find out the method used for excel functions
Hi all, I am looking for a source for the processing steps behind Excel functions. For example I would like to know how exactly the function PERCENTILE(array, percentile) works. Having embarked upon a quick search I couldn't find anything really, so was wondering if anyone here knew of a better source. The reason for my search is that ...
Loop through PivotItems: runtime error 91
I have a dataset in a worksheet that can be different every time. I am creating a pivottable from that data, but it is possible that one of the PivotItems is not there. For example: .PivotItems("Administratie").Visible = False If that specific value is not in my dataset, the VBA script fails, saying that it can't define the item in th...
Making a C#-based Excel add-in run out-of-proc with minimal pain.
Hi, I have developed a prototype C# Excel 2003 add-in in VS2005 that supports an object with some simple calls, plus a separate RTD class, all sitting atop a large existing in-house C# stack. It all works fine as-is, but... I'm told that in order to avoid potential conflicts with other Excel add-ins that might want a different .Net ru...
Excel Add-On to display data from (My)SQL Database
Hello, is there an easy way of importing data from a mysql/odbc datasource into an excel spreadsheet? The user should be able to select some values from drop downs (e..g date range, branch name etc.) and the values from the dropdown should be used to populate (prepared) SQL statements. The results should be displayed in the Excel file...