excel

Excel Smart Tags

Can someone point me to a good tutorial for implementing Excel Smart Tags in Visual Studio? Either with C# or VB.NET. ...

How to programatically re-enable documents in the MS Office list of disabled files

MS Office programs keep a list of disabled files that have caused errors when previously opened. A user can remove documents from this list by accessing the list through the program menu and selecting a document to be re-enabled. (http://support.microsoft.com/kb/286017) The question is: How can this re-enabling of documents be accompl...

How to export SSIS to Microsoft Excel without additional software?

This question is long winded because I have been updating the question over a very long time trying to get SSIS to properly export Excel data. I managed to solve this issue, although not correctly. Aside from someone providing a correct answer, the solution listed in this question is not terrible. The only answer I found was to create...

Unlock cell on a condition from adjacent cell

I have two columns but the codition I would like is to be evaluated from one cell to another. The first column has cells which have a drop down validation with names, and the second will activate only if a certain name from the adjacent cell is selected. so far i only found this code but it does not seem to work: Private Sub Worksheet...

Excel Automation question - how to just say No

I'm working on a vb.net console app that opens a number of spreadsheets one by one, reads in a couple cells and closes the file. Some of the spreadsheets have formulas that Excel recalculates when I open the file, and I'm getting a dialog box asking me if I want to save changes to the spreadsheet when I close it. Message: "Do you wan...

OLEDB, Writing Excel cell without leading apostrophe

I'm writing to Excel file using OLEDB (C#). What I need is just RAW data format. I've noticed all cells (headers and values) are prefixed by apostrophe (') Is it a way to avoid adding them in all text cells? Here is my connection string: string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Exten...

Create a custom worksheet function in Excel VBA

I have a faint memory of being able to use VBA functions to calculate values in Excel, like this (as the cell formula): =MyCustomFunction(A3) Can this be done? EDIT: This is my VBA function signature: Public Function MyCustomFunction(str As String) As String The function sits in the ThisWorkbook module. If I try to use it in the ...

Converting the Text into Number

Morning All, Today i came up with the new assignment of Converting the Text into number automatically by VBA Codes. For Example '1233 need to converted to 1233 and (1234) need to converted to -1234 without any human interference. Some Advice is very helpful. Regards, A Jabeer Ali ...

How to Get Active Excel Instance?

I have an application in C#, this application paste info to excel on user demand, the logic behind is like this, if there are none excel instances running, it creates one and paste to that instance. If there is only one instance running, it tries to get that instance and work with it. This is the code I'm using to do so: Guid _guid; CL...

Embed excel sheet in C# user control

I need to embed an excel sheet portion (not the whole sheet) in a user control in C# language. And I want to manipulate the data on it and save it in a collection. What is the best way to go about it? ...

Excel Reference To Current Cell

How do I obtain a reference to the current cell? For example, if I want to display the width of column A, I could use the following: =CELL("width", A2) However, I want the formula to be something like this: =CELL("width", THIS_CELL) ...

Excel Doesn't Auto-Fit 45º Text

When I orient column headers at 45º I have to manually resize each column since Auto-Fit won't let the oriented text overlap with the neighboring cell. Is there a way to programatically (with VBA) auto-fit the columns where they'll overlap? I'd like a solution which takes font size into account too. ...

Insert row in Excel (late binding)

Hey, I'm trying to insert row into Excel. I'm using late binding. I opened excel workbook, copy sheet, insert values into cell, but i cant figure it out how to insert new row after specific row. ...

How does one sum only those rows in excel not filtered out ?

The title says it all really - I use the SUM(B1..B20) formula to sum a column, but when I filter the data by an adjacent column, the sum doesn't update to reflect only those rows not filtered out. How does one do this? ...

Import de-normalized relational data from Excel into SQL Server

I need to import data from an Excel spreadsheet into SQL Server, but the data isn't in a relational/normalized format so the import wizard isn't going to cut it (as far as I know). The data is in this format: Category SubCategory Name Description Category#1 SubCategory#1 Product#1 Description#1 Category#1 SubCatego...

How do I clear a table in Access with VBA?

What I'm trying to do is, while in Excel, use VBA to push data to an exsisting Access table. I've been able to do this, but am having one small hickup. Before I push the data to access, I want to clear the current data on the Access table, so when the new data from Excel comes in, it is the only data in the Access table. I really don'...

Speed up pivot table filtering VBA code

I have a pivot table with a pivot field and contain many items. I've VBA code logic to decide if the pivot value should be visible or not. The problem is excel recalculates pivot table for each field shown or hidden which makes it very slow. I would like something where it recalculates only once, after all the values are set. I tried usi...

Manupulating Excel files from Windows Scripting Host

Is there a fast way to manipulate the contents of an existing XLS file from Windows Scripting Host? We have Excel templates we received from a customer. Our task is to fill these templates with the data we fetch from an Oracle database. The current approach is to use Windows Scripting Host and VBScript: Get data from Oracle using ADO...

Excel Formulas/VB: Show total based on date entry

Hi, all. I'm trying to accomplish something in Excel that I'm pretty sure requires some VB work, which I've never actually done, before. I've got a spreadsheet with two columns that represent the number of processed records, and the date the records were processed. In some cases, the records can be processed in multiple batches, so th...

SqlBulkCopy does not copy strings > 255 characters long

I am trying to copy a large Excel spreadsheet into the SQL Server database. I am opening an OldDbConnection to the Excel spreadsheet and reading everything from the [Sheet1$]. Then, I am using the OleDbCommand to get a IDataReader with the spreadsheet data. There are several cells in the Excel sheet with text contents of more than 256 ...