excel

Keep absolute reference even when inserting rows in Excel 2007

I have a spreadsheet where I want cell formula to always look at a specific cell, even if rows are inserted and the specific cell moves. Effectively, I always want to look at the 'top' cell of a table, even if new rows are inserted at the top of the table. eg. Cell A2 has the formula[=$E$2] Now I highlight row E and do Insert Row. The ...

How to specify data types when draging content to Excel

I am initiating drag and drop from my WinForms application using this simple IDataObject data = new DataObject(); string textToExcel = "Hello\tWorld\t1\t2\nHello\tWorld\t1\t2\n" data.SetData(DataFormats.Text, textToExcel); I works fine when dropped on Excel, it ends up nicely in columns and rows. Problem is that Excel does not know th...

VBA: How to display a cell value by its defined name?

I have already a defined name for a particular column in my worksheet. How to display a cell value by its defined name? I've tried these: Public Sub Test() Dim R As Range Set R = ThisWorkbook.Names("SomeName").RefersToRange MsgBox CStr(R.Value) End Sub but run-time error occured "Type Mismatch" (error code: 13). What...

Remove images when exporting to Excel from webpage

I'm using a jsp page to export a set of tables to Excel. All the data exports fine and I can use css to format everything, but when i try to hide the images that export, they will not do what I want (i want them to be gone). ...

Excel VBA or Function to extract Workbook name and data from workbook.

Is there any way to extract the workbook name, but then extract only a part of it. Any version of excel would be fine preferably 2003. For example "Help_TicketID123456788.xls" "Help_TicketID563565464.xls" ... So then I'd like to extract the ID numbers and put them into a column on a master worksheet in another workbook. Additi...

What is the best way to create an Excel file with coldfusion?

Hi, i want to create an excel file with coldfustion. since today, i was saving a file AS an html AND changing the extension. i need to create now a real excel file. some advise? thanks ...

How to save a copy of an inactive worksheet - Excel 2003 vba

We have a piece of software that allows the user to run a report. The template for this report is an Excel workbook that exists on a network drive. The steps are: The user clicks "Generate Report" The software opens the template, populates the data, and closes the template, saving it in a specific directory. This is done "silently", ...

How can I extract a specific table and copy from MS Word to Excel in Perl?

I have an MS Word 2003 file which contains several tables in it and I want to extract a specific table contents. For example, tables will be coming under some sections and I want to extract the contents of the table that are coming under section 6 alone and no other table contents, I want copy those contents to an new Excel sheet with fo...

Importing/Pasting Excel data with changing fields into SQL table

I have a table called Animals. I pull data from this table to populate another system. I get Excel data with lists of animals that need to go in the Animals table. The Excel data will also have other identifiers, like Breed, Color, Age, Favorite Toy, Veterinarian, etc. These identifiers will change with each new excel file. Some may...

Excel summary of many excel files data into one report excel

Hi I have a number of excel files containing filled survery, now I would like to have one master document that would have summary result of each. Thus I imaging to have for each file a row input: name - address - some data... I would like to open each of the files, and copy the data from selected cells into my master file. I have ...

Excel: Concatenate/retrieve cells that are across a merged key cell

Hello, Assume a simple sheet like so: ------------- | 1 | a | Need formula to return: "a,b,c" | |-----| | | b | | |-----| | | c | ------------- | 2 | a | Need formula to return: "a,b" | |-----| | | b | ------------- The first column is the merged key cells and the second column has an unknown ...

How to automatically copy text out of excel files

I have 250 Microsoft Excel (.xls) files, all in a folder. I need to do the following: for each file: open the file switch to a specific tab in the file extract the text from rows 15-100 on that tab save the text in a text file somewhere I assume this can be automated somehow, but I have no idea how. Where do I start lo...

Flex/AIR: Export DataGrid to Excel with multiple sheets

Hello, I have an AIR application with two DataGrids that I would like to export to Excel. I've found the as3xls library, but it can only handle one sheet (as per the comments). Ideally, I'd like to export both DataGrids into separate sheets in the same workbook. The AIR application is running entirely on the user's desktop and doesn't...

Creating workbooks with C# and Excel 2007 interop

i'm curently using Excel 2003 to generate reports, but i'm thinking of migrating to Excel 2007. The problem is that some of my users still have Excel 2003 installed on their PCs, so I'll give them the posibility to choose what to generate - Excel 2007 or Excel 2003 compatible report. So my question is : can Excel 2007 Interop save work...

How to get chart data range programmatically via SpreadSheetGear

The requirement is to get the chart data range programmatically via SpreadSheetGear. It seems there is no API to return chart data range directly in SpreadSheetGear, the only way is to calculate the range. What I am doing now is to go through all the Series in the chart, get the starting/ending row/column and compose them to range object...

PHPExcel Export for Larger Files fails

Hi All, I am using PHPExcel library to generate excel data based on mysql datbase. MySql query results in the 1,34,000 rows. And Excel Supports 65,536 Rows on one worksheet. So made logic like foreach($result as $value) { $val = array_values($value); if($rowscounter < 65000) { $objPHPExcel->addRow($val,$rowscount...

Problems with Excel export to Word using VBA

Hi, I'm having problems exporting data from Excel to Word. In the Excel sheet, there is a command button which first sorts the data according to date (this works). Then, the content of these columns (which are declared as variables after the sorting is done) should be exported to a Word document. Opening a word file works, and the firs...

MailMerge.OpenDataSource does not work with network share?

Hello everyone, i do some word mailmerging.. MailMerge.OpenDataSource( _ Name:=m_strDsName, _ setting m_strDsName... if i set a local excel document for datasource: m_strDsName = 'c://documents/somefolder/someexcelfile.xls' everything works just fine if i set the excel document for datasource from network sha...

select data from excel spreadsheet that's read only

I've got a dtsx package that selects data from an excel spreadsheet on the network and inserts it into a sql server table twice a day. However, the process fails if someone is in the spreadsheet modifying data. Is there a way to select data from an excel spreadsheet so that it doesn't fail if someone is in the spreadsheet? ...

Excel Custom Data Format

I'm using NPOI to generate XLS spreadsheets. NPOI is an Excel Spreadsheet generation library/API that is available on codeplex, enables you to create workbooks, formatting, formulae and so on and so forth....I use it to create workbooks with multiple sheets that contain the output of the various calculations. I've used the following cus...