Category Archives: Excel

How do I show a running clock in Excel?

Questions: I’d like to show a clock in cell A1 of Excel 2007. I’m familiar with NOW() and TODAY() but it doesn’t refresh itself every 1 minute like I want it to. You know, like a running clock. I just want the current time in h:mm to be in cell A1. Is this possible? From… Read More »

VB approach to this excel function

Questions: This is the function and it works without any problem Substitute all the occurence of char 160 in a cell with a null “”. Trim any empty spaces. Concatenate the arrived number with char 160 again in the front. act is the name of the referenced worksheet. CAN anyone give a VB equivalent of… Read More »

Using OpenXML SDK to Grab all Cell Values as a String

Questions: I’m wanting to read an Excel 2007+ document using the OpenXML SDK v2.0 in an SAX-like manner. I am using this blog post as a rough guide: http://blogs.msdn.com/b/brian_jones/archive/2010/05/27/parsing-and-reading-large-excel-files-with-the-open-xml-sdk.aspx However, in my document I have a mix of strings and numeric values. Therefore, the string values are being stored as a SharedString, so when the… Read More »

Issue with string separation in VBA

Questions: I would appreciate your help with the following please. I need to separate some data in VBA so it can be set as separate variables to be used in a file naming system. I have the following code: Sub StripSlash1() Dim strVal As String, strVal2 As String, strVal3 As String, strVal4 As String ‘strVal… Read More »

Excel sumif with criteria

Questions: I have a last_name, first_name, salary, and total_salary columns. The sales person appears multiple times throughout the spread sheet. What I would like to do is write a function that will sum up the salary column in the total_salary column only where the sales person appears. Example output: Desired output: Thanks Answers: Try this… Read More »

C# how to iterate over excel columns

Questions: I want to get a specific column of an excel sheet and then iterate through it’s cells. I want it to look something like this: Excel.Workbook workbook = app.Workbooks.Open(svDropPath); Excel.Worksheet xlWorkSheet = (Excel.Worksheet)workbook.Sheets[“Sheet Name”]; var col = xlWorkSheet.UsedRange.Columns[“C:C”, Type.Missing]; // I want the 3rd column foreach(Cell c in col) …. How do I actually… Read More »

Reference a named Excel variable using VBA

Questions: Situation: I’m using Excel 10. I have a named variable that uses a formula to compute it’s value. This is a named variable, not a named range (in the ‘Name Manager’ the name “MaxDate” refers to “=MAX(Sheet1!B:B)” where column B is a list of dates). The computed value does not appear in any cells… Read More »

Django: export object from database to excel

Questions: i have this in my template : <table> <tr><td>book_id</td><td>book name</td><td>book_author</td></tr> {% for book in books %} <tr><td>{{ book.book_id }}</td><td>{{ book.book_name }}</td><td>{{ book.book_author }}</td></tr> {% endfor %} </table> <a href=”/export”>Export to Excel !</a> my view seems to be like this: def export_excel(request): books = Book.objects.all() response = HttpResponse(books , content_type=’application/vnd.ms-excel;charset=utf-8′) response[‘Content-Disposition’] = ‘attachment; filename=”books.xls”‘ return… Read More »

Insert New Column in Table Excel VBA

Questions: I’m running into some issues on a worksheet that I’m building. I want to insert a column at a specific location in a table and then set the header. I searched around for a while, and found (some version of) the following code: ActiveSheet.Columns(2).Insert This works to insert the column, but I’m not sure… Read More »

Excel VBA Textbox font size

Questions: I have a userform to display my terms and conditions which is picked up directly from a cell. Upon Activation I call TandC.Text = Worksheets(“Master”).Range(“L41”) which works perfectly when I run the form directly — correct font size, multiple lines, word wrap, etc. However, when I run the form from another userform, the text… Read More »