Visual Basic Q

[BoltonSte]

Any VB guru's fancy lending a hand?


Unfortunately at work we don't have any databases to record testing of the batches we make, instead it's all done with Excel Workbooks...yes I know


Anywho;


One department does the testing and the worksheet captures various information such as batch number, weights etc... this is then saved, the file name is the batch number.


Finance then use this information to record % yields for the process, a continuous spread sheet with each batch sequentially listed in a column, currently involves opening the previous spread sheet and copying data across.


I can think of a couple of options:
1 - Use VB in the finance workbook, to cross ref the batch numbers with the file names and pull the information back. Although I suspect that this is going to cause issues with the number files needing checking and opening.
2 - Add an 'upload' button to the 1st department, which, once the sheet is complete can be clicked to transfer the information to the appropriate cells in the finance sheet. I'm thinking something like a lookup, matching batch numbers and then put A1 into G, B1 into H etc...


I'm not after anyone to do it for me, but some pointers and terminology would be useful as my VB experience is limited.


Ta


Ste

Considered doing this in a database instead? They are tailor made for stuff like this.

MS Access would be a good start, if its a good fit, export everything to a database, and an access database can be made to talk directly to SQL. So can Excel as it happens, if you do things right.

Your unique record would be the "batch number" as i expect these are unique, and not repeated.
Easy to backup/restore too if you have problems.

C.

[russell664]

MS access database would work far better.

Use things like relational tables/keys, to cross reference the batch numbers. You can make user logins, and track changes if setup correctly.
Doing it this way you will be looking at a lot of VBA code - but once you have the fundamentals - it's fairly easy.

Use could well use some macros to copy data in an Excel workbook, but in my honest opinion, you would be best to move away from that while you can.

[andrewsmith]

Ms access is a good shout, but could cost a lot for a mass deployment and the machines would need to be windows 7 onwards

A database would work a lot better, you could develop a bench test in excel and see how it works in the real world. I did do it many years ago in excel to build a primitive pos system

Sent from my E2303 using Tapatalk

[BoltonSte]

Switching to Access or anything is about as likely as getting water to flow up-hill. Half the users struggle with excel as it is.


We do have a database package for tracking manufacturing, but it doesn't have any quality control capability unless we upgrade to the latest version, see point above.


Andrew, how would you go about developing a bench test in excel? This may be an option.


Ooooo, I apparently have Access 2013 on my laptop, which means the rest of us should also have it, anyone willing to give me a quick pointer on how to start building databases?

[andrewsmith]

When I did it, it was done similar to access it was excel ME I think.

I did as one for inbound, stock and sales using a URN (batch codes are ideal) and used macros to update the pages and logic cells

Quick Google brought this up http://m.wikihow.com/Create-a-Retail...tem-with-Excel

Google how to make pos system in excel, and there's a few good ones up

Sent from my E2303 using Tapatalk

[BoltonSte]

Ta


That looks quite straight forward, I can probably use that for my raw materials as well. Eyes well and truly opened, it's going to be a few evenings playing I think.