Import from Word into Access, Export into Excel, NEED ADVICE 2

[JJOHNS]

I've been given a new project that should be pretty interesting, but I'm not completely sure how to start. Right now, data from a proprietory database is provided to my user in a Word document. My user then keys it all in to an Excel spreadsheet every day. This file is about 300 records long and all numbers, so it is very time consuming.

My task is to create an Access Database that will take the information from the Word document and store it. Then the reports should be fed to the Excel spreadsheet. The goal is to accomplish two things. First, make the data easier to enter. Second, lock the data fields so that it cannot be manipulated once it's entered into the database (users should not be able to "fix" the numbers).

I have a good idea how to lock the Access database and export Access data into an Excel spreadsheet. My problem is how to import the Word data into Access without making my user key it in every day.

I would appreciate any and all suggestions on this before I start the project. I'm planning to start working on it Saturday.

Thanks in advance.

 

[carmo]

Why is there a need to use Word in the first place??

 

[dodge20]

I think you need to clarify your question a little more.
My take on this is that you have a database (probably tab delimited) in word, and you want to import it into access? How are you saving the word document? If you save it as text only, you shouldn't have a problem importing it into word.

Dodge20 If it ain't broke, don't fix it.

 

[KyleS]

Hi,

If I'm understanding correctly the ideal situation would be to take the data directly from the proprietary database and import it into Access. But sometimes we don't have these luxuries... If the database importing from Word is a must, then the it would be best if you could tell us what the word form looks like. Is it just a list that happens to be in Word, or is it an individual form, with fields and the whole nine yards? Kyle

 

[JJOHNS]

I don't have a choice about taking the data in a Word document. The DBA for the proprietory database provides it to my user that way. It looks like a tab deliminated text document (although I haven't examined the document on my computer yet), something like this:

11:01:01 1 22.34 1:00 9:10:01

It's just a long, long list of numbers. Each day, a document like this with over 300 lines is given to my user. Each line represents performance numbers for an employee for a day. The 300 lines are divided among supervisors. There are 30 supervisors with about 10 employees each. Each supervisor keys in the data for his/her employees. The problem is that there is a potential for the supervisors to adjust the numbers to make their sections look better. My user (department manager) wants an Access database that can lock certain fields so the numbers are more difficult to manipulate. He would also like (and this is the hard part) an automated way to put the data into the Access database so that the supervisors don't have to do it. Less work for them, less number changing for the department.

I think I can use Mid() to break the lines into variables and put them into fields if I can get the Access database to read a line a time, right?

 

[rokerij]

It sounds like you could simply import the word document into Excel. By opening Excel and then finding the word document a wizard will pop up allowing you to take what looks like delimited information and plug it into columns in an Excel sheet. Be sure that you have all of the correct info. in the right spot before opening it up in Excel, the wizard will allow you to confirm this. Once the information is in Excel, now the employee does not have to key any information. Your records from Word are in an Excel format and the fields and columns should be in order. Save this file as an excel format, and it will be ready to import into Access. Open your database and go to file-> Import. Find the excel sheet you just created and import the excel sheet either into an existing table or create a new one. As for automation of data entry, there are a couple of ways to handle it. I would set up if possible a form that allows the employee to sign in and out at the start and end of each project, this will allow you to take the actual time and the time they entered for a comparision. If the two don't match you might want to question the employee. This can be locked and secure so that they would not be able to tamper with any information. They would however need access to a machine to enter the time. Otherwise the form could be used by the manager, at which case a manager should be trusted to enter the correct information. Either way the form will bypass the word import and excel imports into access. I hope this helps.

 

[KyleS]

Yeah, as long as all the lines are delimited in exactly the same way, that would be a very easy way to get the data into a useable form, although you could import into Access the same way... If the patter isn't the same for everyone
XX:XX:XX XX XX:XX XX:XX XX:XX:XX

Then some more complicated stuff might be required. I recomend trying it this way to see if you can get it to wrok before we try and use the FileSystemObject abd read this thing in one line at a time...

rokerjj,
By automation of data entry, I believe he was referring to the above portion, having the information in Access without having the supervisors enter their information manually. Kyle

 

[rokerij]

Kyle,
I think you are right. Word will import into a table in Access?

 

[KyleS]

Well, not a Word file, but if you save the word file as text you can go to File-->Get External Data-->Import Table in the Access Menu and look for a text file and import the data that way. Or you can open the word file with Excel, then import into Access. Either way, not too bad. If the Word file is very simple, it might just be easier to change the extension to .txt when the time comes to automate this process... Kyle

 

[JJOHNS]

Thanks to everyone who has posted here. I'm going to start working on this tomorrow morning. I've got some great ideas to work with now. I appreciate everyone's advice.

I'm always interested in any more ideas anyone has...

 

[KyleS]

Good luck! Post back and let us know how it goes and what snags (if any) you have hit and maybe we can be of some more assistance... Kyle

 

[JJOHNS]

I've finally started on my project. I've had some good luck and a couple of new challenges. First, the good luck. It turned out that the DBA for the proprietory system I mentioned was also putting the information out in an Excel spreadsheet all the time, but (unbeknownst to him) no one was using it. I'm importing that spreadsheet into my Access database. Works good, but there's still a problem. I think I can live with it, though. When I import it into Access, I can't import it directly into an existing table. I import it into a new table with no primary key and then append it to the existing table. It' a couple extra steps, but it's only done once a day.

Now the challenge. There is another DBA working on a different proprietory system. His is a scheduling program. He provides data weekly in an Excel spreadsheet. It all fills into the first column on the spreadsheet. Using the instructions from rokerjj above (thanks), I'm able to put it all into separate fields. Here's the difficult part:

The schedule comes out in a format that isn't really great for a database:

For the week of: 3/8/2003
Name SatStart SatEnd SunStart SunEnd ...
John Johnson 9:30 5:30 9:30 5:30
Break 10:45 11:00 10:30 10:45
Break
Lunch 1:00 1:30 1:15 1:45


This is good enough for us to follow during the week, but it doesn't work well going into an Access database.

Does anyone have an idea how I could convert it to something like this?

Name SatDate SatStart SatEnd SatBreak1 SatBreak2...
John 03/08/2003 9:30 5:30 10:45 11:00

I'm thinking about creating a form with a command button to convert it. The background code would have to read the original table and fill the data into variables (maybe an array). Then I could use an INSERT statement to put it into another table the way I want it.

Is there an easier way?