PDA

View Full Version : Solved: Getting started on VBA project



rodneywead
02-23-2011, 09:45 AM
I am looking for information on how to get started on a project I am voluntarily undertaking at work. I would like to point out I am not looking for (as of right now) specific direct code. I am doing this project to try and learn VBA and simply copying and pasting code is not going to do me any good. I have a couple of books on VBA that I have read a little bit of.

I work in a warehouse that does a weekly audit of inventory. The warehouse is divided up into different sections (or mods as we call them) and I have to calculate how many errors are in each mod. I am currently just copying the defects into excel from an internal website. Once that is copied I have to find the mod using a simple left function (……….=left(A1,5)) to get the mod. I then import the weekly count from an already written SQL function and calculate the defections using excel.

I would like to automate this with VBA. I would like to be able to pull the information from the website and SQL and calculate it using VBA. The information on the website is in two links from the main website that update every week. (example………….Count for the week of February 13 has two links that have the information that I need)

How can I get started on this? Thank you for your time and help.

Rodney Wead

Tinbendr
02-23-2011, 05:24 PM
How can I get started on this? Whew! You said a mouthful!

First of all, welcome to VBA Express and what version of Excel do you have?

Is the database in a table on the website? Can it be pulled in via Web Query?

Are you currently pulling the SQL data into Excel from a network?

rodneywead
02-24-2011, 06:25 AM
Thanks for the welcome. I am using Excel 2010. The database is not in a table on the website. I think you could pull it into a web query, but I am not sure. I am pulling the SQL data from MySql browser 1.2.17 which is connected to a computer network at the company.

IBihy
02-24-2011, 07:53 AM
Hello,

am I right with the assumption, as I gather from your text, that you'd rather need support on how to go about it?

I'm a strong supporter of solid requirements engineering. I might be able to help you on your way.

Regards,
Isabella

rodneywead
02-24-2011, 09:43 AM
Yes, that is correct. I am looking for help on how to get started.

Frosty
02-24-2011, 09:45 AM
Don't take this the wrong way, please... but a lot of the times people look for "programming" solutions when they don't know the existing capabilities of the programs they are using.

Taking the 50,000 foot view of the process... it sounds like you need a report which rectifies what you have vs. what you think you have.

You may want to look into using Access instead. There are a lot of built-in functionalities (and samples, including the Northwind Database) for just these kind of inventory functions (and the ancillary reporting required).

IBihy
02-24-2011, 09:53 AM
Hello Rodney,

I think Frosty has a good idea here. The question to you is: Would you have MS Access available to you or another database product?

Isabella

IBihy
02-24-2011, 10:24 AM
[quote=rodneywead]...I work in a warehouse that does a weekly audit of inventory. The warehouse is divided up into different sections (or mods as we call them) and I have to calculate how many errors are in each mod. I am currently just copying the defects into excel from an internal website. Once that is copied I have to find the mod using a simple left function (……….=left(A1,5)) to get the mod. I then import the weekly count from an already written SQL function and calculate the defections using excel.

I would like to automate this with VBA. I would like to be able to pull the information from the website and SQL and calculate it using VBA. The information on the website is in two links from the main website that update every week. (example………….Count for the week of February 13 has two links that have the information that I need)
.../quote]

Hello Rodney,

without having to produce tons of artifacts nobody will ever read later :laugh2:, you can structure the information using pencil and paper. Have a look at the stuff I wrote here, maybe that's a kick-start for you:
http://www.vbaexpress.com/forum/showthread.php?t=36139&highlight=hiflier

I think you able to can use it for your case. If you're not getting on, raise a hand.

Regards,
Isabella

rodneywead
02-24-2011, 11:02 AM
Thanks for the link Isabella. I will take a look at it. Thanks for the suggestion Frosty. I do have Access 2010 and I am fine with using that. The more simple the better. The one question I would have if I did it that way is would it be easy for someone to update/calculate this who does not know Access? The goal of my project is for anyone to open a program, click one button, and have this calculated.

Frosty
02-24-2011, 11:56 AM
There are numerous ways to set up things so that they don't have to have intimate knowledge of the program in order to get the result.

Without knowing more, it's a bit easy for me to say "sure, you can set it up in Access so that when you click on a link to an access database, the database will open and automatically display a report showing the discrepancies between reported inventory and actual inventory."

The devil is in the details, and the best approach for you and your process still remains to be determined... I'm just offering suggestions as it sounds like you're still in the planning phase.

To step back a little-- people often want programming to take the place of training. That is sometimes possible and some times not... but in general, the less training you require, the more programming knowledge you'll require... so you may find yourself in a bind trying to set up a solution when you may not have enough of either.

If that's the case, you will probably need to go back, follow IBihy's advice as far as writing out what you want to get done, and then see what can be automated in Excel (if that's the thing you are most familiar with).

Sorry for the vague philosophical response... I recognize that it is tough to know how to proceed without all the information, but that it is also tough to get the information without knowing how to proceed.

rodneywead
03-16-2011, 09:52 AM
I ended up doing this in SQL. Thanks for the feedback.