Microsoft Excel is a commercial spreadsheet application written and distributed by Microsoft for Microsoft Windows and Mac OS X.
-1
votes
0answers
10 views
What is a good design pattern for generating an Excel (xlsx) file in code? [migrated]
I occasionally have projects where I have to output some data as an Excel file (xlsx format). The process is usually:
User clicks some buttons in my application
My code runs a DB query and ...
4
votes
2answers
66 views
Finding matching strings in a column
I have col D (in sheet1 called Students), col A (in Sheet2 called Students too) and col B ...
4
votes
3answers
52 views
Inefficient (slow) loop with calculations in worksheet
I am looking for best practice help. Slow loops seem to be a recurring problem for me and I'd like to learn a better way. The code itself works as it should, except it is far too slow.
The problem ...
1
vote
1answer
33 views
Processing Excel files of a predefined template to generate output CSV files of another predefined template
I'm a freelancer and I recently wrote a python script that processes input xlsx files that contain data in a particular format and output a csv file in another format as required.
The code works well ...
8
votes
3answers
143 views
Speeding up execution time of worksheet-formatting code
I've been writing some code that places some data in an empty worksheet and then formats the sheet by adding borders to all of the used cells. The code functions how it should perfect, but it takes a ...
3
votes
1answer
55 views
Optimize spreadsheet-filling routine
I'm stuck with the slow VBA routine below, which I think is the cause of lagging (taking to long). I was wondering if there is a faster way to do this:
...
3
votes
3answers
98 views
A practical example of evenly distributing n lists into a single list
I had previously asked about how to evenly distribute the items in n lists into a single list and was referred to this question.
I made a practical example of my solution for this in VBA for Excel, ...
5
votes
3answers
108 views
Assigning value to cells with Excel VBA
I have an Excel Worksheet consisting of two columns, one of which is filled with strings and the other of which is empty. I would like to use VBA to assign the value of the cells in the empty column ...
8
votes
2answers
282 views
Optimize total code in one sub
I have the following code which works fine, but there has to be a faster way to do this. I want to have it all in one sub.
...
4
votes
1answer
53 views
Increasingly Long Runtime for Macro
My code works, but the problem is that it is taking an increasingly long time to run, with the time required to complete calculations increasing every time I use the macro. I've tried a variety of ...
5
votes
3answers
83 views
Read table row with multiple options
I developing an algorithm to read a row from a table (specifically, I'm reading from an Excel file).
I have three conditions about when to terminate the Read operation:
When an empty cell is found ...
6
votes
1answer
49 views
Possible memory leak in a for loop macro
I was reading about similar problems to the one I am having, and my guess is that I am having a 'memory leak'. I'm not sure exactly what that means, or how to correct it. Could you take a look at my ...
8
votes
2answers
61 views
How can I make this userform timer faster?
I have a userform that displays a goal time for workers to shoot for when completing a task. It also has a stopwatch on it that is controlled by a start, stop, and reset button on the userform. If the ...
2
votes
1answer
61 views
Improving performance in generating an Excel file
I am using this code to write more than 170,000 rows to an Excel document. All in all, the file generation process takes about 3 minutes total. (I've already shaved off about ten minutes by shaving ...
5
votes
2answers
198 views
VBA Function slower than Excel Formula
I've replaced the following horrendous Excel formula (values and names have been replaced):
...
4
votes
1answer
81 views
Ignore exceptions when changing column formula in Excel table
The new table feature on Excel is excellent and you can specify a column formula as well as exceptions on the column. However, if you want to change the column formula, Excel will rewrite the whole ...
7
votes
3answers
169 views
Script for finding cheating students in a quiz
Below is a script to find cheating students in a quiz in a daily moodle activity log exported in .xls. It works fine and is written in a procedural way.
Essentially the script isolates the activity ...
5
votes
3answers
92 views
Improving performance of VBA batch scripts?
I am using a script to open a bunch of Excel files, copying two or more sheets into a new file and saving this new file. It sure beats doing it manually, but I think it could be faster.
Here's my ...
6
votes
3answers
125 views
Retrieving data from files in the folder
I am to use VBA code to simply retrieve certain data from all Excel files in certain folder and paste it in working spreadsheet. I am not sure that this is the most efficient (which matters as I would ...
11
votes
3answers
2k views
Validate and import data from an Excel file
I have recently designed a module which will do bulk upload for different contents. Basically the user will upload an Excel file, I will have to read and validate headers of excel then each value of ...
8
votes
2answers
187 views
Calculation of an inflation on volume/year
Background
I would like to find out if my current solution to the problem described below is "good enough" or if there is an alternative way of achieving it. All I care about is the length (no. of ...
5
votes
2answers
3k views
Extract Excel data using Interop.Excel from C# is very slow
Code Objective
I need to create a System.Data.DataSet object from an Excel workbook. Each DataTable within the ...
4
votes
2answers
108 views
Excel sheet code is taking too much time
I have an Excel sheet with lot of rows with data. I am separating it into 2 sheets with some condition satisfies.
This is taking too much time. How can I improve the speed? Please suggest any ...
10
votes
4answers
508 views
Looping through an Excel document in C#
Before looping through an Excel document using a library I found, and wanted to know how long it would take to loop through the whole thing.
There are 40k rows.
I looped through only the first 5k ...
6
votes
1answer
4k views
Reading data from Excel sheet with ExcelDataReader
Objective:
I want to import an Excel file, and read the rows of certain columns. For this, I use ExcelDataReader. I've implemented a low-level class called ...
4
votes
1answer
229 views
Ruby script to load xlsx files into mysql db
I put together this Ruby script (1.9.3) to load data into MySQL from many xlsx files that contain data entered by people. These files typically have many illegal characters and it has been difficult ...
5
votes
2answers
94 views
More efficient update macro in Excel
I have a macro that makes comparisons and then this macro exports all of the changes based on if the information doesn't match. I have it so that each column gets their own worksheet in the new ...
10
votes
2answers
1k views
Creating Excel document is very slow
Attached is a generic code I wrote to create an Excel file with x number of worksheets.
The problem I am having is that it's pretty slow, like 5 seconds a sheet. It was my understanding that using a ...
8
votes
1answer
107 views
Excel Mapping Module
I wrote this bit of python intending it to become some module to replace excel mapping programs like MapForce. Right now it only does columns to columns. So the number of rows in always equals the ...
10
votes
1answer
199 views
Importing data from an external EXCEL-Sheet
I was assigned the task of copying some subsums from a given EXCEL-Sheet into the executing EXCEL-Sheet.
This had to be done with an EXCEL-Macro, so non-programmers can easily use it. There was the ...
4
votes
2answers
159 views
Writing a JTable to a tab delimited file
I have wrote this class that will write JTable to a tab delimited file so that it can be opened in Excel. It works great but is there any way to make it better or is there a way to have it directly ...
0
votes
1answer
180 views
Better implementation of Excel's SUMIFS using pandas, the Python Data Analysis Library
I've implemented Excel's SUMIFS function in pandas using the following code. Is there a better—more Pythonic—implementation?
...
11
votes
2answers
254 views
Suspiciously redundant Excel macro code
I need a little help in finding a better solution. In the screen shot below any change to the forecast line I need to find the value in column B. The screen shot is only a sample in my production ...
2
votes
2answers
137 views
Updating tables with bulk of data through CSV
My work is pretty simple. I have to migrate a bulk of data into the specified tables, so that the data does show up at the front-end. The data is provided through the ...
1
vote
1answer
42 views
Excel to SQL Upload
Aim: Import Excel to SQL
Potential Issue:
Wrong file type - this is handled in the 'upload' button by not allowing anything but *.xlsx files
The wrong type of Excel file i.e. not 2003 onwards, ...
4
votes
0answers
54 views
ABAP Excel data analyzer
I wrote this code years ago to analyze Excel data coming in from the clipboard.
Please review for performance concerns and maintainability concerns.
One minor note, from an OO perspective, a class ...
5
votes
1answer
520 views
XLSX writer implementation
We have multiple scripts which write into XLSX with the same format, into a different format or slide variation. I am trying to write an API which saves time for other programs, but I need input on ...
2
votes
3answers
1k views
Winform that exports a datagrid to Excel
I've just created a winform that exports a datagrid to Excel. Originally the file was never shown, but I was asked to give the option. So I put in a checkbox and modified the code.
Is my if/else ...
12
votes
2answers
266 views
Avoiding repeated code in worksheet
Can someone help me make this code more professional? I'm trying my best to find something similar, but I wasn't successful.
I want to avoid the repeat of the code for every single value and also I ...
4
votes
1answer
70 views
How toTighten up and improve a bastardized Excel VBA code
I have the following code. It's purpose is to add up and sort another columns Data. I tweaked it to fulfil my purpose, but I'm sure it can be cleaned up.
The only problem I have with its function is ...
6
votes
1answer
244 views
Excel: String Tokenizer
I am trying to implement strings tokenizer in Excel via UDF using C# and Excel-Dna. Personally I found the mIRC's function $gettok pretty useful due to the lack of string functions in Excel.
Examples
...
4
votes
3answers
151 views
Getting a value from an Excel sheet column
The code below gets value from a column in an Excel sheet. The values I get are B1, B2, B3, B4...., B100, ...Bn. All I need to do is strip out the char 'B' and convert the numeric string to integer ...
2
votes
1answer
223 views
reading Excel (93-97) sheet with more than 65536 rows using cfspreadsheet
I have a process that reads multiple sheets from an Excel file, then inserts the data into a database table. However, I am experiencing some memory issues when one of the sheets contains more than ...
4
votes
2answers
134 views
Procedure too long. Cannot execute. How to make it shorter and more effective?
My code is too long and cannot be executed. It simply searches for an empty row in a given destination. If it is empty, it performs certain actions (copying, pasting, etc.). If it is not empty, it ...
4
votes
1answer
149 views
Is brute force the accepted best practice for handling Excel COM 'busy' exceptions?
Following on from a question about handling-com-exceptions-busy-codes I would like to know if the following model is the accepted best practice of handling Excel COM busy messages when accessing the ...
3
votes
1answer
415 views
Is there a better or more elegant way to code this CSV cell-parser (using CSVReader)?
I'm currently doing my first internship and had to create an application that will check through the first row of a CSV for valid or invalid input. Is there a more elegant way to code or to refactor ...
4
votes
1answer
3k views
Multiple criterias in a VLOOKUP in Excel VBA
I have made the following custom defined function in Excel. It works somehow like VLOOKUP(), but it takes two criteria. I think the code is a bit of a mess. Does ...
6
votes
1answer
815 views
How to make this ping test with timer more efficient?
I wrote a small ducktaped VBA script which pings servers every 15 mins or so. If a server's status is anything other than "Alive", the server and timestamp is written to another worksheet called ...
0
votes
3answers
393 views
speed up merging worksheets and using find/replace
Of all the macros that i put into heavy rotation these days, this one is running the slowest. ~4-5 seconds depending on the size of the files. It's not a lot but i'd like to know why code 16x as long ...
3
votes
2answers
436 views
better way to remove from collection in excel vba
In my vba code, I am trying to generate a specific list of cell row positions. Basically how i do that is I first fill a collection with the entire list of row positions pertaining to a specific ...
