Questions tagged [excel]
Only for questions on programming against Excel objects or files, or formula development. You may combine the Excel tag with VBA, VSTO, C#, VB.NET, PowerShell, OLE automation, and other programming related tags and questions if applicable. Do NOT use with other spreadsheet software like [google-sheets].
excel
23,227
questions
643
votes
15
answers
402k
views
How to avoid using Select in Excel VBA
I've heard much about the understandable abhorrence of using .Select in Excel VBA, but I am unsure of how to avoid using it. I am finding that my code would be more re-usable if I were able to use ...
202
votes
14
answers
198k
views
Find last used cell in Excel VBA
When I want to find the last used cell value, I use:
Dim LastRow As Long
LastRow = Range("E4:E48").End(xlDown).Row
Debug.Print LastRow
I'm getting the wrong output when I put a single ...
756
votes
9
answers
1.3m
views
How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops
How can I use regular expressions in Excel and take advantage of Excel's powerful grid-like setup for data manipulation?
In-cell function to return a matched pattern or replaced value in a string.
...
43
votes
3
answers
78k
views
Excel VBA, getting range from an inactive sheet
This script works fine when I'm viewing the "Temp" sheet. But when I'm in another sheet then the copy command fails. It gives an Application-defined or object-defined error:
Sheets("...
807
votes
43
answers
351k
views
How do I properly clean up Excel interop objects?
I'm using the Excel interop in C# (ApplicationClass) and have placed the following code in my finally clause:
while (System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheet) != 0) { }
...
2174
votes
48
answers
1.4m
views
How do I create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office?
How can I create an Excel spreadsheet with C# without requiring Excel to be installed on the machine that's running the code?
46
votes
3
answers
41k
views
Why MS Excel crashes and closes during Worksheet_Change Sub procedure?
I am having a problem with Excel crashing, when I run VBA code on an excel sheet.
I'm trying to add the following formula on worksheet change:
Private Sub Worksheet_Change(ByVal Target As Range)
...
38
votes
2
answers
19k
views
Excel - Extract substring(s) from string using FILTERXML
Background
Lately I've been trying to get more familiar with the concept of changing a delimited string into an XML to parse with Excel's FILTERXML and retrieve those substrings that are of interest. ...
158
votes
5
answers
638k
views
How do I put double quotes in a string in vba?
I want to insert an if statement in a cell through vba which includes double quotes.
Here is my code:
Worksheets("Sheet1").Range("A1").Value = "=IF(Sheet1!B1=0,"",Sheet1!B1)"
Due to double quotes I ...
274
votes
7
answers
803k
views
Loop through files in a folder using VBA?
I would like to loop through the files of a directory using vba in Excel 2010.
In the loop, I will need:
the filename, and
the date at which the file was formatted.
I have coded the following ...
40
votes
6
answers
96k
views
Convert matrix to 3-column table ('reverse pivot', 'unpivot', 'flatten', 'normalize')
I need to convert the Excel matrix FIRST in the table LATER:
FIRST:
P1 P2 P3 P4
F1 X
F2 X X
F3 X X
F4 X X
LATER:
F P VALUE
...
232
votes
32
answers
539k
views
Reading Excel files from C#
Is there a free or open source library to read Excel files (.xls) directly from a C# program?
It does not need to be too fancy, just to select a worksheet and read the data as strings. So far, I've ...
46
votes
2
answers
21k
views
Clean up Excel Interop Objects with IDisposable
In my company the common way to release Excel Interop Objects is to use IDisposable the following way:
Public Sub Dispose() Implements IDisposable.Dispose
If Not bolDisposed Then
Finalize(...
11
votes
2
answers
6k
views
Vlookup using 2 columns to reference another
I am trying to do a vlookup under a circumstance of first then last name to get an age. This will be done within Column A, then Column B. If found in Column A, Continue to Column B, If found in Column ...
1324
votes
10
answers
972k
views
What is a correct MIME type for .docx, .pptx, etc.?
For older *.doc documents, this was enough:
header("Content-Type: application/msword");
What MIME type should I use for new .docx documents? Also, for pptx and xlsx documents?