Hi, say if I see this Vlookup formula in my Excel spreadsheet: =VLOOKUP(G37,'tb 2008'!$H$142:$M$247,6,FALSE) Then is there an efficient way to find out which cell it is referencing? I don't want to manually perform the Vlookup in my head to find out which cell it found that fits the parameters. ...
I''m looking for a vba macro that will make a copy of the current Excel 2007 macro-enabled workbook with the name I specify as an Excel 2003 macro-free document. It should also keep the current workbook open and in the Excel 2007 format (so save-as wouldn't work). I can't figure out how to do the copy operation... ...
Hey all I have a .xll plugin that registered a few functions which we use in our excel sheets in a number of places. The performance was very poor, and we didn't have the original code, so we replicated the functionality by writing the functions in VB in a module. The functions work as expected and performance is much better. The probl...
I am using WatiN for automation testing. I need to save few values for future reference in my application. I need to know whether Excel is a good approach for storage or I rather use XML for this purpose. Kind regards ...
I am trying to embed a multi-page PDF document into an Excel file such that when the Excel file is printed, all of the PDF's pages are printed also. I have googled around quite a bit and haven't found a way to do it. It seems that OLE embedded multi-page files are limited to displaying their first page. Can anybody suggest a way to do...
Simply put I am looking for a quick way to do the following. I would like to format an excel spreadsheet below: A B C D 1 foo bat abe test 2 foo bat cat test 3 foo bat dog test 4 bar moo hat girl 5 bar moo bat girl to look like this: A B C ...
What's the best/smoothest solution to import an Excel-file to SQL Server, on a 64 bit Windows system? What I'm specifically after is to import an Excel 97-2003 file, to a SQL 2005 database, from an ASP.NET/C# page. The import data will demand users to use a "template", so the data looks the same every time. Previously the system has us...
I'm trying to update document properties and create new entries if they don't exist However this type of thing does not work Set objDocProps = DSO.GetDocumentProperties(sfilename:=FileName) With objDocProps If .CustomProperties("ABC") Is Nothing Then 'create it here and if I put an error handler in there it barfs as either being loc...
Is there a smart macro in Excel (2000 upwards) to remove the last part of a string, if it has a certain separator? If the separator is not there, the string should be preserved For instance, for "." abcd.1 => abcd abcd => abcd I guess I could write something using a combination of Instr and Mid and such, but ...
I am writing an background service application that has to automatically read data from Excel 2003 files. But no matter what I try, the method OlePropertyGet() always results in an EAccessViolation error while trying to read from address "00000800". The error always occurs at the last line of this code snippet, and seems independent of ...
So I have a document with many entries that follow this general format. Organization Name Title Address Phone Fax Description line 1 Description line 2 Organization's website. Organization Name Title Address Phone Fax Description line 1 Description line 2 Organization's website. Organization Name Title Address Phone Fax Descrip...
Basic question: How do I load an Excel template for use with POI and then save it to an XLS file? Edit: The answer is: FileInputStream inputStream = new FileInputStream(new File(templateFile)); Workbook workbook = new HSSFWorkbook(inputStream); (Just load the template as a workbook and then write the workbook as an XLS file elsewher...
I have some Excel file reading code that uses the OLEDB (Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;) which works well but I keep encountering an issue whereby certain dates are returned as DBNull. In the original XLS document, the format of dates that work (en-GB locale) are: "02/04/2009 17:00:00" ...
[Sample.xlsx] Column 0, Row 0 = "ItemA" Column 0, Row 1 = "ItemB" Column 0, Row 2 = "ItemC" Column 0, Row 3 = "ItemD" [Application] DataSet dsData = new DataSet(); string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Sample.xlsx;Extended Properties='Excel 12.0;'"; OleDbDataAdapter daGetExcel = new OleDbDataAdapter("SE...
Group I am pasting data from SQL to Excel and it appears that Excel is trying to read my mind by converting some of my numbers to dates. I.e. one number is 2-2131 and when I paste it to excel it shows Feb-31 even 2-3291 wnats to be Feb-61 STOP IT EXCEL! This is driving me crazy and I can't seem to find an easy way around this. I trie...
Hello. I am creating a small winforms application for distribution to a few people. I would like the users to be able to export the contents of a DataGridView to a file that Excel can read. I would like the file to be a excel file and not csv or xml. In the past when I have created an "export to excel" function I have always had to incl...
My application scan through the excel files and generate a report having count of Activex controls in each excel file. But while running the tool from client machine we are getting an error on opening the file (for some file not all) and the files are located in the sever side, as follows: "the rpc server is unavailable (exception from ...
I am trying to write a VBA macro to track changes to a workbook in a separate sheet. If you do this manually, the sequence of commands is Tools > Track Changes > Highlight Changes, taking the option Separate Worksheet. You have to do two iterations of the command, one to activate tracking inline, a second to move the tracking to a separ...
In a program i'm currently working on i've created a user defined type to contain some data that i'll later use to populate my form. i'm using an array of that user defined type and as i pull more data from a offsite server i'm resizing the array. So in order to make my program easier to digest i've starting splitting it into subroutin...
How can I efficiently import CSV data with Apache POI? If I have a very large CSV file that I would like to store in my Excel spreadsheet, then I don't imagine that going cell-by-cell is the best way to import...? ...