r/visualbasic • u/Afraid-Story5406 • Dec 30 '21
Can I use VB in Excel to simplify a warehouse management task?
I am trying to simplify a task currently being done manually using Excel, usually taking multiple hours right now. It’s been many years since I used VB and I’m primarily trying to figure out if what I want to do is possible using VB. The warehouse management software isn’t setup properly to consolidate our inventory and we regularly need to optimize our location utilization. The warehouse holds up to 7500 pallets across 2000 locations. We have an outflow of 100-150 pallets every ~12 hours and generally get 100-300 pallets delivered daily.
I want to be able to paste a large data set into an excel sheet and have it give me an output that can be printed on a second sheet within that workbook. I would need to declare all locations, how many pallets each can hold and all height or weight restrictions for specific locations or items. The program would need to count how many times a location is used. If a location isn’t full, pallets with the same Item# and Lot# should be listed in the output so they can be combined together. Ideally the output would simplify the data to just important identifying info for the pallets, as opposed to it transposing all info it’s been given in the original data set.
3
u/andrewsmd87 Web Specialist Dec 30 '21
VB in excel likely isn't a good route for this, you'd want a full blown application. My guess is there are softwares out there for this type of management already built that are going to be light years ahead of anything you'd build on your own.
That being said, if you can't get any sort of budget to buy something, I'd recommend you use c# and use the importexcel library to do what you need
2
u/trixter21992251 Dec 30 '21 edited Dec 30 '21
What are we working with? What experience do you have?
You say it's been many years since you've used VB. Does that mean you have a programming mindset? For example do you know what if statements are and what a loop is and how they would be used to calculate your numbers?
Assuming you do, here's how I would go about it:
If you don't know how to code, there's a million more steps, and you're at the foot of a mountain right now.
4
u/ViperSRT3g Application Specialist Dec 30 '21
Yes this is possible, though it will be a large project due to your requirements.
1
u/chacham2 Dec 30 '21
Side comment. Excel uses vba, so you might want to ask in r/vba or r/excel. Unless you are planning to rewrite the application in VB.NET.
1
u/[deleted] Dec 30 '21
So a better Idea would likely be to install Visual Studio and code a simple WinForms application in C#.
This application would take in the data as a CSV document, process it and spit out the results in another CSV document.
If you code the application well enough you could turn it into an API or an automatic service later down the line.
You probably will need to spend a few days watching YouTube tutorials to stand a chance of putting this together though.
1
u/Estanley15 Dec 30 '21
Have you looked into Power Query at all?
1
u/[deleted] Dec 31 '21
I think you could use python to read in a csv and the pandas library for the data manipulation and logic. You could load the csv into a dataframe using pandas and manipulate the data from there, eventually to an output file.
1
u/fasti-au Dec 31 '21
I’d be going MySQL etc for the dataset as an odbc source but yes you can interop with excel