ODBC Connection to Flat file
What is the best way to take a database and make it a flat file? I am have an ODBC driver and need to pull the data out into a file file. Excel, Access? OpenOffice? ...
excel
Excel WPF Addin Drag Drop Error
I created a Sidebar in Excel using WPF, and trying to drag some some objects from the WPF Pane to the spreadsheet. The Application object in WPF is null. Please guide me how to Achieve this Drag-Drop simple text from WPF Excel Addin to The spreadsheet. Thanks in Advance ...
How do I filter dates by a cell formula in an Excel pivot table?
I am attempting to make some reports in Excel using a pivot table of a Sql server view. One of the customer requirements is to be able to filter the reports based on a date range they select, which seems reasonable. The end user process needs to be fairly simple, so my preference is to have a cell for begin date and one for end date and ...
Is there a library or example for creating Excel (.xlsx) files?
I'm writing a Cocoa application and I'm trying to export to the Excel XML format (ISO/IEC 29500-1) which is basically a zip file with a bunch of xml files in it named with a .xlsx extension. I've tried generating apple script and using NSAppleScript to create the Excel file, which works, but is slow and the user has to have Excel instal...
Getting questionmark characters (?) when reading file line by line into worksheet
I am reading a text file line by line with VBA into a worksheet. it's often returning these characters. Does anyone know how to remove them? this is how i am reading it: Sub ReadAsciiFile() Dim sFileName As String Dim iFileNum As Integer Dim sBuf As String Dim i As Integer i = 1 ' edit this: sFileName = ...
How to delete values of a range in Excel VBA
Hello, I have a range in excel named " ValuesRange" located at Cell "B5". I have four values: 3,4,5,6 located at cells B6,B7,B8,B9 respectively. Given that B10 is always blank. how can delete the four values one by one? Here is the code that I used: Dim startRange as Range Set startRange= Range("ValuesRange").offset.(1,0) while not IsE...
Declaration of an array in Excel VBA
I have in a Excel field (created by someone) a formula containing an Array: =PERCENTILE(Scale1, 0.5). I need to expand this array to other fields. Usually an Array is a range like A1:C52, so expanding it means modifying the formula to, let's say A1:C152. But this "Scale1", even if I can see its border in a blue color, I can't modify i...
C# Excel 2007 late binding
I have this example from Microsoft which is working perfectly with Excel 2003. How can I do the same with 2007? Please look in below the title "Create an Automation client that uses late binding" http://support.microsoft.com/kb/302902 ...
Excel Automation: Protect single worksheet from deletion by user.
VSTO 4.0 / Office 2007 In an Excel document-level automation project, I have a worksheet that must not be deleted from the workbook. I am afraid that a careless user might delete it by accident, which currently causes a lot of grief (exceptions galore). I can not protect the entire workbook, because the user must be able to create, del...
How to import datas to excel sheet
I have datatable . I need to import those datatable values to Excel Template.How to achieve this ...
Pivot table conditional formatting (cell-coloring).
I want to color the cells of a pivot table in a way that it highlights the highest values in the row. The table I have is something like this: ________|_Field1_|_Field2_| ________|_X_______________|_X_Total_|_Y________________________|_Y_Total_| _Field3_|____A___|____B___| |____A___|____C___|____D___| | ____1___|____3___...
How to insert macro into a excel file without using the excel com library
Is there any way to insert an existing macro into a existing excel file without using the excel library ? I need this to set the excel to print whole work book .I cannot use excel library because it will be done online in the server where installation of excel is not possible. I can however use the c#.net coding . I am using NPOI to gene...
External table is not in the expected format in excel through C#
I trying to copy the one excel sheet to another excel sheet. I have a excel file and in it there is a sheet. Now I want to create a new excel file which should have the another sheet with the data which is in the earlier sheet as well as some updated data also. For this I am reading the whole data from first excel sheet and storing in s...
Import Excel outline (drill-down) structured data
Is it possible to import outlined data from Excel? This is where Excel shows drill-down [+] [-]. Reading from ODBC doesn't give parent/child info; neither does exporting to CSV; export to HTML gives this info via mso-outline-level styles, but 2007 version puts actual content into subfolder and users will be VERY confused trying to sel...
How to force quotes in every field while saving an excel worksheet as a csv file?
Here is my situation. data = "" & data & "" xlsheet.Range(crange).Value = data xlfile.SaveAs(FileName:=finalfilename, FileFormat:=6) This code puts " around the data, puts the data in the range and saves the workbook(xlfile) with one worksheet(xlsheet) as a csv file. If I open the csv file in notepad I find these following 1.quotes ...
Data gathering and manipulation: is there something better than Excel?
I work with people who have historically used Excel and e-mail to 'gather' data from their external contacts. The cells these contacts populate are linked to complicated equations (occasionally macros), or are part of a large cascading cell relationship. All the data we gather produces multiple outcomes, but all of it requires additio...
How do I write a function that takes a range or list of arguments in Excel VBA?
With SUM for instance, I can do things like: =SUM(F5:F7,F6:F8,A1,E7:G7,F7,2,7) Which takes multiple ranges and/or individual cells and it adds them all up. My question is how do I do this with a VBA function, e.g. to generalize a binary XOR: Function BXOR(A As Integer, B As Integer) As Integer BXOR = CLng(A) Xor CLng(B) End Func...
Weird occasional InvalidCastException in Excel interop
I am developing a fairly comprehensive Excel add-in, applying a lot of ops. Take a workbook, save under new name, apply actions (add names, change formulae, etc). Generally it works fine, but from time to time .Net throws an InvalidCastException on virtually any of my CTypes. When I acknowledge the error and run the code again (without ...
Differentiate excel file types based on bytes, not extension
I currently have a method that receives a byte array of an excel file. It worked great, and I had it differentiating the file types (.xls, and .xlsx) based on the first two bytes in the file. If it's an .xlsx it started with a PK. Unfortunately, now I am getting .xlsm files sent, and I don't know what byte differs to tell them apart. ...
Excel RTD (Real Time Data) client other than Excel?
Hi everyone, I have been looking all over, and couldn't find any example for an RTD CLIENT (many RTD server samples, though). My goal is to 'pull' data from an RTD server into my application for algo-trading purposes. If possible, without using C# / .Net, as I am looking for a lightweight, deploy-able solution. Can you give me any ti...