Monthly Archives: February 2018

Convert .XLS to tab separated .TXT

Questions: Can I somehow convert Excel .XLS file to txt-tsv (tab-separated-values) file, using C#? Answers: You may read that XLS file easily via OleDb (ADO.NET provider) and create a StreamWriter object to write data into the Text/TSV file. using (OleDbConnection cn = new OleDbConnection()) { using (OleDbCommand cmd = new OleDbCommand()) { cn.ConnectionString = @”Provider=Microsoft.Jet.OLEDB.4.0;Data… Read More »

change worksheet name, a difficult one

Questions: When I use excel to open a .txt file (a notepad file), the worksheet name is the file name of the notepad file that was opened by default. Therefore, the sheet name will be different when open a different notepad file. Downstream code need this worksheet name be a fixed one. Is there anyway… Read More »

How can I transform rows into repeated column based data?

Questions: I’m trying to take a dataset that looks like this: And transform the records into this format: The resulting format would have two columns, one for the old column names and one column for the values. If there are 10,000 rows then there should be 10,000 groups of data in the new format. I’m… Read More »

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 »

ForEach loop object required error

Questions: Summary: I have a list of policy numbers I’m looping through in Column A using the for each loop Problem: Everything works except if there is an empty cell in Column A, my code deletes the entire row (as it should), but then when I try to set the policy variable I get a… 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 »

Calling UserForm_Initialize() in a Module

Questions: How can I call UserForm_Initialize() in a Module instead of the UserForm code object? Answers: From a module: UserFormName.UserForm_Initialize Just make sure that in your userform, you update the sub like so: Public Sub UserForm_Initialize() so it can be called from outside the form. Alternately, if the Userform hasn’t been loaded: UserFormName.Show will end… Read More »