Tag Archives: excel

How can I open an Excel file without locking it?

Questions: I have a process that builds an Excel report, then opens it for the user. The problem is if someone leaves the file open, it stays locked and nobody else can build the report until the first person exits the excel file. Is there a way to open an Excel file without locking it,… Read More »

VBA: cannot automatically recalculate Excel formula after updating it — needs manual interaction

Questions: In VBA, I am updating the formula in a cell (which works ok), but automatic recalculation does not work: updated_formula = “=COUNT(Sheet1!A3:A” & nr_points & “)” Cells(x, y).Formula = updated_formula ActiveWorkbook.Save Cells(x, y).Calculate The formula simply counts the number of existing rows in another sheet. When I run the macro, the cell’s value in… Read More »

Excel Vba – Using an apostrophe in an sql string

Questions: I’m using excel to input data into an Access database and some of my data strings contain an apostrophe for measurements. This is my SQL input string stSQL = “INSERT INTO Products (ProductName, ProductDescription, ProductUnit, SupplierID) ” & _ “Values (‘” & cboxItemNum & “‘, ‘” & txtDescription & “‘, ‘” & txtUnit &… Read More »

Format cells in excel for decimal places using a macro

Questions: How can I format Cell (D:) 2 decimals places Cell (E:) 1 decimal place and Cell (H:)3 decimals places in excel using a macro? Answers: the numberformat property of a range will format it to any custom format sheets(“sheet1”).range(“D:D”).numberformat = “0.00” sheets(“sheet1”).range(“E:E”).numberformat = “0.0” sheets(“sheet1”).range(“H:H”).numberformat = “0.000” for numbers with a comma or currency… Read More »

Get column number by cell value in Excel

Questions: I’m trying to get the column number of a cell in the first row with a specific value. The code I’m using isn’t working for some reason. Dim colNum As Integer ‘sheetName is a String With ActiveWorkbook.Sheets(sheetName) colNum = column(.Match(“ID”, 1:1, 0)) It’s telling me it’s expecting a “list separator or )”. How do… Read More »

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 »

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 »