1 Attachment(s)
Read all Excel files in a folder and edit them via UI
Hello Everybody,
i have a problem and i hope someone here could help me find a solution... As you may notice, I am not a native English speaker and I am not really used to write in english too... Hopefully someone can still follow my thoughts on this:
I have a folder containing some Excel Files, these are part lists and each Excel file contains the parts for a specific project. They are all in the same format, so it should be easy to read the single cells. Like the Project Number, that identifies this project, is always in B4 or the Date is always in B3. The Table containing the listed parts starts at A12 it has six columns and the amount of entries is different in every project.
The task i got is, I have to build an UI in VBA our guy in the warehouse could use to check if we have everything necessary for a build in stock or if we have to order some spare-parts. My Idea looked like this:
Attachment 28179
My plan was to open a specific project (Excel File) by selecting the Projectnumber allocated to it (in B4). So first thing to do is to "scan" for all documents in this folder and list every Projectnumber available in the drop-down-box. I think the best moment to do this is every time the drop-down list is opened, so it is always up to date. I know this will result in a little "stutter" but it is better than a outdated project list.
Now we know the file we want to work with and the other textfields getting filled by the information contained in the fields A12-F12 (our first entry). The Arrow Buttons allowing us to step forward and back over every entry in that list/table.
The only thing left to do is to edit the Excel file and put in 2 more columns. For every Entry we need the warehouse guy to enter the amount "Available in Stock" and in the second column a "O" for order or "S" for in stock. I thought it would help the warehouse guy if he could see fast what was done already, so i decided to show a jpg depending on the made decission.
May or may not you have noticed, that I am completely rusted in my VBA skills, the last time i did something like this was more than 15 years ago... Seems like I've also lost the most importand skill to find everything needed in the internet... So I would really really appreciate if someone could at least give me a few tipps or push me in the right direction, this is a bit frustrating.
Sorry for the text block, if any additional information is needed just ask for it.
Thank you for at least reading this whole post!
