So, to preface, I'm a complete novice at this Excel business. I've found similar examples via Google of how to do this, so I don't think I'm too far off: =IF( AND(D6=FALSE, OR(ISBLANK(B10),B10=0) ),IF( AND(D6=TRUE,B10>=1)," ","Enter number of components"),"fail") Essentially, the first IF block evaluates the contents of the AND expre...
I have a set of Pivot tables/charts automatically created from VBA code with custom formatting. When I change the selection of data to be displayed from the drop down list in the chart, the custom formatting reverts back to the chart type's default display. The underlying chart data is not changed, only what's displayed on the chart. W...
Hello, i need to write the data from an excel sheet/workbook to the text file though C#. also, i want all excel worksheet data in single text file. Apart from reading the data from excel sheets one by one and then appending it to the existing text file is there any other simple way to do it? Can anyone help me with this? Thanks in a...
I am connecting to the excel file using ADO.NET OLeDB drivers. In the connection string I have HDR=NO ( which means my excel file does not have header) I want to run update statement and refer to column as they are in excel file. The below sql query throws the following error : No value given for one or more required parameters. update...
OK, I am finishing up an add-on project for a legacy Excel-VBA application, and I have once again run up against the conundrum of the mysterious range.Rows(?) and worksheet.Rows properties. Does anyone know what these properties really do and what they are supposed to provide to me? (note: all of this probably applies to the correspond...
Hello: I wrote the following code so that when an Excel spreadsheet is closed it will update its name with the current date and time: Private Sub Workbook_BeforeClose(Cancel As Boolean) If ThisWorkbook.Name = "Name_Last Opened-" & Format(Date, "MM-DD-YYYY") & _ "_" & Format(Time, "HH.MM") & ".xls" Then Else ThisW...
I'm collecting some data via a Perl script. The data needs to be reviewed and processed by others using Excel. Currently, I'm writing the data out as a tab-delimited text file, and Excel can open this just fine. There's a hierarchy to the data, however, and it would be easier for the reviewers to see a tree rather than a flat list. ...
Long story short, I'm taking a bunch of excel documents one by one, and importing them using the Import/Export wizard into a database in SQL Server 2005. Here's one report (all processes not shown are a "Success"). Is there any way for me to ignore truncation errors? I've googled around to no avail, or at least not in my version. - E...
I have an Excel report with a bunch of sheets, each with a table of data. I need to make a button that will create one html page containing the tables, one after the other. I don't know much about html, so my first approach was to have a macro that creates another worksheet with the tables on it, and then saves that sheet as html using...
How to add item to right-click menu in Excel chart using VBA? Excel is 2007. Chart is standalone sheet. ...
I'm attempting Excel automation through C#. I have followed all the instructions from Microsoft on how to go about this, but I'm still struggling to discard the final reference(s) to Excel for it to close and to enable the GC to collect it. A code sample follows. When I comment out the code block that contains lines similar to: Sheet.C...
In a spreadsheet I'm using, some of the possibilities for a cell is being blank, having the number zero, or having a non-zero number, or having a string. If I want to test if the cell is zero, but is not blank, I currently do =IF(AND(B2=0,LEN(B2)>0),foo,bar) Is there a way of replacing the two conditionals with one conditional? ...
With my Tool we are scanning excel files and generating reports. My problem is some excel sheets are password protected and getting pup-up while scanning and it halts the Tool to run further. Could someone let me know the solution, how to overcome with this problem. I just need to skip the sheet and continue with scanning without any us...
I was using this routine, triggered by the Worksheet_Change event, to display a selected chart and move all other charts to a holding position off screen. Sub shuffleCharts(chartName As String) Dim analysisChart As ChartObject For Each analysisChart In ActiveSheet.ChartObjects With analysisChart .Left = 1575...
With PHPExcel 1.6.7, i tried to clone a excel file, insert a word in a cell of sheet1 of new file and then save it : $file = "file.xls"; $fp = fopen($file, "r"); $objReader = new PHPExcel_Reader_Excel5(); $xls = $objReader->load($file); //$xls->setActiveSheetIndex(0) ; $xls->getSheetByName('sheet1')->setCellValue('A2', 'bbb') ; $newx...
Hi, I am trying to import a column of dates from a spreadsheet in Excel 2003 into SQL Server 2005 using SSIS. I am in the UK so want dates formatted as dd/MM/yyyy. Unfortunately, the column in the spreadsheet contains a mixture of dates stored as strings in dd/MM/yyyy (with Excel 'General' formatting) as well as dates using Excel 'Dat...
Hi, I'm sure this is the kind of problem other have solved many times before. A group of people are going to do measurements (Home energy usage to be exact). All of them will do that at different times and in different intervals. So what I'll get from each person is a set of {date, value} pairs where there are dates missing in the set...
Hi, Anyone know of a fully managed .NET library that can read and write Excel 2007 binary files (.xlsb)? OLE automation is not an option (must work without having Excel installed). Thanks, Marcus ...
Consider the following data on excel(which you have to create in excel): Numbers 0.2 0.2 3.4 1.5 1.8 4.2 Write a VBA function that scans through the selection, finds the empty spots and then t fills them with a numerical value which is passed through the signature of the function. Output: Numbers 0.2 0.2 3.4 0 1.5 1.8 0.0 4.2 Hi ...
I'm working on some code to export a DataTable to Excel, and I'm trying to make it as fast as possible. Right now, I'm looping through each row and each column for each row (nested for loops). When I've got a large DataTable, this process can take some time. Can I assign a range of cells to an array in Javascript instead of looping throu...