A little project I need some help with.

[paul86]

Hey guys, new here and I need a little help with a project my boss wants me to complete.

Basically right now, he has a huge directory of quote sheets that he fills out whenever he wants to generate a quote for a customer (they are excel files, i've attached a picture of one). He wants me to create something that will scan through this directory file by file and extract certain pieces of information from each excel file, and put them into a database. The company I work for isn't too big, so i'm going to use access as the database.

I've also suggested building a new quote sheet in VB, and programming it so everytime the form is closed, the certain pieces of information he wants will be saved into the database. So I guess after this we can forget about Excel. But also, he wants to be able to bring up the entire quote sheet again if need be.

If you guys could please help me out, what would be the best way of going about doing this? Am I heading in the right direction as far as my thinking goes?

 

[paul86]

I'm not sure if the attachment worked or not so here's a direct link the a picture of what i'm talking about...thanks guys.

http://img.photobucket.com/albums/v136/paul88/quotesheet.jpg

 

[GaryPanic]

why not do the whole in Access ..a lot easier

 

[paul86]

Please explain? I don't need to use VB? Please explain the process you would go about doing this.

 

[gemma-the-husky]

extracting bits and pieces from excel with access is demanding in programming skills

gary is suggesting you do the whole project, including quote design and printing ALL in access. this is also likely to be quite demanding, depending on whether you want to eg, type in addresses and product details or look them up from lists.

looks ups are better, as you get consistent treatment, and it is easier to search the database to find eg, all quotes where you have quoted a given product. but this can start to become an advanced project as well.

 

[paul86]

i'm beginning to think now I should just use visual studio and basically create a form identical to the one that I posted a picture of, and use visual studio to connect to access and display the data in there. the VB that's included in access is garbage and it will probably be a lot easier in terms of designing the form and coding to use visual studio, what do you guys think?

you are right though, this is turning into a bit more of a project than I thought it would be. any help would be greatly appreciated guys, thanks.

 

[Rabbie]

Paul, I get the impression you don't have much experience of Access. forgive me if I am wrong about that. You really need to get your head round Data Normalisation before you start or you will have a lot of problems later on. Try searching this forum and access help for more info about this. There are also some good articles if you use Google.

Good luck

 

[GaryPanic]

Gemma has the guist of this right(with my meaning - i had to keep it short meant to be working..lol) - why mess around with 2 or 3 applications
you could make up a access form to mirror this sheet - punch the numbers in and it will work it out for you

for your end user all they would need to do is enter in a name etc pretty much as you see . then each sheet would be given a unique number docket number ?? and your accounts could then get the info from this docket number ..
a lot of things depend on how computer litarate your user is going to be

you could have it a simple as you shown or more interactive

so you could have the products appear in a drop down list with default pricing - and how many in stock etc..



so you would need the following
a table set up with all of the fields shown on the sheet
then in docket number this might be best to have a numbering system (not autonumber-more squental(?) numbers 1,2,3, etc - varuious options on this

then you could have look up account or just type in a name and let your accouts dept figure the rest out or a list of accounts and a "New " account option- slightly more advanced - but not rocket science ..
then see if we can take it to the next level - have it as an complete quote/invoicing system- accounts system -

from the looks of this - this is not high end mathmatics or serious accounts functions ..


- i would put together a wish list of what you require - put it up on this post , and we help you work it out ...-(please note help.. -the idea of this forum is to help people grasp the idea, once you got it - you be singing the praises of access -)

 

[GaryPanic]

look at the attached zip file (A2000)
this is a rough idea - not pretty
but look at the table set up then look at the form

this has a basic account table

name and address etc
POC (point of Contact field ) note field for genreal notes on a account
now i haven't put any numbering system in - but from this you get the idea

on the phot tbl - item i would be the name of the first item (on the assumption that the Jpeg is the actual view - there are better ways of doing this - the use of contious forms and drop downs will make this smarter - might not be what you need but hey
also no diary system is included ..

i have my own take on diary stuff - why take it out of acces and put it in outlook - build a diary system within access (various options on this slight more advanced - but a simple option would take 5 mins to put together)
requires a date field and a yes no option - done....

 

[paul86]

So you're recommending all this be accompished in access, no need to touch any other program such as visual studio? And yes, i'm definetly no expert in programming access, but I do have general programming knowledge.

anyways, thanks for the list idea, here's what I basically need to accomplish:

1)read all the excel files in a directory (probably about 1000 of them or so) and extract certain pieces of information from them
2)once this information is extracted it needs to be put in the appropriate fields in a table
3)if possible create a form very similar to the one i posted which workers can fill out and when submitted or saved (how does that work?) the same pieces of information that were extracted from the excel files will be saved into the table as well.

also, after this happens I don't think they will be using excel anymore to look after this sort of thing since the database will be much neater, but they will need to be able to open the previous quote sheets and see all the information as well. the reason for having a few specific pieces of data in a table in a database is so if a very similar job comes in they can easily and quickly look up a previous job that has been done like that, saving a lot of time. again thanks for all your help guys.

 

[GaryPanic]

is all the data in the same format - ie are all the quotes sheets exactly the same -

if so easy if not - hmmm time to think-

my view would be start anew - so new quotes would be entered onto the new system and any old ones that need amending reneter them manually etc - save you a lot of head ache time - or only load in the past xxxx

 

[paul86]

yes, all the quote sheets are the exact same format. i'm just not sure how to make access communicate with excel like that so you can extract data from specific cells within the excel document. For example, there is a description area on the quote sheet that tells what kind of job it is, flyers, brochures, business cards, etc. I would like to grab the info in that cell from the excel document and maybe put it under a "type" field in the database. Doing this one by one would be extremely time consuming as there is probably over 1000 of them. I imagine there will be some code to connect to excel and some kind of loop to iterate through all the files in the directory and extract the appropriate information. let me know what you think, thanks!

 

[paul86]

Alright, so i've got it to the point where i'm reading excel data into access, however there are 2 problems.

1)It's only reading one file (it needs to loop through a directory of excel files and read them all)
2)It's importing the WHOLE excel file. I need to be able to select the individual cells which are to be imported to access.

Anybody have any code suggestions? Thanks!

 

[paul86]

Oh and this is the code i'm using to read the excel files right now:

Dim con As New ADODB.Connection

con.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\sampleimport.mdb;Jet OLEDB:Engine Type=4"

Dim strSQL As String
strSQL = "SELECT * INTO Table2 FROM [Sheet1$] IN ""C:\test.xls"" ""Excel 8.0; HDR=No;"""

con.Execute strSQL

con.Close
Set con = Nothing

 

[paul86]

So nobody on this board knows how to do this? It can't be THAT difficult to do.

 

[Rabbie]

So nobody on this board knows how to do this? It can't be THAT difficult to do.

If you think it so easy why don't you do it?? Remember any one who helps you on this forum is doing it unpaid and is under no obligation to help you.

 

[paul86]

Believe me if I knew how to do it I would have already, I believe that's why i'm coming to this board for help...

 

[NigelShaw]

Hi,

people are not here to do the job for you, but they will happily give you a sense of direction. sometimes things are not easy because if they were, then everyone would be doing it.

Consequently, my background was Excel but now i would never look back. VB is as weak or powerful as you want it. when i first looked into VB with Access, it scared me a little as i could just see myself getting lost but its not that bad. more methodical than VB Excel. i do however, have written code that can look through workbooks finding information. i used to use it on my quote pages for purchases and so forth. it look through column, rows or both to find what you need. i suppose a big question is, why only part information to be sorted? it would be easier for all or nothing?

i think i have a code that can look through the worksheets and retreive info and place in onto separate sheets as long as the cells are holding the same info type. if not, i could write one but a sample workbook would be required for cell pointing.

NS

 

[paul86]

There's only 1 Sheet per excel file that needs to be touched. So basically it's going to be a macro that loops through a directory of excel files one by one and plucks the same cells of information out of each of them, and puts them into a table. And yes I only need certain cells of information from each sheet, because there is a lot of information on each quote sheet and I want to be able to quickly locate the specific pieces that i'm looking for (which are the ones I will be importing).

Any ideas?

 

[NigelShaw]

Hi,

can you post a sample sheet with highlighted cells that you need? also, a list of workbook & sheet names as there will be numerous books to go through.

NS