Results 1 to 10 of 10

Thread: I need a big help to run the whole Automation

  1. #1

    I need a big help to run the whole Automation

    I am new at this work and I have to control the production in the company. Since I don't have any experience with VBA I put down our necesities so I need your help telling me for every part of the project what do I have to use to accomplish it, hints like:
    This part you do in access (hint: xxx comand) , that part you do VBA script (first I don't know where to put in VBA scripts) etc.
    I need to know all the components that I need for this and how to connect them, then I will take the resources and search how to do it specifically. So I need all the general hints you can give me here because I am starting from zero. If you want to see excel sheets mentioned, tell me where to upload them. Many thanx!

    To run the company production:

    1) - Database of the planning calendar through the year
    - The screen would contain the planning per chosen week
    - Inside the every day of the week I would choose the time

    intervals for some particular installation and assign it to some of the technicians listed while at the same time period I can assign the other instalation to other technicians.
    - For every of these installations I need to mark if
    a) we received the written report on it
    b) has that part of the work been evaluated
    c) is the proyect finished
    d) do we have some special material needed to finish it

    (marking that we have special requeriments and then put the text comment)
    - being able to search for (un)finished proyects, pending special needs, proyects with the report missing or not evaluated.

    I have already prepaired the default Excel workbook 'Parted.xls' for every daily part of project installation Where you enter the hours and the material and it calculates various costs and the rentability of that part of the installation.

    So, I would like that, when I enter the name of the installation in my planner, the script makes a copy of the default workbook, changing its name to the name of the client with the date that I assigned it to.

    If I mark that the installation is finished, I need a script that takes the other Excel workbook that I have also prepared, 'Project.xls' , adding as many copies of the first row as there are separate Installation_date.xls (versions of 'Parted.xls') workbooks, renames all those files to finished_Installation_date.xls and inside the ProyectX.xls puts the links to all those corresponding cells of the finished_installation_date.xls files so that the 'Project' calculates the total cost of all finished_Installation_date.xls files together.

    2) When the project is finished, I would execute another script that would add that data taken from the Project workbook to the second database which would contain :
    - The name of the client
    - The mediator
    - The duration of the project
    - The amount charged
    - Our benefit from the project

    3) The third database would be the database of items containing:
    - Our reference for the item
    - Descriptive name
    - Long description
    - The market price
    - The make
    - The provider
    - The discount the provider gives us
    - The second provider
    - The discount of the second provider
    - The manufacturer's reference

    Upon every database change, another script would do the actualization of the Excel workbook containing the complete list of items with the fields of our reference, descriptive name, market price, discount, and the manufacturer's reference.

  2. #2
    Something like this really needs SAP or Oracle. Unfortunately those enterprise solutions are not cost efficient for small organizations.

    The problem with your request steel lady is that the whole thing can be done in Access or Excel. Automation of the process can be minimal or extreme.

    The question is too broad.

    What I suggest to people facing your type of problem (that of designing flows of data) is to imagine all the reports that you will need generated. Then imagine the ones you want (but don't need). This will have to be from the perspective of CEO/Controller/Production Supervisor/ and work crew. Also understand how these will tie into your existing systems (I'm assuming you already have an accounting system). How will the data travel back and forth. What are the key metrics that need reporting.

    Then you make a list of the data that needs to be tracked and how that is acquired.

    When you have those things, you have your deliverables and you have your givens.

    Because you do not know a thing about VBA, I highly suggest you use someone in the Company you trust, or network to find a knowledgeable IT professional that you can pick their brains, and present to them with what you have (and the budget you've been given). Ask them how to approach the problem. Hopefully you can find more than one person for input.

    I'm also deducing that your Excel skills are modest: You've already created a "project.xls" but what you really want to produce is a template "project.xlt".

    I really recommend that you do not do this by yourself.

    If you must do this by yourself and have no other alternatives, begin with a manual system that covers your needs and ask help for automation of the tasks one by one. Asking for a whole system on the fly is too open for me I'm afraid.

    Good luck!

  3. #3
    Hi Gwkenny,

    I don't have the practice in this field because I was using Linux with Fortran before and seeing VBA it doesn't look so complicated. I like to play with such things so I decided to do it since it is the small company, of one boss, 1 administrative, me and tecnicians. So, since there are no people to ask and we will not afford to pay someone to do it, I will do it myself. After thinking what do we need I put down the description that you have.
    The template Parted is designed to have the imput in hours and menus for selection of material and calculates all the costs for clients and our rentability. I really do not need much more than to keep track of which information I still miss and to be able to search well all the information we have. We don't have so extensive needs to use SAP or Oracle and I really think that it can be done using Excel, Access and VBA, I just need help to make the structure and tips to start.

  4. #4
    VBAX Master Tommy's Avatar
    May 2004
    Houston, TX
    You could use MySql, PostgreSQL on Linux, use Apache and php, html for reports/viewing/editing and be at the same level as Oracle. You could also download SQL Express edition (for Free)which would enable you to use some of the reporting/programming tools.

    So far as a "server" for all this, in a small company < 300 a decent PC would work.

  5. #5
    Yes but now I am in the company that uses only Windows and I have these templates prepared that we are already using., I don't know why you are moving me all around. I just need tips how to conect Access with Excel using scripts!

  6. #6
    VBAX Master Tommy's Avatar
    May 2004
    Houston, TX
    Not really trying to send you all over the place. Just giving you viable options, choices to make a decision with.

    Since you want access and excel - the access forum would be a good place to start. Posting a sample of the excel file with the private information removed would give us an idea of what you have. Searching the knowledge base would give you a lot of information to get started.

    this excel sheet has a lot of how to. it is written by Tommy Bak (not me). The forum will not let me post because it is way too big.


  7. #7
    Tommy, I can not see the file, it tells me hat the server is not found... Can you upload it to speedyshare ?

    Many thanx!

  8. #8

  9. #9
    VBAX Wizard lucas's Avatar
    Jun 2004
    Tulsa, Oklahoma
    I was able to download it from the first link by right clicking and select save target as...

    howdy Tommy.....been fishin yet?
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  10. #10
    VBAX Master Tommy's Avatar
    May 2004
    Houston, TX
    LOL that's all I have to do!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts