excel

SSRS: Error while querying data from an Excel file (through ODBC)

I am trying to render a simple chart using SSRS. The data is in an Excel sheet. I have set up an user DSN and created a data source in SSRS using ODBC. I am able to query the excel in "Data" tab. However while trying to preview, I get the following error: error [hy000] [microsoft] [odbc excel driver] the connection for viewing your lin...

Excel VBA: Is there any way to format chart axes through VBA only?

Hi, I have to make column charts in Excel using VBA only (no user input). I wanted to format the labels of the x-axis so that the alignment for every label becomes -270 degrees. (This can be done manually by changing the "Custom angle" property in the "Alignment" tab of the "Format Axis" Dialog.) I have tried recording a macro for this ...

Excel VBA: Which OLE/OCX control to use to display a chart in a form?

Hi, I have to display a column chart in a user form in VBA. Exporting the chart to GIF and importing in a picture box works; but I cannot use it because the chart needs to be modified based on options chosen by the user from the same form. I have been searching and people have commented that default OLE/OCX controls provided by MS may b...

Is there a control which emulates Excel functionality but is not Excel?

I'm creating a VB.net application with VS2005 which, at the moment, uses the DataGridView to emulate Excel style functionality on a very basic level. Before I go crazy with creating custom methods for the DataGridView to do some more advanced things, does anyone have a suggestion for an alternative? I plan on installing my app on machine...

How should I import highly formatted data from Excel to a database?

What is the best way to import highly formatted data from Excel to SQL server. Basically I have 250+ Excel files that have been exported from a reporting tool in a format that our business users would prefer. This is a 3rd party tool that can not export data in any other format. I need to "scrub" these files on a monthly basis and impor...

How to copy data from sheet1 to sheet2 with a condition in Excel

In excel workbook I am trying to copy data from sheet1 to sheet2 with a condition, IF((Sheet1!B2:B80=Sheet2!A2:A80),Sheet2!D2) i am not getting the result.please help me solve this. I am trying to copy from sheet1 to sheet2 with a condition if the the value in sheet 1,column B is equal to the value in sheet2 column A the value in colum...

Prompt user when cell is changed

Hello Everyone, I'm very new to creating macros and programming in general. I have a worksheet of 38 tabs. 31 tabs are for the days of the month. I would like to create a macro that will prompt users with a warning message any time "MCO" is selected in column N for each of these 31 tabs. Is that possible? Thanks ...

Help with a simple formula for a novice

I am very new to Excel and need some help. all I need to do is to use an ID in the following hyperlink: http://www.wotnots.co.uk/property/images/property2402_image1.jpg The ID here is 2402. I want to change it to the ID number which is in the first column and is different on every row. I want to have the ID number to be the same as the n...

Loop through PageField in OLAP Cube [PivotTable]

Hi, I'm trying to write a VBA script that will draw buttons beside the PageFields in a Pivot Table, these buttons will loop through the values in the PageField. I had this working for a regular Pivot Table, but I've been asked to adapt it for an OLAP Cube (External Data Source) and I can't work out how to find the values for a Member usi...

To upload an excel file as a clob

Could some one give an idea about how to convert an excel file into a CLOB in oracle through JDBC. I would like to know how to covert excel file into a String using API available as part of JDK and the conversion from string to clob should be straight forward. Thanks in advance. If a similar question was already raised, kindly provide me...

How to stop the leading 0's from being stripped off when exporting to excel from a datatable?

I am running into the same problem as in this question: http://stackoverflow.com/questions/22879/how-do-you-prevent-leading-zeros-from-being-stripped-when-importing-an-excel-doc But I am not sure if that is the best solution for my scenario. Here is the code I am using to do the export. Does anyone know what I can change to prevent t...

Permission Denied opening an Excel File using Excel 12.0 Library & VB6

I have used Excel in my VB6 apps many times before, and have never run into such a weird problem trying to accomplish something very easy.. I am trying to open an excel (xls or xlsx) file and read through values, as you can probably see. When I try to open the file, I get an error 70 (permission denied) error. The odd thing is that the...

Want VBA in excel to read very large CSV and create output file of a small subset of the CSV

I have a csv file of 1.2 million records of text. The alphanumeric fields are wrapped in quotation marks, the date/time or numeric fields are not. For example "Fred","Smith",01/07/1967,2,"7, The High Street","Anytown","Anycounty","LS1 7AA" What I want do is write some VBA in Excel (more or less the only tool available to me that I am ...

VBA code for SUMIFS?

I'm trying to write a custom function that will let me retrieve a cell from the first row in a range that meets x number of criteria. I imagine this would be very similar to the way SUMIFS works, just simpler in that it doesn't continue processing after the first match. Does anyone know code to reproduce the SUMIFS (excel 07) function ...

ASP.NET output expense report in Excel

On this project I am working on right now, one of the newest feature requests is to output expense information which is stored into expense report that matches an Excel worksheet they already use for all of their expense reporting. I was curious if there was a way that I could take this excel worksheet (with all of its layout already d...

What's the best way to parse Excel file in Perl?

What's the simplest way to parse an Excel file in Perl? Converting it to a text file would also work. ...

Scientific notation when importing from Excel in .Net

Hi, I have a C#/.Net job that imports data from Excel and then processes it. Our client drops off the files and we process them (important because I don't have any control over the original file). I use the OleDb library to fill up a dataset (I hate writing that code. Seriously, is there any code that a .Net developer dreads writing m...

Having problems creating a custom lookup function in excel. Issue with match and concatenated ranges

Hi there, I'm having some trouble with a large spreadsheet of mine. I bring in a lot of raw data into a data sheet, and then do a number of lookups across the data. Using built in functions I've come up with =IF(ISNA(INDEX(Data!$L$7:$L$1100,MATCH(Data!$I$2&$B$199&$B29&Data!$J$5,Data!$K$7:$K$1100&Data!$J$7:$J$1100&Data!$I$7:$I$1100&Data!...

Dont let excel open the file if it is already opened

I'm using Microsoft.Office.Interop.Excel.Workbooks.Open Method http://msdn.microsoft.com/zh-tw/library/microsoft.office.interop.excel.workbooks.open(VS.80).aspx This method opens a dialog window if the file is already opened, asking the user if he wants to open as read-only or not open (the method returns null if user chose to not open...

I have ported an Excel financial calculator to javascript. Javascript runs much slower than Excel. Help!

I've ported an Excel retirement calculator into javascript. There are 35 worksheets in the original excel containing many recursive calculations, all of which I've converted to JS. JS is running slower (1-2 seconds compared to Excel's instantaneous). I am caching the resursive calculations already to speed things up and prevent stack ove...