Questions tagged [excel]

Microsoft Excel is a commercial spreadsheet application written and distributed by Microsoft.

Filter by
Sorted by
Tagged with
10
votes
1answer
8k views

Import multiple XML files into Excel

I have adapted a VBA code to import all the XML files I have on a folder into Excel. I have tried to run the code with only two files and it takes about 30 seconds. Several minutes to import less than ...
10
votes
1answer
114 views

Aggregating 2 lists by matching UniqueIDs

This Macro takes my company's 2 main sources of all-client data (each a data table in a separate worksheet) and aggregates them into a 3rd list, by matching a unique account number (for clients that ...
10
votes
1answer
509 views

A simple ascii progress bar

I've been wanting to experiment with a bit of error handling and robustness to make my code more user friendly. I was inspired by this answer on UX to try and add a progress bar to a button - but was ...
10
votes
2answers
367 views

Creating a pseudo Pivot Table / Database using a 4-D array

Why am I not just using a Pivot Table / Database? a) I've never ever used either before. And I don't have time to learn how before this project needs to actually be finished. b) The final output ...
10
votes
1answer
317 views

Produce a Linear Cut List for Woodworking

I was inspired to write this by a recent review of cutting pipes. The only thing I'm really iffy about is using the WriteCuts method. I couldn't figure out a way to ...
9
votes
2answers
2k views

Build Excel formulas with string replacements

...
9
votes
3answers
1k views

Adding data from multiple Excel files to two lists

I have 5 nested for loops below, to add rows of data from multiple files to one of two lists. Is there a more pythonic way of doing this? I've come across an ...
9
votes
2answers
343 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. ...
9
votes
3answers
208 views

Check Accounting Journal for Errors

I wrote this sub in Excel VBA. I am a novice, but I was able to get my code to work how I wanted it to. Unfortunately, I can see there is a lot of repetition, but it is too complex for me to ...
9
votes
2answers
1k views

Using VBA to "apply names" to named range references

So there's a known bug in excel where applying named ranges via ribbon doesn't work properly (for 2010 and 2013 and possibly 2016). There was a question about it on superuser - Apply names in excel ...
9
votes
2answers
2k views

Extracting Country Names from Cell Values

This fairly clunky looking VBA script successfully pulls names of countries from an Array, but could it be made shorter? Mass of text from Column C onward in each row may contain the names of ...
9
votes
3answers
584 views

Write to EXCEL from SQL DB using VBA script

I have a vba code which takes data from SQL database and dumps it into excel. I see that my query should extract approximately total of 120k records. I monitored this activity and learnt that even ...
9
votes
2answers
1k views

Text file to spreadsheet

Lets say you have several .txt files in a folder and want to merge all text files into a single Excel document: A.txt ...
9
votes
2answers
17k views

Powershell script for manipulating Excel files

I have a directory of .xls workbooks with the following naming convention: 001.WIP Monthly Report 002.WIP Joes Custom Report ... 129.PUR Supplier Spend The ...
9
votes
2answers
351 views

short or simple solution for putting values from label to column in excel

I have a code here that counts the number of fruits from Column B to Column AF (31 days). I used a switch with cases from 1 to 31. I'd like my code to be simpler. ...
9
votes
2answers
246 views

Matching between files and a list of filenames at scale

Given a folder typically containing ~250,000 small (20-100kb) files in HTML format (they open as single-sheet workbooks in Excel) and a list of ~ 1 million filenames, I need to open all the files that ...
9
votes
3answers
2k 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 ...
9
votes
3answers
337 views

Remove unwanted characters from specified column in all Worksheets

I need to remove some unwanted characters from a given column in all Worksheets from my Workbook. The code below works perfectly, but I'd like to know if there is a better/cleaner/faster way of doing ...
9
votes
1answer
925 views

Test if Excel is open without relying on catching an error

Getting a hook on an open instance of Excel is something I do all the time. I'm now questioning if I'm doing this the best way. I'm particularly concerned with the fact that I rely on the ...
9
votes
2answers
333 views

Battleship UI: GameSheet

In the previous post I presented the MVC architecture, but all we saw of the View was the pretty bits. From the outside everything looks innocently pretty: The guts of the worksheet's code-behind is ...
9
votes
1answer
3k views

Excel VBA Multiplayer Tetris Game Loop Repaint Rate

While searching VBA Excel Tetris games online, I noticed several single player Tetris games but no multiplayer games. I am looking for a better strategy to manage the speed of the Application and ...
8
votes
5answers
2k views

Copying and comparing two sheets before deleting duplicates

I am running this code as a macro in Excel, which copies two columns from sheet 1 and pastes them into sheet two. It then compares the first column to a column in sheet two before deleting any ...
8
votes
4answers
11k views

Building an OPC client in Excel

I am pretty deep into building a very multi-faceted little app in Excel with VBA. It does a number of tasks, all centered around using OPC to get tag values from several PLC's and doing various ...
8
votes
5answers
961 views

Managing book of Excel sheets

This is a follow-on from a previous question I posted here. I've got code here that works for what I want, but the problem is the loop takes ages to perform. I was wondering if anyone could follow ...
8
votes
3answers
172 views

Loop through a unique combinations driven by choices

I am trying to optimize my code and make it as fast as it can be, I have researched as much as I can and am now asking the community for any tips because there are definitely people more experienced ...
8
votes
4answers
198 views

Looks at order data and arranges it to focus on order locations

I have a sheet that order a bunch of information on individual orders. Order numbers are in column A, other information is in columns B-P, and then product numbers are the headers for columns Q-DL. ...
8
votes
4answers
2k views

Converting from .xlsx to .txt

I have code that converts each sheet of a spreadsheet into a .txt file. It works well, however, given the big number of exports (about 90 .txt files), I'd like to seek advice on how to speed it up. <...
8
votes
3answers
290 views

Colorizing Excel cells with a date range filter

In a table there is a number of column (chambre) and a number of row (alveole). If these matches in a list in an other sheet then it checks if it the date is between two dates on that second sheets. ...
8
votes
3answers
160 views

Clearing duplicates from an Excel 2007 sheet

This is the original code I had for removing duplicates from a sheet: ...
8
votes
1answer
145 views

Data entry of Information

Couple of items to make note of first: Some of the code is inherited the other has been refactored by me. The procedures that have an underscore in them I am still working on refactoring. I am still ...
8
votes
3answers
421 views

Speed up VBA with 138k rows and ~330 sheet creation

I have a VBA script that does the following and I am trying to see if I can have it perform faster than 44 seconds: start with ~138k rows of data on sheets("Data") concatenate each cell in the row ...
8
votes
3answers
324 views

If ComparisonIsTrue(thisPost, ComparisonOperator.NotEqualTo, goodCode) Then Me.Answer

This is a follow-on to a global enum for comparison operators. I wanted to pass a logical expression E.G. >= someValue as a parameter to a function, so I made an ...
8
votes
3answers
2k views

Implementing IDisposable for an Excel class

I am developing a class (in a C# MVC 5 project) that originally had a using block within a single method, and it got to be very huge after placing formatting ...
8
votes
3answers
4k views

Parsing Excel file into CSV file

EDIT: Thanks everyone for your answers, this is moving quickly now and it's always essential to get better with design and best practice! I am loading in an excel file to a dataset using ...
8
votes
2answers
128 views

Function to find the kth match (2)

code from suggestions in comments here. I will still write it like the first time, so you do not need to read the old question. I've created a UDF some time ago and used it pretty often till now in ...
8
votes
3answers
4k views

Invert rows and columns in an Excel file

Annother little excercise with openpyxl: Say you have a Excel file like this: The Goal is to make make it look like this: My code to acomplish this: inversion.py ...
8
votes
2answers
384 views

Efficiently create and sort a Collection

The goal of my code is to sort data into two categories. It must use a local copy of the initial data from Collar (Top View).csv. My code creates a Collection of items called Collars using the initial ...
8
votes
3answers
193 views

Turn logged data into fancy plot

I'm logging data from an embedded platform over UART. This gets saved to a log file and I want to process the saved log with Excel. I have no idea whether what I did was even remotely a good solution....
8
votes
2answers
199 views

Report Building (Data Retrieval, Validation, Aggregation, Business Logic, Report Building, Visual Presentation)

This: Is a data table we get from our financial platform with lots of useful information. For reference, "--" is also the string they use to denote empty values. This: Is a spreadsheet I built (...
8
votes
1answer
112 views

Pasting Values To/From a Calculation Model

I have a Database worksheet (SoapUI - CollisionsSingle) that have around 1500 records and 38 columns of data per record. I have a VBA script that copies the records ...
8
votes
2answers
10k views

Fast(er) web scraping with VBA

I have a code that fetches rates from a website called X-Rates, and outputs to excel the monthly averages of a chosen country. The code runs quite fast, but I still think I could improve the code a ...
8
votes
2answers
3k views

Converting Data in Rows and Columns to Rows in VBA for Excel

I have a working VBA script for Excel that converts a matrix of data with multiple records in per row to multiple rows with one record per row. A StackOverflow user told me that the code could use ...
8
votes
3answers
11k views

Most efficient multi-find/replace solution in Excel VBA

I'm looking to improve the runtime and efficiency of my VBA code that performs multiple Replace operations on an Excel Spreadsheet. I started out with the following ...
8
votes
2answers
217 views

Aggregating data from multiple worksheets into a consistent format

First off, this is the 3rd (and probably last) review of the project in question. You can find the previous question here. The Macro accesses a workbook containing 8 worksheets each with similarly ...
8
votes
1answer
6k views

Excel VBA binary search class module [closed]

I have a long list of sorted strings in a Workbook, and I have to find a lot of values in it. I was hoping to build a binary search Class Module that will find them for me faster than Excel's built-in ...
8
votes
1answer
1k views

Space Invader Style Game Written In VBA

EDIT: Link to the github, https://github.com/Evanml2030/Excel-SpaceInvader THE README (APPLIES HERE TOO): VBA version of a classic game Yes, I know that "Missile" is repeatedly written as "Missle"...
8
votes
1answer
168 views

A global Enum for comparison operators

This approach was born out of wanting to pass a logical expression (E.G. ">=10000") as a parameter to a function. So, I built an Enum and a function to use it to evaluate logical expressions. Is ...
8
votes
1answer
1k views

Spreadsheet to calculate how to cut pipes

I have written a piece of code that calculates how to cut pipes based on an order and fixed raw pipe length (in this case 288 feet), but I am not 100% happy with my current code as it counts the pipe ...
8
votes
1answer
155 views

Matlab Computer Vision

The code below takes a video as input (a drop onto a fabric surface) and I want it to return the area of the drop. Currently the code works, but I'm no Matlab expert, and wrote it entirely through ...
8
votes
2answers
134 views

Worksheet that lists a person's name

I have a worksheet that lists a person's name (column A; many duplicate names are in this column) with associated data (columns B through G). I have code below that takes this list of a ~ 1000 rows ...

1
2
3 4 5
22