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
288,125
questions
2173
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?
1322
votes
10
answers
970k
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?
- 82.8k
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) { }
...
- 16.9k
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.
...
- 31.2k
646
votes
36
answers
889k
views
Excel to CSV with UTF8 encoding [closed]
I have an Excel file that has some Spanish characters (tildes, etc.) that I need to convert to a CSV file to use as an import file. However, when I do Save As CSV it mangles the "special" Spanish ...
- 13.5k
643
votes
15
answers
401k
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 ...
- 7,194
638
votes
34
answers
694k
views
Is it possible to force Excel recognize UTF-8 CSV files automatically?
I'm developing a part of an application that's responsible for exporting some data into CSV files. The application always uses UTF-8 because of its multilingual nature at all levels. But opening such ...
- 20.9k
628
votes
36
answers
643k
views
Stop Excel from automatically converting certain text values to dates
Does anyone happen to know if there is a token I can add to my csv for a certain field so Excel doesn't try to convert it to a date?
I'm trying to write a .csv file from my application and one of the ...
user16324
592
votes
40
answers
1.9m
views
'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine
I'm trying to get data from an Excel file on a button click event. My connection string is:
string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\source\\SiteCore65\\Individual-Data....
- 6,051
586
votes
25
answers
1.7m
views
Is there a way to crack the password on an Excel VBA Project?
I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords.
Is there a way of removing ...
- 9,519
583
votes
3
answers
789k
views
What is correct content-type for excel files? [duplicate]
I want excel files on a website to open in Excel when clicked, not get saved on desktop, or get opened embedded in a browser etc. Now obviously it all depends on how everything is configured for each ...
- 18.6k
569
votes
60
answers
303k
views
How to convert a column number (e.g. 127) into an Excel column (e.g. AA)
How do you convert a numerical number to an Excel column name in C# without using automation getting the value directly from Excel.
Excel 2007 has a possible range of 1 to 16384, which is the number ...
- 8,694
441
votes
6
answers
591k
views
Setting mime type for excel document
MS Excel has the following observed MIME types:
application/vnd.ms-excel (official)
application/msexcel
application/x-msexcel
application/x-ms-excel
application/x-excel
application/x-dos_ms_excel
...
- 5,772
436
votes
14
answers
1.0m
views
Using Pandas to pd.read_excel() for multiple worksheets of the same workbook
I have a large spreadsheet file (.xlsx) that I'm processing using python pandas. It happens that I need data from two tabs (sheets) in that large file. One of the tabs has a ton of data and the other ...
- 6,081
387
votes
12
answers
372k
views
Convert xlsx to csv in Linux with command line
I'm looking for a way to convert xlsx files to csv files on Linux.
I do not want to use PHP/Perl or anything like that since I'm looking at processing several millions of lines, so I need something ...
- 3,879
363
votes
13
answers
613k
views
How to create a string or formula containing double quotes in Excel?
How can I construct the following string in an Excel formula:
Maurice "The Rocket" Richard
If I'm using single quotes, it's trivial: ="Maurice 'The Rocket' Richard" but what ...
- 92.6k
348
votes
3
answers
1.1m
views
How to return a result from a VBA function
How do I return a result from a function?
For example:
Public Function test() As Integer
return 1
End Function
This gives a compile error.
How do I make this function return an integer?
- 60.1k
331
votes
8
answers
642k
views
Add leading zeroes/0's to existing Excel values to certain length
There are many, many questions and quality answers on SO regarding how to prevent leading zeroes from getting stripped when importing to or exporting from Excel. However, I already have a spreadsheet ...
- 5,971
319
votes
3
answers
1.0m
views
Shortcut to Apply a Formula to an Entire Column in Excel [closed]
If I select a cell containing a formula, I know I can drag the little box in the right-hand corner downwards to apply the formula to more cells of the column. Unfortunately, I need to do this for 300,...
- 7,130
301
votes
13
answers
406k
views
Pandas: Looking up the list of sheets in an excel file
The new version of Pandas uses the following interface to load Excel files:
read_excel('path_to_file.xls', 'Sheet1', index_col=None, na_values=['NA'])
but what if I don't know the sheets that are ...
274
votes
7
answers
802k
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 ...
- 8,653
271
votes
11
answers
761k
views
Check whether a cell contains a substring
Is there an in-built function to check if a cell contains a given character/substring?
It would mean you can apply textual functions like Left/Right/Mid on a conditional basis without throwing ...
- 23.2k
268
votes
21
answers
919k
views
Return empty cell from formula in Excel
I need to return an empty cell from an Excel formula, but it appears that Excel treats an empty string or a reference to an empty cell differently than a true empty cell. So essentially I need ...
- 6,583
238
votes
7
answers
307k
views
What is the difference between .text, .value, and .value2?
What is the difference between .text, .value, and .value2? Such as when should target.text, target.value, and target.value2 be used?
- 2,759
232
votes
32
answers
538k
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 ...
- 12.8k
229
votes
10
answers
257k
views
Saving results with headers in SQL Server Management Studio
I am using SQL Server Management Studio.
I wish to save the results of a query to an excel file.
I choose "save as" and then save to CSV file which I can open in excel. All good except I am ...
- 17.5k
228
votes
12
answers
489k
views
'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data)
I get this error when I try to connect to any excel through MSSQL Server Data Import i.e. SSIS package The Microsoft.ACE.OLEDB.16.0 provider is not registered on the local machine. (System.Data)
It ...
223
votes
14
answers
395k
views
Excel: last character/string match in a string
Is there an efficient way to identify the last character/string match in a string using base functions? I.e. not the last character/string of the string, but the position of a character/string's ...
- 23.2k
217
votes
21
answers
229k
views
Is there a way to auto-adjust Excel column widths with pandas.ExcelWriter?
I am being asked to generate some Excel reports. I am currently using pandas quite heavily for my data, so naturally I would like to use the pandas.ExcelWriter method to generate these reports. ...
- 3,477
211
votes
4
answers
955k
views
Excel VBA - exit for loop
I would like to exit my for loop when a condition inside is met. How could I exit my for loop when the if condition has been met? I think some kind of exit at the end of my if statement, but don't ...
- 10.1k
208
votes
22
answers
164k
views
Microsoft Excel mangles Diacritics in .csv files?
I am programmatically exporting data (using PHP 5.2) into a .csv test file.
Example data: Numéro 1 (note the accented e).
The data is utf-8 (no prepended BOM).
When I open this file in MS Excel is ...
- 2,333
204
votes
8
answers
891k
views
How to keep one variable constant with other one changing with row in excel
Lets say I have one cell A1, which I want to keep constant in a calculation. For example, I want to calculate a value like this:
=(B1+4)/(A1)
How do I make it so that if I drag that cell to make a ...
- 2,043
202
votes
14
answers
197k
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 ...
- 4,045
202
votes
12
answers
812k
views
How to write to an Excel spreadsheet using Python?
I need to write some data from my program to an Excel spreadsheet. I've searched online and there seem to be many packages available (xlwt, XlsXcessive, openpyxl). Others suggest writing to a .csv ...
- 2,289
200
votes
23
answers
944k
views
How to read and write excel file
I want to read and write an Excel file from Java with 3 columns and N rows, printing one string in each cell. Can anyone give me simple code snippet for this? Do I need to use any external lib or ...
- 2,269
198
votes
7
answers
398k
views
How to correctly display .csv files within Excel 2013?
It seems Excel 2013 doesn't read CSV files correctly (Excel 2010 does). Every time I open .csv files, all my data are displayed in the first column.
I know I can go to DATA, Convert, and then choose ...
- 4,875
195
votes
16
answers
478k
views
How can I perform a reverse string search in Excel without using VBA?
I have an Excel spreadsheet containing a list of strings. Each string is made up of several words, but the number of words in each string is different.
Using built in Excel functions (no VBA), is ...
- 118k
188
votes
2
answers
573k
views
How to save MySQL query output to excel or .txt file? [duplicate]
How do you save output of a MySQL query to a MS Excel sheet?
Even if it's only possible to store the data in a .txt file, it will be okay.
- 6,343
185
votes
20
answers
211k
views
Import and Export Excel - What is the best library? [closed]
In one of our ASP.NET applications in C#, we take a certain data collection (SubSonic collection) and export it to Excel. We also want to import Excel files in a specific format. I'm looking for a ...
- 7,990
184
votes
28
answers
529k
views
Function to convert column number to letter?
Does anyone have an Excel VBA function which can return the column letter(s) from a number?
For example, entering 100 should return CV.
- 16.9k
182
votes
22
answers
174k
views
Best way to do Version Control for MS Excel [closed]
What version control systems have you used with MS Excel (2003/2007)? What would you recommend and Why? What limitations have you found with your top rated version control system?
To put this in ...
- 3,011
181
votes
13
answers
176k
views
Excel VBA App stops spontaneously with message "Code execution has been halted"
From what I can see on the web, this is a fairly common complaint, but answers seem to be rarer. The problem is this:
We have a number of Excel VBA apps which work perfectly on a number of users' ...
- 2,154
181
votes
5
answers
956k
views
How to get the path of current worksheet in VBA?
I wrote a macro as an add-in, and I need to get the path of the current worksheet on which it is being executed. How do I do this? How do I get the file path (just the directory)?
- 59.3k
177
votes
19
answers
403k
views
Generating CSV file for Excel, how to have a newline inside a value
I need to generate a file for Excel, some of the values in this file contain multiple lines.
there's also non-English text in there, so the file has to be Unicode.
The file I'm generating now looks ...
- 29.5k
171
votes
25
answers
347k
views
How can I read numeric strings in Excel cells as string (not numbers)?
I have excel file with such contents:
A1: SomeString
A2: 2
All fields are set to String format.
When I read the file in java using POI, it tells that A2 is in numeric cell format.
The problem is ...
- 2,046
171
votes
5
answers
205k
views
Pandas cannot open an Excel (.xlsx) file
Please see my code below:
import pandas
df = pandas.read_excel('cat.xlsx')
After running that, it gives me the following error:
Traceback (most recent call last):
File "d:\OneDrive\桌面\practice....
- 2,607
169
votes
17
answers
361k
views
How to write to an existing excel file without overwriting data (using pandas)?
I use pandas to write to excel file in the following fashion:
import pandas
writer = pandas.ExcelWriter('Masterfile.xlsx')
data_filtered.to_excel(writer, "Main", cols=['Diff1', 'Diff2'])
writer....
- 2,597
168
votes
6
answers
376k
views
How can I send an HTTP POST request to a server from Excel using VBA?
What VBA code is required to perform an HTTP POST from an Excel spreadsheet?
- 31.2k
160
votes
26
answers
225k
views
Importing CSV with line breaks in Excel 2007
I'm working on a feature to export search results to a CSV file to be opened in Excel. One of the fields is a free-text field, which may contain line breaks, commas, quotations, etc. In order to ...
- 4,738
159
votes
9
answers
1.2m
views
How do I declare a global variable in VBA?
I wrote the following code:
Function find_results_idle()
Public iRaw As Integer
Public iColumn As Integer
iRaw = 1
iColumn = 1
And I get the error message:
"invalid attribute in ...
- 2,413