excel

Excel autofill event in C#

I'm writing an Excel add-in using C# and need to be able to tell when users autofill cells. How can I catch the autofill event? ...

How to open Excel 2007 (xlsx) file with CDaoDatabase

Using CDaoDatabase defined in afxdao.h, how can I open up Excel 2007 (xlsx) files? I can open up Excel 2003 (xls) files using the connection string Excel 8.0;HDR=NO;IMEX=1, anybody know how I can do this? Thanks! ...

How to build a consolidated pivot table when the source data contains column headings that are dates?

I have a customer who is currently using Excel to do their staff planning. They have many workbooks for different projects and each project contains 1 or more sheets containing the actual staffing data: The customer wants to consolidate all of the data from all of these many sheets and workbooks into a single pivot table. A 'consoli...

Unable to access Excel Form Objects through VBA when Items are Grouped

Is there anyway to access (through VBA) properties of a form object (using the forms toolbar for pre-2007, and on the developer tab / insert / Form Controls for 2007) when it is grouped? For example, normally you could access the max property of a Spinner control with the following code: Sheet1.Spinners("Spinner 1").Max OR Sheet1.S...

regular expression in vba excel looking for groups out of a string

I am currently working on a vba project that has the end user copy/paste long strings of text into a worksheet and the code to parse out data from the junk in these strings and organize it from them. The strings will always be in different lengths, and have a different number of spaces between the data. However they will always be g...

Pulling date and time values from excel (CSV) to C#

Hi everyone, I’m building a application that monitors information on a website. The website allows you to save stuff off it as a CSV. My problem arises when I try to pull time and date information from Excel. For those who want to see what spreadsheet I am working on: http://ets.aeso.ca/ets%5Fweb/ip/Market/Reports/CSMPriceReportServle...

Exporting excel to xml spreadsheet with blank cells

Hello I am exporting an excel workbook into xml spreadsheet The excel has lets say 10 columns and 10 rows Some of the cells are empty(i.e with no value) When i save the file into xml spreadsheet and review the row that has blank cell in it it has only cells, the cell with the empty value is not there and the xml show that the cell befor...

Mailmerge macro from Excel datasource gives dialog "name or cell range"

A Word mailmerge macro from excel datasource gives dialog "name or cell range" How can I hide that dialog? ...

Tool for transforming Excel files? (swapping columns, basic string manipulation etc)

I need to import tabular data into my database. The data is supplied via spreadsheets (mostly Excel files) from multiple parties. The format of each of these files is similar but not the same and various transformations will be necessary to massage the data into the final format suitable for import. Furthermore the input formats are like...

MailMerge seem to open Excel file twice?

With ActiveDocument.MailMerge .MainDocumentType = wdCatalog .OpenDataSource Name:=excelfile, _ Connection:="Entire spreadsheet", SubType:=8, ReadOnly:=True ''# Range = Selection.Range .Destination = wdSendToNewDocument .Execute End With ''# Activedocument DDETerminateAll Why does this code open "excelfile" twice to excel, one of these ...

HOW TO close Excel instance started by mail merge

HOW TO close Excel instance started by mail merge this code running inside launcher does not have access to Excel running via DDE ?? 'For i = 1 To Workbooks.Count ' MsgBox ("here" + Workbooks(i).Name) 'If (Workbooks(i).Name <> ActiveWorkbook.Name) Then 'Workbooks(i).Close 'End If 'Next i ...

Excel VBA: Recreate Source Data from Pivot Table Cache

I am trying to extract the source data from a pivot table that uses a pivot table cache and place it into a blank spreadsheet. I tried the following but it returns an application-defined or object defined error. ThisWorkbook.Sheets.Add.Cells(1,1).CopyFromRecordset ThisWorkbook.PivotCaches(1).Recordset Documentation indicates that Pivo...

Excel: Removing duplicates in one column while retaining highest value in the adjacent column.

I have an excel spreadsheet with two columns. The first column is a label the second column is a numeric value. I would like to remove all the duplicate labels in column "A" and remain with the maximun numeric value in column "B". I've tried to illustrate below (the filter would result in "Consolidated Sheet" given "Original Sheet": NOT...

Excel: how to start macro recording from OLE

I control Excel instance via OLE and I want to start macro recording. Can I do that via code? The only thing I've found is Application.RecordMacro and it doesn't do what I need. ...

Problem with dates using CarlosAg.ExcelXmlWriter

I am using the CarlosAg.ExcelXmlWriter library to generate an Excel file in C#. If I treat all data as strings everything works fine, but I need some cells to be recognized by Excel as date fields. When I try to set the data type accordingly, the resulting Excel file fails to open in Excel 2003 (or Excel 2007 for that matter). In Exc...

Excel to XML, encoding question

This is all mixed up in my head, and I can't wrap my head around it. I have an excel file I have to parse, using Java, and translate to XML. Using the jExcel library, I can achieve the parsing, and the application's doing the right work, and putting the right strings in the right place. So for the parsing part, I've got that covered. P...

Can't release Excel COM object in .NET

Hello, I have created a tool that imports an excel sheet. The excel COM object is created during the lifetime of the app. I have applied the MVP pattern to my tool so that VIEW and Presenter are seperating the UI and logic. The vIEW that is a WinForm is having a Dispose() method due inheritance from From class, which is overriden in th...

Fill array with consecutive integers

I'm trying to fill an array with consecutive integers using Excel and VBA to pass to the Subtotal function. I need a dynamically sized array of consecutive ints. The fnction works when I use Array(1,2,3,4,5) for example, but not if I try to use a dynamically sized array. I've read some articles on this but none actually worked - has any...

Excel VBA returning weird results with large calculations

I've created a function in excel which basically searches a dynamic range in a For statement for a string and returns the value of the cell one column over. It's basically a budgeting function, but that's beside the point. Here's the problem, everything works with small results, but when the results get too large (say around 32000... fo...

Pretty Print Excel Formulas?

Does anyone know of a (free) tool to pretty print Excel formulas? A Google search didn't turn anything up. I've got a few worksheets of semi-complex formulas to slog through, so this would make my life a bit easier. I'm just looking to turn something like this AC6+AD6+(IF(H6="Yes",1,IF(J6="Yes",1,0)))+IF(X6="Yes",1,0) into somethin...