Category Archives: Excel

Changing the series in excel vba

Questions: I have a chart on one of my sheets and I need to change a series in it in code. The problem is that I keep getting the error 1004 message. I’ve looked around and can’t find a reason for it. Here’s the code: Sheets(“Charts”).ChartObjects(1).Chart.SeriesCollection(1).Formula = “=G49:I” & dblResult & “)” Answers: Are you… Read More »

Excel VBA Complex String Replace

Questions: Is there a way to find a sub-string in a string then replace that sub-string and a number of characters before and after it? For example, I have these: Mandatory659852 y n n TRY RACE Mechanica888654 y n n FRY SAME GILLETE685254 y n n CRY FACE Can I program VBA to remove the… Read More »

Macro to Auto Fill Down to last adjacent cell

Questions: I am trying to use macro recorder in Excel to record a macro to fill down a column of cells, however because the fill down each time is a different number of cells it either fills down to short or too long and this seems to be because the macro identifies the cell range… Read More »

Choosing an excel java api [closed]

Questions: All I need to do is open an MS excel sheet – not sure which kind yet (2003, 2007, etc.) – and parse the information in each row into an object. I’m performing only readonly operations. out of Apache POI, JExcelAPI, or OpenXLS which is best for this task? I’d rather not find out… Read More »

To find the absolute difference have negative and positve values

Questions: I have 2 columns with one positive numbers and the other negative values. Sometimes one column has positive numbers and other negative. I need to find the absolute difference between the two columns. What I am doing is, firstly I edit both the columns and make all the values positive and then subtract first… Read More »

How do we make the header(s) shown always at the top of the sheet

Questions: Filter data option in excel is useful to filter data based on a certain criteria. How do we make the header(s) shown at the top always as we scroll down the excel sheet. Answers: Freeze the row and column Instructions at : http://office.microsoft.com/en-us/excel-help/freeze-or-lock-rows-and-columns-HP001217048.aspx Questions: Answers: View tab Freeze Panes – Freeze Panes /* everything… Read More »

What is the best way to package and distribute an Excel application

Questions: I’ve writen an Excel-based, database reporting tool. Currentely, all the VBA code is associated with a single XLS file. The user generates the report by clicking a button on the toolbar. Unfortunately, unless the user has saved the file under another file name, all the reported data gets wiped-out. When I have created similar… Read More »

perl module for writing excel2007 workbook

Questions: I have a huge report coming out of a tool from which i extract the some important data and write a excel file. Till now i used the module Spreadsheet::WriteExcel, but it crashed when the number of rows exceed 65535. Is there any other module which supports generating excel for huge data? I checked… Read More »