MS Excel nerdery

Woden

Woden

Woden

Soldato
Joined
7 Mar 2011
Posts
6,859
Location
Oldham, Lancashire
So I have an 8 hour shift and literery no work to do, at all. So a good time to make a start and improve some of the stuff we use on a day to day basis.

Basically we have to log all unschedualed returns. So we have an excel spreadsheet, one for each year with a tab for the contracts list, then one for each month. We basically keep track with rows that list things like date, shift, tracking number, etc. Problem is the file is getting massive. its almost 3MB now and these PC's aren't exactly beasty.

There has to be a better way. I dont have access to C++/c# at work just Office (no Access unfortunately) and VBA. File locking on the shared drive isnt ideal either.

Any ideas? This could actually be usefull as I havn't had any experiance of VBA since I left uni, and that was very limited.
 
Your glad you don't work with me if you think a 3meg excel file is big:)

On a more helpful or not so so helpful point this as a candidate for a simple user form and access back end. But as you don't have access that ain't gonna work.

You can build a user form in excel as your front end however you need to store the data somewhere, it seems kind of pointless building a user form and storing the data in excel, you may as well just keep the solution as is if your going to do that.

You could look at storing it in text files in a secure location, the disadvantage of that will be if you have multiple users trying to write to the file at the same time, you would have to check the file was not in use before calling a write procedure.
 
3MB is massive on a machine with 512MB of ram, at least with all the other stuff I need open at the same time. When I told IT I needed to have a lot of stuff open at once, they gave me a new monitor :| (23" 1080p though, not bad to be fair!)

Yea, Access was my first solution but the IT guys wont install it for us for some reason. Most of the time all we are doing is adding a new entry, its very rare we have to look back at older entries, and obviously we *never* edit old entries, that would defy the point.

ATM (well not ATM im not in work) I am making a new spreadsheet with fewer contract numbers, almost 10k contracts but most clients have a whole bunch, so we don't need them all in this file.

I'm guessing if I make a frontend, I can have drop down boxes that select a client, and fills in contract number by pulling it from that list? Then date/shift can be automagic. All they would have to type then is description, tracking number and name.
 
You must log in or register to reply here.
Back
Top Bottom